Zero-Downtime Database Migrations on Live Systems

Engineering question: How do you change the schema of a table holding millions of rows — renaming a column, changing a type, splitting a field — without locking the table and taking the application down?

The naive answer (ALTER TABLE ... ALTER COLUMN ... in one shot) acquires a heavy lock and rewrites the whole table. On a large, busy table that can mean minutes of blocked writes, which on a production ERP means an outage. The disciplined answer is to never make a breaking change in a single step.

The Expand–Transition–Contract pattern

Every safe schema change is split into three deploys. The core rule: the database schema and the application code are always compatible across at least two consecutive versions. Old code must work against the new schema, and new code must work against the old schema, during the transition.

Phase 1 — Expand

Add the new structure additively. Never drop or rename anything yet.

SQL

"text-white/40 italic">-- Adding a new column is cheap; it does not rewrite existing rows.
ALTER TABLE customers ADD COLUMN phone_e164 text;

Adding a nullable column is a metadata-only operation in PostgreSQL — it does not rewrite the table and does not take a disruptive lock. At this point the new column exists but is empty, and nothing reads it yet.

Phase 2 — Transition (dual-write + backfill)

Deploy application code that writes to both the old and the new column on every insert and update. Now all new data is correct in both places.

For the existing millions of rows, backfill in small batches so you never lock the whole table at once:

SQL

UPDATE customers
SET phone_e164 = to_e164(phone)
WHERE phone_e164 IS NULL
  AND id BETWEEN :start AND :end;   "text-white/40 italic">-- batch of e.g. 5,000 rows

Loop the batches with a short pause between them. Each batch is a small, fast transaction that touches few rows and releases its locks immediately, so normal traffic keeps flowing.

Once the backfill is complete and verified, switch reads over to the new column and deploy.

Phase 3 — Contract

Only now, when nothing reads or writes the old column anymore, do you remove it:

SQL

ALTER TABLE customers DROP COLUMN phone;

Because the application stopped depending on it a full deploy ago, dropping it is safe.

Why dual-writing is the key

The reason this works without downtime is that there is never a moment where the running code and the live schema disagree. The old column and the new column coexist through the entire transition, kept in sync by dual-writing, so you can roll forward or roll back at any phase without data loss.

Practical guardrails

Always batch backfills. A single large UPDATE takes one big lock; ten thousand small ones don't.
Create indexes concurrently. CREATE INDEX CONCURRENTLY builds without blocking writes (it's slower and can't run inside a transaction block — that's the trade).
Set a lock timeout on migration sessions (SET lock_timeout = '3s') so a migration that would block waits politely and fails fast instead of freezing traffic.
Decouple deploy from release. Schema changes ship ahead of the code that needs them, never in the same atomic step.

Treated this way, a migration stops being a scheduled-maintenance event and becomes a routine, reversible deploy.

Achieving Zero-Downtime Database Migrations on Live Production Systems