Schema Evolution & CQRS — Series 2

Preview — 3 of 10 questions

What does the saga pattern do to replace a distributed ACID transaction across multiple services?

javascript

1. Order service: CREATE order (status=PENDING)      → publishes OrderCreated
2. Payment service: CHARGE card                        → publishes PaymentCharged
3. Inventory service: RESERVE stock                    → publishes StockReserved
4. Order service: UPDATE order status=CONFIRMED

AIt executes a sequence of local transactions, each in its own service/database, where each step publishes an event/message triggering the next step; if a step fails, previously completed steps are undone via explicit compensating transactions rather than a single atomic rollback

BIt wraps all services in a single two-phase commit coordinated by PostgreSQL

CIt requires every service to share the same physical database to guarantee atomicity

DIt retries the entire multi-service operation indefinitely until all services succeed simultaneously

How can a CQRS read-model projection be kept in sync with the write-side database without the write-side application explicitly publishing events?

javascript

-- Set up a logical replication slot and publication
CREATE PUBLICATION orders_pub FOR TABLE orders;
-- A CDC tool (Debezium, or a custom consumer using pgoutput) subscribes
-- and receives every INSERT/UPDATE/DELETE on `orders` as a structured event

ABy polling the entire write-side table with SELECT * every few seconds and diffing results

BBy using logical replication/decoding to stream row-level INSERT/UPDATE/DELETE changes directly from the write-side database's WAL (e.g., via pgoutput or a tool like Debezium) to a consumer that updates the read-model store — capturing changes without requiring any application code changes to explicitly emit events

CLogical replication can only replicate to another PostgreSQL instance, never to a different kind of read store

DCDC requires modifying every INSERT/UPDATE statement in the application to call a stored procedure

You need to add NOT NULL to an existing column on a huge live table without a long-held lock scanning the whole table upfront. What's the safe technique (PostgreSQL 12+)?

javascript

-- Step 1: add the check but don't validate existing rows yet (fast, brief lock)
ALTER TABLE users ADD CONSTRAINT chk_email_not_null CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: validate separately — scans the table, but only takes SHARE UPDATE EXCLUSIVE,
-- which allows concurrent reads AND writes to continue
ALTER TABLE users VALIDATE CONSTRAINT chk_email_not_null;

-- Step 3: now this is fast — PostgreSQL 12+ recognizes the validated CHECK
-- already proves NOT NULL, and skips its own redundant full-table scan
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Step 4 (optional cleanup): the CHECK is now redundant with NOT NULL
ALTER TABLE users DROP CONSTRAINT chk_email_not_null;

ADirectly run ALTER TABLE ... ALTER COLUMN ... SET NOT NULL — PostgreSQL always does this instantly regardless of table size

BNOT NULL constraints can only be added when the table is created; existing tables cannot have them added later

CAdd a CHECK (col IS NOT NULL) constraint with NOT VALID (which takes only a brief lock and doesn't scan existing rows immediately), then run VALIDATE CONSTRAINT separately (which scans without holding the same exclusive lock throughout); once validated, ALTER COLUMN ... SET NOT NULL can use that already-validated check to skip its own full table scan

DRename the table, recreate it with the NOT NULL constraint from scratch, then copy all data over during a maintenance window

Answer all 10 questions (7 more), see explanations for every answer, and track your score.