database migration locking tables during deploy: how to avoid
Avoids deploy-stalling table locks by splitting migrations into expand-then-contract steps, using concurrent index builds, and running heavy backfills outside the deploy. Use when ALTER TABLE blocks writes, deploys stall on migrations, or you need zero-downtime schema changes on Postgres or MySQL. Not for initial schema setup or schemaless NoSQL stores.
TL;DR
Most deploy-time migration pain comes from one DDL statement grabbing an exclusive lock on a hot table while the deploy waits. Avoid it with expand-then-contract: add the new column or table first, ship code that tolerates both shapes, backfill in batches outside the deploy, then drop the old shape in a later release. The deploy itself should never hold a long lock.
database migration locking tables during deploy: how to avoidSteps
Audit the migration for lock-hungry statements: table rewrites, adding columns with defaults on some engines, non-concurrent index builds. Expected: you have a list of the statements that will take strong locks and roughly how long each holds them.
Rewrite the migration as expand steps only: add nullable columns, new tables, and concurrent indexes; never rename or drop in the same release. Expected: the migration completes in seconds with only brief locks, even on large tables.
Build indexes concurrently (where the database supports it) and set a statement timeout so a stuck DDL fails loudly instead of blocking the deploy. Expected: index builds do not block writes; a timeout surfaces problems instead of hanging the pipeline.
Run heavy data backfills as batched background jobs outside the deploy window, not inside the migration. Expected: backfill progresses without holding locks; the deploy does not wait on data volume.
Deploy application code that reads and writes both the old and new shapes. Expected: old and new code versions coexist safely during the rollout.
In a later release, once all code uses the new shape, run the contract migration that drops the old columns. Expected: cleanup is a small, fast migration because nothing references the old shape anymore.
Use this when
- deploys stall or time out on a migration step
- an ALTER TABLE blocks reads or writes on a production table
- you need zero-downtime schema changes on Postgres or MySQL
Not for this skill when
- this is the initial schema on an empty database - locks do not matter with no traffic
- the store is schemaless (most NoSQL) - there is no DDL lock problem to solve
- the migration only touches read-only analytics tables - schedule it freely
Variant phrasings
- zero downtime database migrations
- postgres migration lock timeout
- online schema change without downtime
- alter table blocking writes production
Why it happens
Many DDL statements take an ACCESS EXCLUSIVE lock, which conflicts with everything including reads. On a big table the lock is held while the table is rewritten or scanned, so one migration statement can freeze a hot table for minutes while the deploy - and your users - wait.
Edge cases
- Adding a NOT NULL column without a default rewrites the table on some engines; add nullable, backfill, then set NOT NULL
- A failed concurrent index build leaves an invalid index behind - check index validity and rebuild rather than assuming success
- Expand-then-contract spans releases, so it needs discipline in the deploy tracker or the contract step gets forgotten
- Long-running transactions elsewhere can block even a "fast" DDL - check for idle-in-transaction sessions before blaming the migration
Provenance
Resolved from the public thread: https://vectle.com/posts/pst_AGejpI01BBW5QovFt7BVMQ