DB Migrations Agent Skill

Database migrations without downtime

A production migration is a sequence, not a statement. The moment you treat ALTER TABLE ... NOT NULL

as one atomic change, you have already lost: the lock blocks every writer, the deploy that depends on the

new column races the deploy that still reads the old one, and a rollback means a second outage. Your job

is to decompose one risky change into a chain of small steps where **every intermediate state is

independently deployable** — the old app keeps working, the new app keeps working, and you can stop at any

point without an outage.

This skill is engine-neutral. It owns the *choreography* — what order to run things in and how to keep each

DDL from freezing the table — across Postgres, MySQL, and serverless variants, whatever runner you use

(Flyway, Alembic, golang-migrate, drizzle-kit, raw SQL).

Route elsewhere

• Postgres lock internals, EXPLAIN/ANALYZE, indexing strategy, RLS, PgBouncer → ../postgresdb/SKILL.md.
• Writing schema and running drizzle-kit specifically → ../drizzle-orm/SKILL.md.
• PlanetScale deploy-request / branch cutover (Vitess online DDL) → ../planetscale/SKILL.md.
• Creating/verifying backups, restore drills, PITR → ../backups/SKILL.md (a migration *requires* a backup; making it lives there).
• Pure query logic — joins, windows, CTEs → ../sql/SKILL.md (backfill batching is here; query correctness is there).

Step 1 — Is this change online-safe in one step?

Most outages come from running a multi-step change as a single statement. Decide first:

| Change | One step? | Why |

|---|---|---|

| Add nullable column, no volatile default | Yes | Metadata-only on PG 11+/modern MySQL; no rewrite, no long lock. |

| Add column with a constant default | Yes (PG 11+) | Stored as a catalog default, no table rewrite. |

| Add column with a volatile/expression default | No | Rewrites every row under lock → expand-contract. |

| Add NOT NULL to existing/new column | No | Full-table validation scan under lock → add-nullable → backfill → validate. |

| Rename column / table | No | App reads the old name; needs dual-name window → expand-contract. |

| Change column type (non-trivial) | No | Rewrites rows, may invalidate the running app → new column + backfill. |

| Split / merge columns | No | Two-sided data move → expand-contract. |

| Drop column / table | No | Running app may still reference it → contract only after a cooling period. |

| Add foreign key | No | ADD CONSTRAINT validates all rows under lock → add NOT VALID then VALIDATE. |

| Create index | No, but cheap | Plain CREATE INDEX locks writes → use CONCURRENTLY. |

If the row says No, you are doing expand-contract. Go to Step 2.

Step 2 — The expand-contract loop

Three phases, each tied to a distinct deploy. Worked example: rename users.email → users.email_address.

Expand — add the new structure, start dual-writing, leave the old one fully working.

-- migration 1 (deploy A): additive only, old app untouched
SET lock_timeout = '1s';
ALTER TABLE users ADD COLUMN email_address text;  -- nullable, no default → metadata-only

App deploy A: write BOTH columns on every insert/update; keep READING email.

Backfill — copy historical data in small background batches (Step 4), then verify counts/checksums.

Switch reads — once backfilled and verified, deploy the app that reads email_address.

App deploy B: read email_address; still dual-write both.  ← cooling period starts here

Contract — only after a cooling period (hours to days) where the new app has run clean and you can

still roll back the app without touching schema, stop writing the old column and drop it.

-- migration 2 (deploy C): runs days later, after cooling period
SET lock_timeout = '1s';
ALTER TABLE users DROP COLUMN email;

The cooling period is the point. It is what lets you roll back app deploy B to app deploy A with **zero

schema change** — because the old column still exists and is still being written. Contracting early throws

that safety away.

Step 3 — The N/N-1 rule

At every moment, two app versions can be live: the one running and the one rolling out. **The schema must

be compatible with both, and each app must tolerate the other's schema.** That is the entire reason

dual-write exists — it makes the schema a superset that satisfies app N and app N+1 simultaneously.

