Databases
Schema migrations: multiple-choice review
Crux Multiple-choice synthesis across the migrations unit: lock-queue freeze, ADD COLUMN rewrite, NOT VALID/VALIDATE, expand-contract, backfill WAL flood, and deploy order.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitSix questions that cut across the whole unit. Each one mirrors a decision you make while shipping a schema change to a live database under traffic — not a definition to recite, but a failure mode to defend against.
Goal
Confirm you can connect lock behaviour, safe-DDL rewrites, the expand-contract phases, backfill blast radius, and deploy ordering — the synthesis the seven lessons built toward.
Quiz
Completed
On PG 16 a deploy runs the statement below and it completes in 8 ms, yet the users table returns 503s for 70 seconds. A 60-second analytics SELECT was already in flight on users when the ALTER fired. What actually froze the table? ```sql ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'; ```
Heads-up A constant default is instant since PG 11 — it is stored in pg_attribute.attmissingval, no rows are written. The 8 ms runtime proves there was no rewrite; the freeze came from the lock queue, not row work.
Heads-up Postgres never pre-empts another session for a migration. The ALTER waits behind the held ACCESS SHARE; it cannot cancel it. The freeze is the FIFO queue, not a priority pre-emption.
Heads-up The pool exhausted because new SELECTs were stuck waiting in the lock queue behind the ALTER. The migration is the proximate cause, via the lock queue — not a coincidence.
Quiz
Completed
The same instant ALTER from question 1 keeps timing out behind long queries. Which single change turns 'migration freezes the database' into 'migration logs an error and retries'?
Heads-up statement_timeout caps how long a statement runs after it holds the lock — it does nothing about waiting in the queue. The queue-freeze is a lock-acquisition problem, so lock_timeout is the lever. A migration wants both, for different reasons.
Heads-up Wrapping in a transaction does not shorten the wait for the lock; the ALTER still queues behind the held ACCESS SHARE for the blocking query's full duration. It only controls hold time after acquisition, which was already 8 ms.
Heads-up More connections just means more sessions pile up behind the ALTER in the lock queue — the table is still frozen for new readers. This masks the symptom and can worsen the pile-up.
Quiz
Completed
You must add a FOREIGN KEY to a 100M-row orders table without blocking writes. Which approach is production-safe?
Heads-up A plain ADD CONSTRAINT takes ACCESS EXCLUSIVE and scans the whole table inside that lock — writes are blocked for the scan duration regardless of the hour. Off-peak shrinks the blast radius but does not remove the block.
Heads-up App-level checks race under concurrency and drift from the schema; they do not give the same guarantee. The NOT VALID + VALIDATE pattern adds the real constraint without blocking writes, so there is no reason to weaken the guarantee.
Heads-up The ACCESS EXCLUSIVE lock is held for the entire validating scan whether or not it is inside an explicit transaction. A short transaction does not help; splitting into NOT VALID + VALIDATE does.
Quiz
Completed
A rolling deploy is renaming users.username to users.handle. To keep both the old and new app versions working at every moment, which step ordering is correct?
Heads-up During the rolling deploy old pods still reference username; the instant the rename commits they get 'column does not exist' errors. A rename is fast but breaks the both-versions-compatible invariant — that is exactly the outage expand-contract avoids.
Heads-up Dual-writing to a column that does not exist yet fails, and dropping username right after backfill kills old pods still reading it. The contract must wait until no running code references the old column.
Heads-up Dropping username while old pods still read it breaks them immediately — this is the single-step rename failure with extra steps. Expand and contract must be separated by full code rollouts.
Quiz
Completed
A backfill ships as one statement on a 100M-row table. On a primary + 2 replicas, what is the first thing that breaks and what is the durable fix? ```sql UPDATE users SET handle = username WHERE handle IS NULL; ```
Heads-up A single UPDATE does not deadlock against itself. The blast radius is the WAL flood and the resulting replication lag on standbys, not a deadlock on the primary.
Heads-up Postgres has no mechanism to throttle DML based on replication impact — it runs the UPDATE and floods WAL. Bounding the blast radius is the engineer's job, via batching.
Heads-up Replicas replay WAL sequentially; tens of GB cannot be consumed instantly. Lag climbs and standby reads return stale data — the exact failure batching prevents.
Quiz
Completed
Three Kubernetes replicas boot at once and each runs migrate up on startup. One succeeds; two crash with duplicate-object errors. What was missing, and what is the cleaner production pattern?
Heads-up Migrations are not idempotent by default — CREATE TABLE or ADD COLUMN fails if applied twice. The crash is the expected result of three runners racing without a lock, not a tool bug.
Heads-up Postgres has no idea the three sessions are logically the same migration set; it runs each independently and they collide. Serialisation must be arranged by the runner via an advisory lock.
Heads-up A unique constraint stops double-inserts in the ledger, but the actual DDL still errors on the losing pods and crash-loops them. The fix is to serialise the runners, ideally out of pod startup entirely.
Recap
The unit’s through-line is one defensive checklist for every schema change. Locks: most DDL needs ACCESS EXCLUSIVE and the FIFO queue turns one slow query into a table freeze — SET lock_timeout so a failed attempt aborts and retries. Rewrites: a constant default is instant since PG 11, volatile defaults still rewrite. Constraints and indexes: NOT VALID + VALIDATE and CREATE INDEX CONCURRENTLY take SHARE UPDATE EXCLUSIVE and leave DML running. Breaking changes: expand-contract keeps old and new code compatible at every moment, never a single-step rename. Backfills: batch to bound the WAL flood and replica lag. Runners: serialise with an advisory lock and gate a pre-deploy job before the rollout. Every answer resolves back to the same invariant — the schema must stay compatible with both code versions, at every moment, without freezing the table.