db-migration-guardian
A migration that "works on my machine" can still take production down by holding an ACCESS EXCLUSIVE lock on a hot table for 40 seconds, or by deleting a column the currently-deployed code still selects. This skill makes the safe path the default: every schema change is decomposed into steps that are individually backwards-compatible, lock-aware, and reversible.
Use this BEFORE
- Any
ALTER TABLE on a table a live deployment reads or writes.
- Adding/removing/renaming a column, changing a type, adding a constraint or index.
- Any data backfill that touches more than a few thousand rows.
If the table is genuinely unused by anything running, you can skip the ceremony — but confirm that first, don't assume it.
The core rule: expand → migrate → contract
Never change a column's meaning in one deploy. Split every breaking change across at least two deploys so the old and new code both work against the intermediate schema.
- Expand — add the new shape additively (new nullable column, new table, new index). Old code ignores it; new code can start writing it. Deploy code that writes both old and new.
- Migrate — backfill existing rows into the new shape in batches. Switch reads to the new shape. Deploy code that reads new, still writes both.
- Contract — once nothing reads or writes the old shape, drop it. Deploy code that only uses the new shape, then run the drop migration.
A rename is never RENAME COLUMN on a live table — it is: add new col → backfill → dual-write → switch reads → stop writing old → drop old. Same for a type change.
Lock-aware DDL (Postgres)
The danger isn't the DDL itself, it's the lock it takes and how long it holds it behind a queue of queries.
| Operation |
Safe? |
Do this instead |
ADD COLUMN (no default, nullable) |
✅ fast, metadata-only |
— |
ADD COLUMN ... DEFAULT <const> |
✅ on PG 11+ |
Fine; volatile defaults still rewrite — avoid those. |
ADD COLUMN ... NOT NULL |
⚠️ |
Add nullable → backfill → add CHECK (col IS NOT NULL) NOT VALID → VALIDATE CONSTRAINT → set NOT NULL. |
CREATE INDEX |
❌ blocks writes |
CREATE INDEX CONCURRENTLY (outside a txn). |
DROP INDEX |
⚠️ |
DROP INDEX CONCURRENTLY. |
ADD FOREIGN KEY / ADD CHECK |
❌ full scan under lock |
Add ... NOT VALID, then VALIDATE CONSTRAINT (takes a weaker lock). |
ALTER COLUMN TYPE |
❌ table rewrite + lock |
New column + backfill + swap (expand-contract). |
SET NOT NULL directly |
❌ full scan under lock |
Via validated CHECK constraint as above. |
Always set a short lock_timeout (e.g. SET lock_timeout = '3s') before DDL on a hot table, so a migration that can't get its lock fails fast and retries instead of stacking a queue of blocked queries behind it. Pair with a statement_timeout for backfills.
MySQL notes: prefer online DDL (ALGORITHM=INPLACE, LOCK=NONE) where the engine supports it; for the rest use gh-ost / pt-online-schema-change rather than a blocking ALTER.
Backfills
- Batch by primary key range (
WHERE id > $last LIMIT 1000), commit each batch, sleep briefly between batches to leave headroom for live traffic.
- Make the backfill idempotent and resumable — store the last processed key so a crash mid-run just resumes.
- Never
UPDATE a whole large table in one statement: it's one long transaction holding row locks and bloating the WAL.
Every migration ships with its down
- Write and test the rollback before applying. Apply → assert new state → roll back → assert original state → apply again. A migration you can't reverse is a migration you can't deploy on a Friday.
- Expand steps are trivially reversible (drop the additive thing). Contract steps are the dangerous ones — only run a contract once you've confirmed, in metrics or logs, that nothing touches the old shape.
- Backfills are usually not reversible; that's fine as long as the schema change around them is. Note it explicitly.
Procedure
- State the end-state schema and diff it against current.
- Decompose into expand/migrate/contract steps; identify which deploy each step rides with.
- For each DDL step, classify its lock from the table above; rewrite any ❌ into its safe form.
- Write forward + rollback for each step. Add
lock_timeout/statement_timeout guards.
- Dry-run against a production-like snapshot; measure lock time and duration on realistic data volume, not an empty dev table.
- Backfill in batches; verify counts (
old populated == new populated) before switching reads.
- Only after reads/writes have moved and you've confirmed the old shape is cold: run the contract migration.
Definition of done
- No step breaks the currently-deployed code.
- No DDL takes a table-blocking lock on a hot table (verified, not assumed).
- Rollback tested for every schema step.
- Backfill batched, idempotent, resumable; row counts reconciled.
- Migration is checked in with the code deploy it's coupled to, and the coupling is documented (which migration must run before/after which deploy).
1---2name: db-migration-guardian3description: Plan and apply relational schema migrations safely against a live database, with zero-downtime as the default. Use before writing or running ANY migration that alters a table other services or a running deployment still read/write. Enforces expand-contract, backwards-compatible steps, lock-aware DDL, and a tested rollback. Postgres-first, with MySQL notes.4---56# db-migration-guardian78A migration that "works on my machine" can still take production down by holding an `ACCESS EXCLUSIVE` lock on a hot table for 40 seconds, or by deleting a column the currently-deployed code still selects. This skill makes the safe path the default: every schema change is decomposed into steps that are individually backwards-compatible, lock-aware, and reversible.910## Use this BEFORE11- Any `ALTER TABLE` on a table a live deployment reads or writes.12- Adding/removing/renaming a column, changing a type, adding a constraint or index.13- Any data backfill that touches more than a few thousand rows.1415If the table is genuinely unused by anything running, you can skip the ceremony — but confirm that first, don't assume it.1617## The core rule: expand → migrate → contract18Never change a column's meaning in one deploy. Split every breaking change across at least two deploys so the old and new code both work against the intermediate schema.19201. **Expand** — add the new shape *additively* (new nullable column, new table, new index). Old code ignores it; new code can start writing it. Deploy code that writes both old and new.212. **Migrate** — backfill existing rows into the new shape in batches. Switch reads to the new shape. Deploy code that reads new, still writes both.223. **Contract** — once nothing reads or writes the old shape, drop it. Deploy code that only uses the new shape, then run the drop migration.2324A rename is never `RENAME COLUMN` on a live table — it is: add new col → backfill → dual-write → switch reads → stop writing old → drop old. Same for a type change.2526## Lock-aware DDL (Postgres)27The danger isn't the DDL itself, it's the lock it takes and how long it holds it behind a queue of queries.2829| Operation | Safe? | Do this instead |30| --- | --- | --- |31| `ADD COLUMN` (no default, nullable) | ✅ fast, metadata-only | — |32| `ADD COLUMN ... DEFAULT <const>` | ✅ on PG 11+ | Fine; volatile defaults still rewrite — avoid those. |33| `ADD COLUMN ... NOT NULL` | ⚠️ | Add nullable → backfill → add `CHECK (col IS NOT NULL) NOT VALID` → `VALIDATE CONSTRAINT` → set `NOT NULL`. |34| `CREATE INDEX` | ❌ blocks writes | `CREATE INDEX CONCURRENTLY` (outside a txn). |35| `DROP INDEX` | ⚠️ | `DROP INDEX CONCURRENTLY`. |36| `ADD FOREIGN KEY` / `ADD CHECK` | ❌ full scan under lock | Add `... NOT VALID`, then `VALIDATE CONSTRAINT` (takes a weaker lock). |37| `ALTER COLUMN TYPE` | ❌ table rewrite + lock | New column + backfill + swap (expand-contract). |38| `SET NOT NULL` directly | ❌ full scan under lock | Via validated `CHECK` constraint as above. |3940Always set a short `lock_timeout` (e.g. `SET lock_timeout = '3s'`) before DDL on a hot table, so a migration that can't get its lock fails fast and retries instead of stacking a queue of blocked queries behind it. Pair with a `statement_timeout` for backfills.4142**MySQL notes:** prefer online DDL (`ALGORITHM=INPLACE, LOCK=NONE`) where the engine supports it; for the rest use `gh-ost` / `pt-online-schema-change` rather than a blocking `ALTER`.4344## Backfills45- Batch by primary key range (`WHERE id > $last LIMIT 1000`), commit each batch, sleep briefly between batches to leave headroom for live traffic.46- Make the backfill idempotent and resumable — store the last processed key so a crash mid-run just resumes.47- Never `UPDATE` a whole large table in one statement: it's one long transaction holding row locks and bloating the WAL.4849## Every migration ships with its down50- Write and *test* the rollback before applying. Apply → assert new state → roll back → assert original state → apply again. A migration you can't reverse is a migration you can't deploy on a Friday.51- Expand steps are trivially reversible (drop the additive thing). Contract steps are the dangerous ones — only run a contract once you've confirmed, in metrics or logs, that nothing touches the old shape.52- Backfills are usually not reversible; that's fine as long as the *schema* change around them is. Note it explicitly.5354## Procedure551. State the end-state schema and diff it against current.562. Decompose into expand/migrate/contract steps; identify which deploy each step rides with.573. For each DDL step, classify its lock from the table above; rewrite any ❌ into its safe form.584. Write forward + rollback for each step. Add `lock_timeout`/`statement_timeout` guards.595. Dry-run against a production-like snapshot; measure lock time and duration on realistic data volume, not an empty dev table.606. Backfill in batches; verify counts (`old populated == new populated`) before switching reads.617. Only after reads/writes have moved and you've confirmed the old shape is cold: run the contract migration.6263## Definition of done64- No step breaks the currently-deployed code.65- No DDL takes a table-blocking lock on a hot table (verified, not assumed).66- Rollback tested for every schema step.67- Backfill batched, idempotent, resumable; row counts reconciled.68- Migration is checked in with the code deploy it's coupled to, and the coupling is documented (which migration must run before/after which deploy).
omonuj