Zero-Downtime Database Migrations in Production

Everyone who ships backend code knows migrations can take production down. Few teams plan for the shape that actually breaks them: a rolling deploy where half the pods still run yesterday's binary against today's schema.

The failure isn't "we ran ALTER at noon instead of midnight." It's coupling — one migration that adds display_name, drops user_name, and ships in the same release as the code that still SELECTs user_name. The first old pod after the migration wins the error log lottery. Someone rolls back the app. The column is already gone. Now you're negotiating schema recovery instead of finishing the feature.

That's the coupled-deploy tax. Zero downtime isn't a tool pick — it's refusing to ship schema and code as one atomic bet.

The Coupled-Deploy Tax

Parallel change — expand, migrate, contract — is the boring name for the only discipline that survives rolling deploys. Expand: add the new thing without removing the old. Migrate: move reads, writes, and data across while both versions work. Contract: drop the old thing only after you've proved nothing touches it.

The invariant at every step: running code and live schema must stay compatible. Not compatible after you finish the deploy. Compatible while pods are mid-rollout, while a canary is at 5%, while someone pauses the pipeline to grab coffee.

Maintenance windows don't fix coupling. They just concentrate traffic so the blast radius looks intentional. If old code can still hit the database after your migration runs, you've only moved the incident to a calendar slot.

Most postmortems I've read on "migration took prod down" stop at "ALTER blocked the table." The sharper ones name the real mistake: schema and application shipped as one step, so rollback became a schema problem instead of a deploy revert. The database didn't fail. The deploy contract did.

Three Migrations That Still Page People

Competitors say "use expand-contract" and move on.

Operators still get paged on the same three changes. Here is the deploy sequence each one actually needs — not the ALTER you wish you could run.

Column Rename — Never One Migration

ALTER TABLE users RENAME COLUMN user_name TO display_name looks atomic. During a rolling deploy it is a breaking change. Old pods ask for user_name the moment the rename commits.

The online path is add, not rename:

• Expand — ADD COLUMN display_name (nullable). Old code keeps using user_name.
• Backfill — copy user_name → display_name in batches. Both columns populated; either name works for reads if you dual-write.
• Flip writes — deploy code that writes both columns (or only display_name while reads still tolerate user_name).
• Flip reads — deploy code that reads display_name. user_name is now dead weight but still your rollback story.
• Contract — drop user_name only after query logs and deploy history say nothing references it.

Five deploys for a "rename." That's the job.

Skipping the read flip to drop the old column in the same PR is how teams delete their own exit ramp — the old path is rollback insurance, not clutter.

Type Change — Shadow Column, Not In-Place ALTER

Changing amount DOUBLE PRECISION to amount_cents BIGINT with a single ALTER COLUMN ... TYPE is how you invite a table rewrite under an exclusive lock. The production-safe version is the same expand pattern with a shadow column:

• Expand — add amount_cents BIGINT nullable.
• Dual-write — new rows populate both columns; app converts on write if you can.
• Backfill — existing rows in batches (WHERE amount_cents IS NULL LIMIT 1000), throttling if replication lags.
• Read flip — deploy code that reads amount_cents.
• Constraint — add CHECK (amount_cents IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT in a follow-up migration so you don't scan under the wrong lock mode.
• Contract — drop amount.

"In-place type change" is a local-dev fantasy.

Production wants a new column and a multi-release habit.

Foreign Key — Two Transactions, Not One

Adding FOREIGN KEY (user_id) REFERENCES users(id) looks like housekeeping. Postgres has to verify every existing user_id against users. A plain ADD CONSTRAINT blocks writes on both tables until the scan finishes.

The online path Postgres documents:

ALTER TABLE orders
  ADD CONSTRAINT orders_user_id_fk
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;
 
-- separate migration / transaction
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fk;

NOT VALID enforces the constraint on new rows immediately without scanning history. VALIDATE CONSTRAINT scans existing rows under a SHARE UPDATE EXCLUSIVE lock — reads and writes continue.

Edge case worth knowing: adding a new nullable column with a FK in one ADD COLUMN can behave differently from bolting a FK onto an existing column — Postgres can skip full-table validation in the add-column path. When the column already exists full of data, treat FK add as the two-step sequence above. No prizes for discovering the difference in production.

Three migrations. Three multi-deploy stories. Same pattern underneath.

Postgres Footguns That Undo "Online"

Postgres gives you real online primitives.

Migration runners love to wrap them in transactions and undo the "online" part.

CREATE INDEX CONCURRENTLY builds without blocking writes — at the cost of two scans and longer runtime. It also cannot run inside a transaction block — a silent footgun when Flyway, Liquibase, TypeORM, and friends wrap each migration file in BEGIN/COMMIT. Your CI passes. Production fails with CREATE INDEX CONCURRENTLY cannot run inside a transaction block. The fix is boring: that one migration runs with transactions disabled, plus a short lock_timeout so a contended deploy fails fast instead of queuing behind a long query.

Same class of mistake for backfills: one giant UPDATE holds row locks until it finishes. Batch with a primary-key cursor and FOR UPDATE SKIP LOCKED (or your team's equivalent throttle). The migration should be resumable and boring, not heroic.

NOT VALID / VALIDATE and CONCURRENTLY reduce blocking. They do not prove your application tolerates both schemas. That's still an expand-contract problem — the database being online doesn't mean your ORM mapping with a hard-coded column list is.

MySQL — When "Online DDL" Isn't Online for the Fleet

On MySQL, "Online DDL" on the primary can still serialize through replication and lag the fleet. That's why shadow-table tools exist: build a ghost copy, catch up via triggers or binlog tailing, swap names in a sub-second cutover after hours of background work.

Rough tool fork:

• Foreign keys you cannot drop → pt-osc. gh-ost doesn't support FK graphs.
• Write-heavy tables where trigger overhead hurts → gh-ost. No triggers; binlog applier; --postpone-cut-over-flag-file when lag spikes and you need to defer the swap.
• Need resume after a dead process → pt-osc with --resume and --nodrop-* flags. gh-ost doesn't resume a dead run — you plan for retry from scratch.

Native DDL might win on small tables or when MySQL 8 instant algorithms apply. The mistake is assuming "online on the master" means "online for replicas and on-call."

Expand-contract still applies on MySQL. gh-ost changes how you ALTER; it doesn't let you rename and drop in one deploy while old code rolls.

Column rename: add, backfill, flip, drop — never RENAME under rolling traffic. Type change: shadow column, not in-place ALTER TYPE. Foreign key: NOT VALID, then VALIDATE. Same expand-contract spine, three different scar tissues.

The rollback conversation nobody wants usually starts with "we shipped schema and code together." Split the deploys, keep the old column until you're embarrassed by how long it's been there, and the pager stays quiet long enough to finish the feature.