DB Migrations

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:

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:

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