schema:  +email_address ......................... -email
apps:    [N reads email] [N+1 reads email_address] [N+2 ignores email]
              ^ both alive during rollout ^         ^ safe to drop now ^

If you ever have a single instant where one deployed app version cannot run against the current schema,

your migration is not zero-downtime — it is a timed outage.

Step 4 — Make each DDL statement safe

Always set a short lock_timeout first. A DDL waiting on a lock builds a *queue* — every query behind

it stalls too, and one slow ALTER freezes the whole table. A short timeout makes the migration fail fast so

traffic keeps flowing; you retry instead of taking the site down.

-- Bad: unbounded wait; if a lock is held, this queues every reader/writer behind it
ALTER TABLE orders ADD COLUMN region text;

-- Good: fail fast, retry
SET lock_timeout = '1s';
ALTER TABLE orders ADD COLUMN region text;

Create indexes CONCURRENTLY, outside a transaction. Plain CREATE INDEX takes a SHARE lock and

blocks all writes for its whole duration — that is the "our deploy deadlocks when we add an index" symptom.

CONCURRENTLY takes SHARE UPDATE EXCLUSIVE and does not block writes, but **cannot run inside a transaction

block**. Runners (Flyway, Alembic, Django, Rails) wrap each migration in a transaction by default, so you

must opt that step out — see references/tools-and-runners.md.

-- Bad: locks out every writer for the full build
CREATE INDEX idx_orders_region ON orders (region);

-- Good: non-blocking; this statement must NOT be inside BEGIN/COMMIT
CREATE INDEX CONCURRENTLY idx_orders_region ON orders (region);

> A failed CONCURRENTLY build leaves an INVALID index behind. Drop it (DROP INDEX CONCURRENTLY) before

> retrying. For PG-specific lock-level detail, see ../postgresdb/SKILL.md.

Add NOT NULL in four steps, never in one. A single ALTER ... SET NOT NULL (or `ADD COLUMN ... NOT

NULL DEFAULT <volatile>`) scans/rewrites the whole table under lock.

-- Bad: full-table validation/rewrite under an exclusive lock
ALTER TABLE users ADD COLUMN status text NOT NULL DEFAULT 'active';  -- on a 40M-row table = outage

-- Good: add nullable, backfill in batches (Step 5), then add a constraint without a blocking scan
SET lock_timeout = '1s';
ALTER TABLE users ADD COLUMN status text;                              -- 1. nullable
-- 2. backfill in batches (Step 5), app dual-writes the new column
ALTER TABLE users ADD CONSTRAINT users_status_nn
  CHECK (status IS NOT NULL) NOT VALID;                                -- 3. instant, no scan
ALTER TABLE users VALIDATE CONSTRAINT users_status_nn;                 -- 4. scans WITHOUT blocking writes
-- PG 12+: a validated CHECK (col IS NOT NULL) lets SET NOT NULL skip its own scan
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

Same NOT VALID → VALIDATE pattern for adding a foreign key. The full per-change checklists live in

references/expand-contract-playbook.md.

Step 5 — Backfill at scale

Never one giant UPDATE. A single statement over millions of rows holds locks, bloats WAL/undo, and

pins replicas behind a wall of lag. Loop bounded chunks, commit each, throttle on lag, then verify.

-- Bad: one statement locks rows, balloons WAL, spikes replica lag for minutes
UPDATE users SET email_address = email WHERE email_address IS NULL;

-- Good: bounded PK-range batches, committed individually, throttled between batches
-- pseudo-loop (driven by your runner/script): a few thousand rows per batch
UPDATE users
SET email_address = email
WHERE id BETWEEN :lo AND :hi
  AND email_address IS NULL;
-- commit; check replica lag; sleep if lag/error-rate is high; advance :lo/:hi; repeat

Watch replication lag and the app error rate between batches; back off when either climbs. **Verify before

contracting** — row counts and a checksum of old-vs-new must match. For very large sets, snapshot + CDC

