A fast migration that stopped every read
The migration was ALTER TABLE work_order ADD COLUMN dispatch_ref text. On Postgres 11 and later, adding a nullable column with no default is a catalogue update measured in milliseconds. It ran during a normal deploy and the shop-floor API returned errors for six minutes.
The ALTER needed ACCESS EXCLUSIVE. A reporting query started four minutes earlier held ACCESS SHARE on the same table and had another six minutes to run. The ALTER joined the lock queue and waited. Postgres queues lock requests in order, so every subsequent SELECT — which would otherwise have been perfectly compatible with the running report — queued behind the ALTER. One long read plus one instant DDL equals a full outage on that table.
The migration itself was fine. The waiting was the outage.
lock_timeout and a retry loop
Every DDL statement we run now is wrapped: SET lock_timeout = '2s', attempt, and on failure back off with jitter and try again, up to twenty times. If the lock cannot be taken in two seconds, nothing has queued behind it long enough to matter, and the deploy retries a few seconds later when the reporting query has finished.
Set it as a statement-level setting inside the migration transaction, not in postgresql.conf, or you will find routine application statements timing out. Pair it with a pre-flight check that refuses to start DDL while any transaction older than 60 seconds is open against the target table — pg_stat_activity plus pg_locks gives you that in one query.
This single change removed the entire category. In eighteen months since, no schema change has caused a customer-visible error at Kestrel, across roughly 400 migrations.
Expand, migrate, contract
The pattern is old and still under-applied. Expand: add the new column or table, nullable, no constraints, deployed while application code still writes only the old shape. Migrate: deploy code that dual-writes, then backfill history in batches, then switch reads. Contract: stop writing the old column, then drop it, in a separate release at least one deploy cycle later.
The step teams skip is the gap between 'stop writing' and 'drop'. Dropping a column that a rolled-back application version still references turns a rollback into an incident. We hold dropped columns for one full release cycle and rename them to zz_dropped_dispatch_ref first, so anything still referencing them fails loudly in staging.
Constraints get the same treatment. ALTER TABLE ... ADD CONSTRAINT ... NOT VALID takes a brief ACCESS EXCLUSIVE and validates nothing; VALIDATE CONSTRAINT afterwards takes only SHARE UPDATE EXCLUSIVE and scans without blocking writes. Doing it in one step scans the whole table under ACCESS EXCLUSIVE, which on a 400 million row table is not a deploy, it is a maintenance window.
Indexes and backfills that do not bite
CREATE INDEX CONCURRENTLY cannot run inside a transaction block, does two table passes, and on failure leaves an invalid index behind that still costs write amplification. Check pg_index.indisvalid after every concurrent build and DROP INDEX CONCURRENTLY before retrying. Many migration frameworks wrap statements in transactions by default; that has to be disabled per-migration.
It also holds a transaction open for its full duration, which blocks vacuum from reclaiming dead tuples across the whole database for that period. On a busy table a two-hour concurrent build produced 40GB of bloat elsewhere. Build large indexes during low-write windows and watch pg_stat_user_tables.n_dead_tup while it runs.
Backfills go in batches of 5,000 rows keyed on the primary key, each in its own transaction, with statement_timeout at 10s and a sleep proportional to observed replication lag. We target keeping replica lag under 2 seconds; the loop reads pg_stat_replication and slows itself. A 380 million row backfill took eleven hours and nobody noticed it, which is the correct outcome.