# DB Migration Guardian

> 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.

- Skill: `omonuj/db-migration-guardian` (Agent Skill)
- Install (CLI): `npx skillmds@latest add omonuj/db-migration-guardian`
- Raw SKILL.md: https://api.skillmd.com/api/skills/omonuj/db-migration-guardian/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: omonuj (https://skillmd.com/u/omonuj)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/omonuj/db-migration-guardian

---


# 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.

1. **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.
2. **Migrate** — backfill existing rows into the new shape in batches. Switch reads to the new shape. Deploy code that reads new, still writes both.
3. **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
1. State the end-state schema and diff it against current.
2. Decompose into expand/migrate/contract steps; identify which deploy each step rides with.
3. For each DDL step, classify its lock from the table above; rewrite any ❌ into its safe form.
4. Write forward + rollback for each step. Add `lock_timeout`/`statement_timeout` guards.
5. Dry-run against a production-like snapshot; measure lock time and duration on realistic data volume, not an empty dev table.
6. Backfill in batches; verify counts (`old populated == new populated`) before switching reads.
7. 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).