Zero-downtime database migrations: the expand-contract discipline

01The rule that governs everything

During any deploy, two versions of your application run simultaneously - the old one still serving requests and the new one starting up. That window may be seconds or minutes, but it exists, and both versions must work against the same schema.

This single constraint rules out every destructive schema change as a single step. You cannot rename a column, because the old code queries the old name and the new code queries the new one. You cannot drop a column that old code still selects. Every change must be decomposed into steps that are individually backwards compatible.

02Expand, migrate, contract

The pattern that resolves this is three phases separated by deploys. Expand: add the new structure without touching the old, so nothing breaks. Migrate: write to both, backfill existing rows, then switch reads to the new structure. Contract: once nothing references the old structure, remove it.

Renaming a column becomes: add the new column, deploy code writing to both, backfill in batches, deploy code reading from the new column, verify nothing reads the old one, then drop it. Five steps instead of one, spread over days rather than minutes. It is more work, and it is the difference between a routine change and an incident.

• Expand - additive changes only; old code unaffected.
• Migrate - dual-write, backfill in batches, then switch reads.
• Verify - confirm nothing references the old structure before removing it.
• Contract - drop the old structure in a later, separate deploy.

03The operations that take dangerous locks

Not every DDL statement is equally risky, and knowing which ones block traffic is what lets you move quickly on the safe ones. The specifics vary by database and version - modern Postgres has made several previously blocking operations safe - so verify against your actual version rather than folklore.

04Backfills are jobs, not migrations

Updating two hundred million rows in a single statement holds a transaction open for the duration, generates enormous WAL volume, blocks vacuum from reclaiming space, and rolls back entirely if it fails at 95%. Backfills belong outside the migration system, as resumable batch jobs.

Process in batches sized to complete in well under a second each, commit between batches, track progress in a table so the job resumes rather than restarting, and add a deliberate pause between batches to leave I/O headroom for real traffic. Slower in wall-clock time, dramatically safer, and interruptible when something else needs the database.

05Locks queue behind each other

This is the mechanism that turns a slow migration into a full outage, and it surprises people. When your ALTER TABLE waits for a lock held by a long-running query, it does not wait politely. It sits in the lock queue, and every query arriving afterwards - including simple reads that would not have conflicted with the original query - queues behind it.

One blocked DDL statement can therefore stall all traffic to a table within seconds. The mitigation is a short lock_timeout so the migration fails fast rather than queueing, combined with retry logic. Failing and retrying is vastly preferable to holding the queue open.

06Make it routine

Teams that do this reliably have automated the checks rather than relying on review. A linter on migration files flags dangerous operations. CI runs migrations against a production-sized dataset to surface duration. Every migration sets a lock timeout by default via configuration rather than developer memory.

The cultural piece matters as much: normalise multi-step changes spread over several deploys. A team that expects a column rename to take a week will do it safely. A team that expects it to take an afternoon will take the lock.