instead of an online loop. Postgres and MySQL backfill scripts, the lag-throttle, and checksum queries are

in references/backfill-and-batching.md.

Step 6 — Rollback reality

Forward-only is the prevailing 2025-2026 production stance. Reliable down() migrations are rarely

worth the effort — whether a rollback is even safe depends on the migration type and how much time has

passed (data has changed under you). Treat applied migrations as immutable and fix forward.

Your real safety net is not a down() script — it is the expand-contract structure itself: because the old

column/table still exists during the cooling period, "rolling back" is just redeploying the previous app.

A reversible down-migration is only worth writing when the change is additive-only and pre-traffic

(e.g. a brand-new table no app version reads yet). For anything that touched live data, a down-migration is

a trap that looks like a seatbelt.

Step 7 — Pick a runner / OSC tool

Versioned runners replay ordered scripts; declarative tools diff your target schema. Pick by stack:

| Stack / need | Tool (2026) | Note |

|---|---|---|

| JVM, SQL-first, 50+ DBs | Flyway 12.6.x | Version-based; Redgate dropped the Teams tier in 2025. |

| Max format flexibility | Liquibase | SQL/XML/YAML/JSON changelogs; most verbose. |

| Python / SQLAlchemy | Alembic 1.18.x | Requires Python ≥3.10; plugin system since 1.18.0. |

| Go | golang-migrate | The Go standard. |

| TypeScript / Drizzle | drizzle-kit | Mechanics in ../drizzle-orm/SKILL.md; safety sequence here. |

| Schema-as-code, planned diffs | Atlas | Declarative — you give the target, it plans the diff. |

| Big MySQL ALTER, native online DDL can't keep it online | gh-ost or pt-osc | See below. |

gh-ost vs pt-osc for a large MySQL ALTER: gh-ost is triggerless — it copies in chunks and replays

the binlog, throttles on replica lag, and gives easier pause/cutover control; prefer it on a very busy

master. pt-osc is trigger-based — strictly consistent, but the triggers add write overhead and can fail

to acquire the metadata lock under highly concurrent or long-transaction load. Decision detail and

per-runner transaction opt-out in references/tools-and-runners.md.

Step 8 — Gate migrations in CI

Add a static linter for migration SQL before merge, so a bare CREATE INDEX or a one-shot NOT NULL

never reaches review. Squawk is the standard for Postgres migration SQL — it flags adding NOT NULL

columns, non-concurrent index creation, blocking constraint adds, and column drops.

# CI step: fail the PR on dangerous migration DDL
- name: Lint migrations
  run: npx squawk@latest migrations/*.sql

Config snippet and rule notes in references/tools-and-runners.md. scripts/verify.sh in this skill runs

the same static checks against the example migrations shipped in references/.

Anti-patterns

| Anti-pattern | Why it hurts | Do instead |

|---|---|---|

| ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT <expr> on a big table | Full-table rewrite under lock = outage | add nullable → backfill → NOT VALID → VALIDATE (Step 4) |

| Plain CREATE INDEX in prod | SHARE lock blocks all writes for the build → deploy "deadlock" | CREATE INDEX CONCURRENTLY, outside a transaction |

| DDL with no lock_timeout | Builds a lock queue; one slow ALTER freezes the table | SET lock_timeout='1s' first, fail fast, retry |

| One giant backfill UPDATE | Locks rows, bloats WAL, spikes replica lag | bounded batches, commit each, throttle on lag (Step 5) |

| Renaming a column the running app still reads | Instant errors for app version N | dual-name window: add new, dual-write, switch reads, then drop |

| Contracting before the cooling period | Kills your no-schema-change rollback path | wait until the new app has run clean; then drop |

| Relying on down() in prod | Data already changed; rollback is unsafe or wrong | forward-only; expand-contract IS the rollback (Step 6) |

| Coupling app + schema in one deploy | Breaks N/N-1; an instant where some app can't run | separate deploys; schema is a superset of both (Step 3) |