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.