Migration Safety Checker

Turn a migration that is correct in a test DB but takes a production-halting lock into a sequence of small, reversible, zero-downtime steps. The outage is rarely the DDL itself - it is the queue of fast queries that pile up behind a statement waiting for an ACCESS EXCLUSIVE lock.

Workflow

1. Identify the lock each statement takes. In Postgres, ACCESS EXCLUSIVE (blocks reads AND writes) is taken by: changing a column type, adding a non-concurrent index, adding a column with a volatile default on old engines, and most constraint additions via direct ALTER. Flag every statement that takes a heavy lock on a large, write-hot table - as a rule of thumb, treat anything above a few million rows or with sustained write traffic as hot.
2. Guard the lock acquisition. Set lock_timeout (2-5 seconds is a common production setting) before any DDL so a blocked statement aborts instead of stalling traffic behind it. A long ALTER waiting for its lock blocks every query that arrives after it, including fast SELECTs - that queue is the outage.
3. Add columns nullable, then backfill, then constrain. Add the column NULLable with no volatile default (instant on modern Postgres). Backfill in batches of 1,000-10,000 rows (UPDATE ... WHERE id BETWEEN ...), committing each batch and pausing briefly between them, to avoid one giant transaction and table bloat. Add NOT NULL via a CHECK constraint with NOT VALID then VALIDATE CONSTRAINT - a weaker lock than direct SET NOT NULL.
4. Build indexes and constraints without blocking. Use CREATE INDEX CONCURRENTLY (cannot run in a transaction - disable the migration's transaction wrapper, e.g. disable_ddl_transaction! in Rails). Add foreign keys and check constraints as NOT VALID first (fast, light lock), then VALIDATE CONSTRAINT in a separate step (scans without blocking writes). Drop with DROP INDEX CONCURRENTLY.
5. Use expand-contract for renames and type changes. Never rename or retype a column in one deploy - running code still references the old shape. Expand: add the new column, dual-write from the app, backfill. Migrate reads to the new column. Contract: stop writing the old column, then drop it in a later deploy. Each step is independently deployable and reversible.
6. Verify the actual lock before shipping. Confirm the rewritten statement's lock with a lock-impact migration linter (e.g. squawk) or by inspecting pg_locks while the statement runs on a staging clone - never assume from the SQL alone.

Deliverable

Produce the rewritten migration as an ordered list of independently deployable steps, each annotated with the exact statements to run, the lock it takes and why that is safe, the batch size for any backfill, and its rollback - plus the lock_timeout setting to apply before each DDL step.

Quality bar