Databases
Schema migrations: free-recall review
Retrieval beats re-reading. For each prompt, say or write a full answer from memory before you open the model answer — the effort of recall is what makes the mechanism stick when you are staring at a frozen table at 2 a.m.
Reconstruct the unit’s core mechanisms — the FIFO lock queue, the PG 11 missing-value trick, NOT VALID/VALIDATE, expand-contract, the WAL-flood backfill, and runner serialisation — without looking back at the lessons.
- 01Why can an instant ALTER TABLE freeze a busy table for 60+ seconds, and what exactly is queuing behind what?
- 02How does PG 11+ make ADD COLUMN with a constant default instant, and what is the difference for a volatile default?
- 03Explain the NOT VALID + VALIDATE CONSTRAINT pattern and why it is production-safe where a plain ADD CONSTRAINT is not.
- 04State the core invariant of expand-contract and walk the six phases of a column rename (username to handle).
- 05Why does a single-statement backfill flood WAL, what breaks downstream, and what is the batch-size guideline?
- 06Why do down migrations destroy data in production, why does CREATE INDEX CONCURRENTLY need an advisory lock and a non-transactional annotation alongside it, and what is the right rollback strategy?
If you could reconstruct each answer from memory, you hold the unit’s spine: the FIFO lock queue means a waiting ACCESS EXCLUSIVE freezes a table behind one slow query, so lock_timeout is mandatory; a constant default is a catalog trick (attmissingval) while volatile defaults still rewrite; NOT VALID + VALIDATE and CREATE INDEX CONCURRENTLY move the heavy scan to SHARE UPDATE EXCLUSIVE so DML keeps running; expand-contract maintains a both-versions-compatible schema superset across a rename; batched backfills bound the WAL flood and replica lag; advisory locks serialise runners and forward migrations — never down migrations — are the production rollback.