Zero‑Downtime Database Migrations

Zero downtime is less about magic and more about choreography: change the schema, backfill safely, and only then switch traffic. I have run this dance on e-commerce checkouts, ERP syncs, and plain CRUD apps — the steps never change, only the tempo does.

Outages rarely come from the SQL itself. They come from the assumption that code and schema flip at the same instant. They never do: during a rolling deploy, old pods and new pods serve traffic side by side for minutes or hours. Rename a column and the old code crashes. Add a NOT NULL constraint and half your writes bounce. Run a long ALTER that takes an exclusive lock and every request queues behind it. The cure is a rule I refuse to break: every deploy must work against both the schema before it and the schema after it.

The three-step migration pattern

Every risky schema change decomposes into three small deploys, each one boring on its own:

• Expand: add new columns or tables without breaking existing code.
• Backfill: populate data while old and new versions run side-by-side.
• Contract: remove the old path only after all reads and writes use the new schema.

Expand costs almost nothing. Add nullable columns, new tables, indexes built concurrently. Old code ignores what it does not know about, so nothing breaks. I resist adding constraints at this stage — validation comes later, once the data has caught up.

-- Expand: additive, nullable, no exclusive locks
ALTER TABLE orders ADD COLUMN customer_email text;
CREATE INDEX CONCURRENTLY idx_orders_email ON orders (customer_email);

Backfill is where discipline pays. Start dual-writing in the application first: every insert and update fills the old field and the new one. Then walk the historical rows in small batches — a few thousand at a time, with a pause between batches — while watching replication lag and lock waits. One giant UPDATE on a hot table is how a zero-downtime plan turns into an incident review.

-- Backfill: small batches, repeat until zero rows updated
UPDATE orders SET customer_email = legacy_contact
WHERE id IN (
  SELECT id FROM orders
  WHERE customer_email IS NULL
  LIMIT 5000
);

Contract comes last, and later than feels comfortable. Switch reads to the new column behind a flag, keep dual-writes for one more release, and only then drop the old path. Dropping a column is a one-way door; I walk through one-way doors slowly.

Verification in production

A migration you cannot verify is a migration you are merely hoping at. During the backfill I schedule three cheap checks: compare old versus new fields row by row, watch the ratio of NULLs in the new column trend to zero, and run the most important read-only queries against the new path before any user does. These signals are almost embarrassingly simple — and they catch drift days before a customer would.

-- Drift check: must trend to zero and stay there
SELECT count(*) AS mismatched
FROM orders
WHERE customer_email IS DISTINCT FROM legacy_contact;

If you can’t measure it, you can’t trust it — especially when you need a clean rollback. And rollback here is deliberately cheap: because every step is backwards compatible, reverting means redeploying the previous version, not restoring a backup at 3 a.m. The old column still exists, the old code still works, and the backfill simply resumes where it stopped when you try again.

Checklist before you contract

• Drift between old and new fields has been zero for a full business cycle, weekend batch jobs included.
• No query in the slow log or pg_stat_statements still touches the old column.
• Dual-writes ran for at least one release, and the rollback was actually rehearsed, not just written down.

None of this is glamorous. It is choreography: small steps, in the right order, with a way back at every point. That is what zero downtime actually means.