A migration that rewrites a busy table in one committing statement is long and blocking. A sequence of small, backward-compatible changes moves the same schema forward with no maintenance window, because each step is separately deployable and separately reversible.

What is being managed is a lock, and the question is how long it is held — which depends on how much data the statement touches.

Expand, backfill, contract

Three phases: expand adds the new column while the old shape still works, backfill populates it in batches, and contract retires the old shape once no code reads it.

Between them sits the dual-write phase: the application writes both columns for a while, so a rollback loses nothing, and reads switch once the new column is trustworthy.

Old code needs the old shape, so the ordering rules follow: adding a nullable column is safe, dropping a column is not, renaming is a two-step affair, and a NOT NULL constraint comes after the backfill, never before. On a 500,000-row sqlite table, ADD COLUMN note TEXT took 2.0 ms and left the page count at 13,926 — metadata only. Making that column NOT NULL required a rewrite: 115.4 ms and 13,926 pages became 28,532. Same schema change, two costs: the difference is whether the engine must touch every row.

Batching matters more than phase order. Backfilling 500,000 rows in one transaction held the write lock for 671.2 ms; in batches of 20,000 the same work ran 25 transactions with a median hold of 7.3 ms and a p90 of 7.8 ms. Total work is identical — only the longest single lock differs, and that is the number the application experiences.

The lock is the whole problem

PostgreSQL’s ACCESS EXCLUSIVE conflicts with every other lock mode, including the ACCESS SHARE a bare SELECT takes. It is acquired by TRUNCATE, VACUUM FULL, REFRESH MATERIALIZED VIEW without CONCURRENTLY, and many ALTER TABLE forms. Everyone waits.

A migration can also be blocked, and then it becomes the blocker. In rollback-journal mode an ALTER TABLE issued while another session held a read transaction open waited 1,512 ms for it to finish, and four application writes failed in that window; the same migration in WAL mode finished in 0.3 ms with zero failures. The lesson is not about journal modes: a forgotten BEGIN turns a 3 ms schema edit into a 1.5 second outage that queues everyone behind it.

Validate outside, enforce inside

SET NOT NULL is where the split is explicit in the docs: it normally scans the whole table to prove no NULL exists, unless a valid CHECK constraint already proves it or it was added NOT VALID. ADD CONSTRAINT ... NOT VALID records the constraint without checking existing rows, and VALIDATE CONSTRAINT does the scan later under a weaker lock.

That is the right division. The validation scan over 500,000 rows took 16.3 ms and needed no write lock — zero application writes failed while it ran — while the enforcement step, which rewrites the table, took 210.4 ms. The expensive part is the scan, and the scan needs no lock.

When it breaks

The first failure is the backfill in one transaction: it locks and bloats. A single UPDATE over a large table holds the write lock for the whole run, and in PostgreSQL writes a new row version for every row touched, which then needs vacuuming. The measured version is 671 ms of exclusive access instead of a p90 of 7.8 ms, and the blast radius is every write in the application, all of which fail at once if the pool timeout is shorter than the lock.

The second failure is a migration that holds a lock long enough to trip the application’s own timeout. A plain CREATE INDEX takes SHARE — blocks writes, allows reads — for the entire build; the fixture’s three builds held the write lock for 67, 83, and 69 ms on 500,000 rows, and production tables are bigger. CREATE INDEX CONCURRENTLY exists for this, at a price: SHARE UPDATE EXCLUSIVE, two table scans, a wait for any transaction that could modify the table, no transaction block, and a failed build that leaves an invalid index updates still pay for.

Check it quickly

Find the lock holder, then run DDL with a timeout so it fails instead of blocking:

psql -c "SELECT pid, state, now()-xact_start AS age, left(query,50) FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;"
psql -c "SELECT pid, mode, granted, left(relation::regclass::text,40) FROM pg_locks WHERE NOT granted;"
psql -c "SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE NOT indisvalid;"
psql -c "SET lock_timeout='3s'; ALTER TABLE events ADD COLUMN note text;"
psql -c "SELECT conname, convalidated FROM pg_constraint WHERE conrelid='events'::regclass;"

The first query names the transaction your migration will queue behind: a large age on a non-null xact_start. The second shows a migration waiting for a lock; readers queueing behind it are the blast radius. The third catches the invalid index a failed CONCURRENTLY build leaves. The fourth is how to run every DDL statement — lock_timeout converts “blocks everything for forty seconds” into “fails fast and you retry”. The last shows convalidated, proving the CHECK constraint before you rely on it for SET NOT NULL.

Sources