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.
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.
allowed-tools
Read,Grep,Glob,Edit,Write,Bash
metadata
{"category":"Backend & Infrastructure","tags":["database","migration","postgres","mysql","zero-downtime","expand-contract"],"provenance":{"kind":"first-party","owners":["port-daddy"]},"pairs-with":[{"skill":"background-job-orchestrator","reason":"Batched backfills run as supervised background jobs with retries and throttling — exactly the queue/worker machinery that skill owns."},{"skill":"event-driven-architecture-expert","reason":"Dual-write and outbox sequencing during a service split lean on the messaging and delivery-guarantee patterns that skill covers."}],"io-contract":{"kind":"deliverable","consumes":["[Truncated]","[Truncated]"],"produces":["[Truncated]","[Truncated]"]}}
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.
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 takes. () The non-obvious failure mode is the : if your DDL waits behind a long-running transaction, every subsequent and queues behind your DDL — the table is effectively dark for reads and writes until your statement either acquires the lock or fails. ()
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 ADDCOLUMN 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:
In Postgres 11 and later, ADD COLUMN with a non-volatileDEFAULT 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
ADDCOLUMN currency TEXT NOT NULLDEFAULT'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 ADDCOLUMN 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 DROPCOLUMN 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 ISNULL
LIMIT batch_size
FORUPDATESKIP LOCKED -- prevents two backfills from contending
);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
PERFORM pg_sleep(0.1); -- give autovacuum + replication roomCOMMIT;
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)
Dual writing: app writes to BOTH old and new schema on every mutation. Reads still go to OLD.
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:
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)
Deterministic Audit
Before running (or reviewing) a production migration, write it as a JSON plan matching
schemas/zero-downtime-database-migration-plan.schema.json and run the deterministic
auditor:
auditZeroDowntimeDatabaseMigration(plan) (in
scripts/zero_downtime_database_migration_audit.mjs) turns this skill's core sequence —
expand → migrate → contract, three deploys minimum — and its Quality Gates into
machine-checkable rules over structured fields: a breaking change squeezed into fewer
than three deploys, a migration session with no lock_timeout, CREATE INDEX without
CONCURRENTLY on a hot table, an FK add skipping NOT VALID → VALIDATE CONSTRAINT,
a single-statement or unthrottled backfill, dual writes with no feature flag, a contract
step with no verification or rollback plan, and gh-ost pointed at a table with foreign
keys or non-ROW binlogs. It returns { pass, score, findings, recommendations }.
examples/sample-input.json is a three-deploy Postgres column rename that clears every
gate (pass: true). Changes are tracked in CHANGELOG.md.
NOT for
Greenfield schema design — different concern, no migration constraint. No dedicated skill yet.
