migration-safety-checker

• SKILL.mdcore · 4,485 bytes

# 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