Zero-Downtime Postgres Migrations in Practice
Expand-contract patterns, lock budgets, and the exact sequencing we use to migrate multi-terabyte tables without freezing writes.
Locks are the enemy of uptime
Postgres migrations fail in production when they take AccessExclusiveLock on hot tables. Adding a column with a default on older versions, rebuilding indexes the wrong way, or rewriting a table under traffic will queue writes until customers notice.
We set lock budgets and statement timeouts on migration runners. If a lock cannot be acquired quickly, abort and retry with a safer plan rather than blocking checkout.
Expand-contract sequencing
Expand-contract means adding new structures first, dual-writing or backfilling, switching reads, then removing old columns. It takes more deploys than a big-bang ALTER, and it is how multi-terabyte tables move without a maintenance window.
Each phase is independently reversible. That property matters more than clever SQL when something unexpected appears mid-migration.
Backfills that respect production
Backfills run in batches with sleeps, throttled concurrency, and progress metrics. We avoid long transactions that hold snapshots and bloat. For very large tables, shadow tables plus atomic rename patterns can outperform in-place rewrites.
Always verify row counts and checksum samples before cutting reads over. Silence is not success — instrumentation is.
Indexes, constraints, and cleanup
Create indexes CONCURRENTLY. Validate constraints in a second step after NOT VALID. Drop unused objects only after the application no longer references them and observability shows zero access.
Document the migration runbook next to the SQL. The next engineer on-call should know which phase you are in without reading git archaeology.
Alex Thompson
Backend Engineer
Backend engineer focused on performance, Postgres, and TypeScript platforms at scale.
