zero-downtime-database-migration

name	zero-downtime-database-migration
description	Use when changing schema on a live production database without dropping traffic, doing expand/contract migrations, backfilling new columns on million-plus-row tables, dual-writing across schemas, renaming a hot column, splitting a table, or adding a foreign key to a big table. Triggers: ALTER TABLE locks the world, "must be removed in a separate deploy", expand-contract-cleanup phases, NOT NULL on populated column, foreign-key add on big table, dropping a column still read by old replicas, lock_timeout choice, gh-ost vs pt-online-schema-change. NOT for greenfield schema design, ORM-managed migration mechanics (Prisma/Drizzle), D1/Supabase CLI quirks, or NoSQL document migrations.
category	Backend & Infrastructure
allowed-tools	Read,Grep,Glob,Edit,Write,Bash
tags	["database","migration","postgres","mysql","zero-downtime","expand-contract"]

Zero-Downtime Database Migration

A schema change on a live production database is three changes happening at once: a deploy, a backfill, and a contract update between code and storage. The job is to never have a moment where the database and the running code disagree on what's required. The shape that always works — and Stripe explicitly names this pattern — is the 4-step dual-writes sequence: dual write → change reads → reverse writes → remove old. (Stripe Engineering — Online migrations at scale) The sibling community framing is expand → migrate → contract. (Pete Hodgson — Expand/Contract) Three deploys minimum. There is no shortcut that's safe under traffic.

Jump to your fire:

• ALTER TABLE locks reads and writes for minutes → Lock levels and the lock queue
• Need to add a NOT NULL column → Add column safely (pg11+)
• Need to add an index without an outage → CREATE INDEX CONCURRENTLY
• Foreign-key add timing out → FK add in two steps (NOT VALID then VALIDATE)
• Need to rename a hot column → Rename via expand/contract
• Backfill is dragging replication lag up → Backfill in batches
• App version N can't read what version N+1 writes → Dual-writes pattern
• Postgres vs MySQL tooling choice → MySQL: gh-ost vs pt-online-schema-change

When to use

• Live database with traffic; downtime windows are not an option (or are limited to seconds, not minutes).
• Renaming, splitting, or merging columns/tables that are read by hot paths.
• Adding a NOT NULL column to a table with existing rows.
• Changing a column type (int → bigint when you're approaching 2.1B rows).
• Adding a foreign key to a populated big table.
• Migrating data between two schemas during a service split.

Core capabilities

Lock levels and the lock queue

Most ALTER TABLE statements take an ACCESS EXCLUSIVE lock — the strongest Postgres has, conflicting with every other lock type including the ACCESS SHARE that plain SELECT takes. (Postgres — Explicit Locking) The non-obvious failure mode is the lock queue: if your DDL waits behind a long-running transaction, every subsequent SELECT and UPDATE queues behind your DDL — the table is effectively dark for reads and writes until your statement either acquires the lock or fails. (Xata — Schema changes and the Postgres lock queue)

The standard mitigation pair, recommended by both pganalyze-style operational guides and Xata's writeup:

SET lock_timeout = '2s';
SET statement_timeout = '60s';

BEGIN;
ALTER TABLE orders ADD COLUMN refund_reason TEXT;
COMMIT;

A short lock_timeout (commonly < 2s) bounds the damage if a long-running transaction is in front of you, then your migration runner retries with backoff. Never run migrations without lock_timeout. (Xata, PostgresAI — Zero-downtime Postgres schema migrations need this)

A few ALTER TABLE forms take weaker locks — know them and prefer them where possible:

Operation	Lock	Source
ADD COLUMN (no volatile DEFAULT, no rewrite)	Brief ACCESS EXCLUSIVE (metadata only)	pg docs
ADD CONSTRAINT … NOT VALID	Brief ACCESS EXCLUSIVE (metadata only)	pg docs
VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE (concurrent reads + writes OK)	pg docs
ADD FOREIGN KEY (forces validation)	SHARE ROW EXCLUSIVE (weaker than ACCESS EXCLUSIVE)	pg docs
CREATE INDEX CONCURRENTLY	No write lock; longer	pg docs
SET DATA TYPE (binary-coercible, e.g. text ↔ varchar)	No table rewrite	pg docs

Add column safely (pg11+)

In Postgres 11 and later, ADD COLUMN with a non-volatile DEFAULT no longer rewrites the table — the default is stored in metadata and returned when old rows are read. The Postgres docs spell this out explicitly: "the default value is evaluated at the time of the statement and the result stored in the table's metadata… In neither case is a rewrite of the table required." (pg docs)

-- pg11+: O(1), metadata-only.
ALTER TABLE orders
  ADD COLUMN currency TEXT NOT NULL DEFAULT 'USD';

But this becomes a full rewrite if the default is volatile (now(), clock_timestamp(), random()), or if you ADD a stored generated column, an identity column, or a column with a constrained-domain type. (pg docs) For those, do expand/backfill/contract.

There's also a subtle optimisation for SET NOT NULL: if a valid CHECK (col IS NOT NULL) constraint already exists, ALTER COLUMN ... SET NOT NULL skips the table scan. (pg docs) Useful when you can't tolerate the scan but want the schema constraint.

CREATE INDEX CONCURRENTLY

CREATE INDEX without CONCURRENTLY takes an ACCESS EXCLUSIVE lock — every read and write blocks until it finishes. On a 50M-row table that is an outage. CONCURRENTLY does the build without blocking writes; the cost is a longer build and an additional follow-up if the build aborts (the index is left INVALID and you must DROP INDEX CONCURRENTLY and retry). (pg docs — CREATE INDEX)

CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC);

