Zero-Downtime Schema Migration Review
Reviews DDL migrations for the locks and table rewrites that take production down — version-aware for Postgres and MySQL, with a safe step-by-step rewrite for anything risky.
Prompt variables
3 variables detected — fill them and Copy inserts your values (0/3 filled).
Review this schema migration for production safety on a live, high-traffic database. The question is never "is the SQL valid" — it is "what does this lock, for how long, and what waits behind it".
Database & version: {{database_and_version}} (be version-precise: e.g. Postgres 11+ adds `NOT NULL` column with default without rewrite; earlier rewrites the table)
Table sizes / traffic: {{table_context}}
Migration:
```sql
{{migration_sql}}
```
For EVERY statement, report:
1. **Lock taken** — exact lock level (e.g. ACCESS EXCLUSIVE, SHARE UPDATE EXCLUSIVE) and on what.
2. **Duration class** — instant (metadata-only), proportional to table size (rewrite/full scan), or proportional to write traffic (validation).
3. **Blast radius** — what queries block behind it, and the queueing trap: even a fast ACCESS EXCLUSIVE waits behind long-running queries while EVERYTHING queues behind it. Flag missing `lock_timeout` / `statement_timeout` settings.
Then check the classic killers:
- `ADD COLUMN` with volatile default or NOT NULL (version-dependent rewrite)
- `CREATE INDEX` without `CONCURRENTLY` (and: CONCURRENTLY cannot run in a transaction — is this runner transactional?)
- `ALTER COLUMN TYPE` (almost always a rewrite; propose the new-column + backfill + swap dance)
- Adding FK / CHECK / NOT NULL without `NOT VALID` + later `VALIDATE CONSTRAINT`
- Backfill `UPDATE` without batching (lock escalation, replication lag, bloat) — demand batched updates with sleep and a progress predicate
- Dropping/renaming a column or table the currently-deployed application code still references — ask for the deploy ordering
Output:
- Verdict per statement: SAFE / SAFE-WITH-SETTINGS / UNSAFE
- For anything not SAFE: the rewritten migration as an ordered, numbered runbook (each step independently deployable, with the app-deploy steps interleaved where required), including exact `SET lock_timeout` values and the retry rule.
- One paragraph: the single riskiest moment in the runbook and how to watch it (which pg_stat/processlist query to have open).
Proof of work
Example outputs
Statement 2 — `ALTER TABLE orders ADD COLUMN refunded_at timestamptz NOT NULL DEFAULT now();` Lock: ACCESS EXCLUSIVE on orders. Duration: metadata-only on PG 14 (non-volatile default is fine, now() is evaluated once) — BUT verdict SAFE-WITH-SETTINGS, not SAFE: with no lock_timeout, this waits behind any long transaction while every read/write on orders queues behind IT. At 40M rows and ~900 writes/min that queue becomes an outage in seconds. Runbook step: SET lock_timeout = '2s'; run the ALTER; on failure retry up to 5x with jitter. Watch: SELECT pid, state, wait_event, query FROM pg_stat_activity WHERE wait_event_type = 'Lock'; Statement 3 — `CREATE INDEX idx_orders_refunded ON orders (refunded_at);` Verdict: UNSAFE as written — plain CREATE INDEX takes SHARE lock, blocking all writes for the full build (~4–6 min at this size). Rewrite: CREATE INDEX CONCURRENTLY, run OUTSIDE the migration transaction (your runner wraps migrations in a transaction — move this to a post-deploy step), and check pg_index.indisvalid afterwards; a failed concurrent build leaves an invalid index to drop and retry.
Comments (0)