Expand-contract: zero-downtime for breaking schema changes

A team renames users.username to users.handle in one ALTER TABLE statement at deploy time. The migration commits instantly. But the rolling deploy is still in progress — old pods are still running SQL that references users.username. Every request from an old pod fails with “column does not exist”. A fast migration caused a real outage.

Why rename/drop/type-change cannot be done in one step

A rolling deploy — the only safe deploy strategy at any scale — runs old and new code simultaneously for seconds to minutes. If a migration commits before all old pods drain:

• Rename: old code references old column name → column does not exist errors.
• Drop: old code references dropped column → same.
• Type change: old code expects int, column is now text → type cast errors.

The invariant that must hold at every moment: the current schema is simultaneously compatible with the previous application version and the next one. A one-step rename, drop, or type change breaks this invariant.

The general expand-contract framework

Every breaking change decomposes into six phases:

Column rename walkthrough: username → handle

The canonical example. Total span: 3–7 days.

Day 1 — Expand:

-- Migration 1: instant, no default
ALTER TABLE users ADD COLUMN handle TEXT;

Day 1 — Dual-write deploy: App code now writes both username and handle on every INSERT/UPDATE. Old code still reads username. New code reads username (for now).

Day 2 — Backfill:

-- Outside a migration transaction (batched loop):
UPDATE users SET handle = username
WHERE handle IS NULL
  AND id BETWEEN :batch_start AND :batch_end;
-- Repeat until SELECT COUNT(*) FROM users WHERE handle IS NULL = 0

Batches of 1k–10k rows, each a short transaction with pg_sleep(0.1) between.

Day 3 — Cut over reads: Deploy code that reads handle exclusively. Writes still go to both columns. Watch dashboards for 24h.

Day 5 — Stop dual-write: Deploy code that writes only handle. Wait for rolling deploy to complete.

Day 7 — Contract:

-- Migration 4: fast (metadata only)
ALTER TABLE users DROP COLUMN username;

Every step is independently deployable and reversible. Zero-downtime throughout.

Down migrations are not rollback

Down migrations (reverse SQL of an up migration) appear in many tools (down.sql counterpart files). They work in development but are dangerous in production:

• A down migration that drops handle also destroys all the data that new code wrote to it.
• If the up migration was ADD COLUMN, the down is DROP COLUMN — destructive.

Production rollback is forward: write a new migration that corrects the problem, deploy it, and move forward. Treat down migrations as development scaffolding only.