-- If a prior attempt aborted:
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status_created;

CONCURRENTLY cannot run inside a transaction block, which means most migration tools need a special path for it. Verify yours does.

FK add in two steps

Adding a foreign key with the default behavior takes a brief SHARE ROW EXCLUSIVE lock but has to scan the table to validate every row — on big tables that scan is the outage. The two-step pattern:

-- Step 1: declare. Brief lock. No scan.
ALTER TABLE orders
  ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

-- Step 2: validate. Only SHARE UPDATE EXCLUSIVE — concurrent reads + writes still work.
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

Postgres's docs are explicit on the lock weaker than ACCESS EXCLUSIVE here: "validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered." (pg docs)

Rename via expand/contract

-- DON'T: a single rename creates a window where some pods read the old name and fail.
ALTER TABLE users RENAME COLUMN nickname TO display_name;

Three deploys instead:

-- Deploy 1 (EXPAND): add the new column, app dual-writes, app reads OLD.
ALTER TABLE users ADD COLUMN display_name TEXT;
-- App code now writes BOTH nickname AND display_name on every update.

-- Backfill historical rows (batched, see below).
-- Deploy 2 (READ SWAP): app reads display_name, falls back to nickname if NULL.
-- Deploy 3 (CONTRACT): drop old column. Now safe — no pod reads it.
ALTER TABLE users DROP COLUMN nickname;

Stripe explicitly notes their version of this requires both code paths active and uses GitHub's Scientist library to compare results in production, alerting if the two paths diverge: "Scientist is a Ruby library that allows you to run experiments and compare the results of two different code paths, alerting you if two expressions ever yield different results in production." (Stripe)

Backfill in batches

-- WRONG: single statement; locks rows, blows up replication lag, can run for hours.
UPDATE big_table SET new_col = compute(old_col);

-- RIGHT: batched, advisory-locked, with sleep, traceable.
DO $$
DECLARE
  batch_size INT := 5000;
  rows_updated INT := 1;
BEGIN
  WHILE rows_updated > 0 LOOP
    UPDATE big_table SET new_col = compute(old_col)
    WHERE id IN (
      SELECT id FROM big_table
      WHERE new_col IS NULL
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED  -- prevents two backfills from contending
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    PERFORM pg_sleep(0.1);    -- give autovacuum + replication room
    COMMIT;
  END LOOP;
END $$;

Batches of 1k–10k. FOR UPDATE SKIP LOCKED lets two parallel backfills (or backfill + live writes) co-exist without deadlocks. The pg_sleep is non-negotiable on a high-write table — without it, replication lag climbs until your read replicas fall behind the SLO.

Stripe's account explicitly says they used MapReduce on Hadoop snapshots rather than querying production for the heaviest backfills: "we use MapReduce to quickly process our data in an offline, distributed fashion." (Stripe) For the petabyte-scale case, offline-then-replay-the-delta is the same idea at a different physical layer.

Dual-writes pattern

Stripe's named 4-step pattern, paraphrased from their post: (Stripe)

1. Dual writing: app writes to BOTH old and new schema on every mutation. Reads still go to OLD.
2. Backfill: copy historical rows into NEW. Verify (counts + sample diffs).
3. Change all read paths: app reads from NEW (still dual-writing). This is the moment Scientist-style verification matters most.
4. Reverse the writes: app writes only to NEW; remove dual-write. Then drop OLD.

// Phase 1 — dual write.
async function updateUser(id: string, displayName: string) {
  await db.transaction(async (tx) => {
    await tx.update('users', { id }, {
      nickname: displayName,        // OLD
      display_name: displayName,    // NEW
    });
  });
}

// Phase 3 — read swap, with fallback.
async function getUser(id: string) {
  const u = await db.select('users', { id });
  return { ...u, display_name: u.display_name ?? u.nickname };
}

Gate the dual-write itself behind a feature flag (see feature-flag-rollout-strategist). When something goes wrong mid-migration, you toggle dual-write off without redeploying.

MySQL: gh-ost vs pt-online-schema-change

Postgres mostly handles the cases above natively. MySQL needs an external tool because ALTER TABLE has historically meant copy-the-whole-table-with-a-write-lock. The two production-grade options have different tradeoffs:

Concern	gh-ost	pt-online-schema-change
Data capture	Reads MySQL binary log (no triggers) (Mydbops)	Installs triggers on the source table
Foreign keys	Not supported (Severalnines)	Limited support, but better than gh-ost
Triggers on source table	Not supported	Not supported (cannot install another trigger over them)
Pause / resume mid-migration	Yes — has binlog coordinates (Severalnines)	Limited
Replica lag awareness	First-class (built-in throttling)	Manual / external
Binlog format requirement	ROW-based replication only (Severalnines)	Any
Maturity	GitHub-built, widely deployed	Older, more battle-tested across MySQL versions

Default to gh-ost if your tables don't have foreign keys and your binlogs are ROW-based. Default to pt-online-schema-change when you must support FKs or older MySQL. Both implement the same conceptual flow: shadow table → copy + capture changes → cutover.

Verification before you contract

Before the irreversible step (drop the old column / table / index):

• Row counts match between OLD and NEW within an acceptable window.
• Sampled diff query: SELECT old_col, new_col FROM t TABLESAMPLE BERNOULLI(0.1) WHERE old_col IS DISTINCT FROM new_col LIMIT 100 returns nothing.
• Application error rate on the new path is flat.
• Replica lag normal during the entire backfill.
• Optional but powerful: a Scientist-style shadow comparison wired into production reads for a soak period. (Stripe)

If any of these fails, rollback is simply "stop reading from new, keep dual-writing." No data loss. No downtime.

Anti-patterns

Single-deploy rename

Symptom: Errors during deploy: "column foo does not exist" or "column bar does not exist", flapping during the rolling deploy. Diagnosis: Old pods and new pods disagree on the column name. Fix: Expand → backfill → contract. Three deploys. There is no shortcut.

Plain CREATE INDEX in production

Symptom: Database freezes for minutes; everything backs up; pager fires. Diagnosis: CREATE INDEX (without CONCURRENTLY) takes ACCESS EXCLUSIVE and the lock queue piles up behind it. (Xata) Fix: Always CONCURRENTLY on hot tables. CI lint that fails on CREATE INDEX without it.

No lock_timeout on the migration session

Symptom: A long autovacuum or long-running report transaction blocks your DDL; your DDL blocks every read for ten minutes. Diagnosis: Default lock_timeout is 0 (wait forever). The lock queue does the rest. (Xata) Fix: SET lock_timeout = '2s'; (or less) at the top of every migration. Tool retries with backoff.

Single-statement backfill

Symptom: Migration runs for an hour, locks rows, replicas lag 30 minutes behind. Diagnosis: UPDATE big_table SET ... without batching. Fix: Batches of 1k–10k with sleeps. Run in a separate session, not in the migration tool itself. Use FOR UPDATE SKIP LOCKED.

NOT NULL added before backfill completes

Symptom: New inserts start failing because the new column isn't supplied; CI passes because seed data has the value. Diagnosis: Constraint enforced before app code is updated. Fix: Add the column with a DEFAULT first. After app code consistently writes the column, drop the default in a later deploy if you want strict semantics.

FK add without NOT VALID

Symptom: ALTER TABLE … ADD FOREIGN KEY runs for 20 minutes blocking writes on a 100M-row table. Diagnosis: Default validation requires a full table scan with the table locked. Fix: Two-step: ADD CONSTRAINT … NOT VALID then VALIDATE CONSTRAINT separately. (pg docs)

Reverse-incompatible API + DB change in the same deploy

Symptom: Mid-rollout, half the pods 500 because old API consumers see new schema responses. Diagnosis: Two breaking changes in one release. Fix: Sequence them: API contract first (additive), DB next, drop old API later. Deploy ordering matters; document it.

Quality gates

• Test: rehearse the migration on a snapshot of production. Time the longest step. If it holds ACCESS EXCLUSIVE > 5 seconds, redesign.
• Test: dry-run the rolling deploy in staging with two app versions running concurrently; assert no 500s.
• Every migration session sets lock_timeout (commonly < 2s) and the runner retries with exponential backoff.
• Every CREATE INDEX on a > 100k-row table uses CONCURRENTLY. CI lint enforces.
• Backfill is batched (≤ 10k rows per batch) with sleeps and FOR UPDATE SKIP LOCKED.
• Renames, type changes, FK adds, and NOT NULLs added on populated columns all split across ≥ 3 deploys.
• Dual-write code is gated by a feature flag (see feature-flag-rollout-strategist) — toggle off without redeploy.
• Verification queries (counts, sample diffs) run before the contract step. Documented in the migration's runbook.
• Replica lag monitored during backfill (see grafana-dashboard-builder); pause if SLO breached.
• Postgres only: ADD FOREIGN KEY always uses NOT VALID then VALIDATE CONSTRAINT.
• MySQL only: tool selected (gh-ost vs pt-online-schema-change) based on FK + binlog-format reality, not habit.
• Rollback plan documented before deploy: how to revert each phase without data loss.
• Optional: Scientist-style shadow comparison wired into production reads for the soak window. (Stripe)

NOT for

• Greenfield schema design — different concern, no migration constraint. No dedicated skill yet.
• ORM-managed migration generator quirks (Prisma migrate, Drizzle kit) — generator-specific. → d1-and-supabase-migrations for D1 + Supabase CLI specifically.
• EXPLAIN ANALYZE / planner debugging of the queries the migration touches — different skill. → postgres-explain-analyzer.
• Document/NoSQL migrations — different consistency model.
• Pure data backfills (no schema change) — the batching pattern transfers but the surrounding sequencing doesn't.

Sources

• Stripe Engineering — Online migrations at scale (4-step dual writes; Scientist; MapReduce backfills). stripe.com/blog/online-migrations
• PostgreSQL — ALTER TABLE (lock levels, NOT VALID, ADD COLUMN DEFAULT in pg11+, FK weaker lock, SET NOT NULL with CHECK shortcut). postgresql.org/docs/current/sql-altertable.html
• PostgreSQL — Explicit Locking (table-level lock matrix). postgresql.org/docs/current/explicit-locking.html
• PostgreSQL — CREATE INDEX (CONCURRENTLY semantics, INVALID index recovery). postgresql.org/docs/current/sql-createindex.html
• Xata — Schema changes and the Postgres lock queue (lock_timeout < 2s, queue cascade behavior). xata.io/blog/migrations-and-exclusive-locks
• PostgresAI — Zero-downtime Postgres schema migrations need this: lock_timeout and retries. postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries
• Pete Hodgson — Expand/Contract: making a breaking change without a big bang. blog.thepete.net/blog/2023/12/05/expand/contract-making-a-breaking-change-without-a-big-bang/
• Severalnines — Online Schema Change for MySQL & MariaDB — gh-ost vs pt-online-schema-change. severalnines.com/.../online-schema-change-mysql-mariadb-comparing-github-s-gh-ost-vs-pt-online-schema-change
• Mydbops — gh-ost: Triggerless Online Schema Change for MySQL. mydbops.com/blog/gh-ost-for-mysql-schema-change