Zero-Downtime Database Migrations

Schema changes are where confident deploys go to die. Everything else can be canaried and rolled back, but a migration that renames a column or adds a NOT NULL constraint can take down the app the moment it runs, and rolling back a migration is far scarier than rolling back code. The good news: with the right pattern you almost never need a maintenance window.

Why naive migrations cause downtime

Two reasons:

1. Locks. Some DDL operations take locks that block reads/writes while they run. On a big table, ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT or adding an index can lock for minutes.
2. Code/schema coupling. If you deploy code and schema together, there's always a window where the new code expects the new schema (or vice versa) but the other half hasn't rolled out yet. During a rolling deploy, old and new code run simultaneously, both must work against the same schema.

The fix for both is the same idea: never make a breaking change in one step.

The expand-contract pattern

Also called parallel-change. You split every breaking schema change into backward-compatible steps so that, at every moment, both the old and new code work against the current schema.

Three phases:

1. Expand: add the new schema alongside the old. Additive only. Both old and new code work.
2. Migrate: backfill data and switch the code to use the new schema, while still tolerating the old.
3. Contract: once nothing reads the old schema, remove it.

Each phase ships as its own deploy. No single step breaks running code.

Worked example: renaming a column

Say you want to rename users.username to users.handle. The naive ALTER TABLE RENAME COLUMN breaks every running instance of the old code instantly. Expand-contract instead:

Phase 1: Expand:

• Add a new handle column (nullable, no rewrite of the table).
• Deploy code that writes to both username and handle, reads from username.

Phase 2: Migrate:

• Backfill: UPDATE users SET handle = username WHERE handle IS NULL (in batches, see below).
• Deploy code that reads from handle, still writes both.

Phase 3: Contract:

• Deploy code that only uses handle.
• Drop the username column.

It's four deploys instead of one, but each is reversible and none causes downtime. That trade is almost always worth it for a production system.

Backfills: do them in batches

Never UPDATE a huge table in one statement, it locks rows and bloats transaction logs. Batch it:

-- repeat until no rows updated
UPDATE users SET handle = username
WHERE handle IS NULL
LIMIT 5000;

Run batches with a short pause between them so you don't saturate I/O or replication. For very large tables, a background job that walks primary keys in ranges is more controllable.

Postgres-specific gotchas

• Adding a column with a non-volatile default is fast in modern Postgres (no full rewrite): but a volatile default still rewrites. Know the difference.
• Create indexes with CREATE INDEX CONCURRENTLY so you don't lock writes (it can't run inside a transaction block, tell your migration tool).
• Adding NOT NULL directly scans the whole table under a lock. Instead: add the column nullable, backfill, add a CHECK (col IS NOT NULL) NOT VALID constraint, VALIDATE it (cheaper lock), then optionally set NOT NULL.
• Set a lock_timeout on migrations so a migration that can't get its lock fails fast instead of queuing behind it and stalling every query.

Make migrations part of the deploy pipeline

• Run migrations as a separate, explicit step before or after the app deploy: not implicitly on app startup (that races during rolling deploys).
• Decouple deploy from migration so you can deploy code that's compatible with both old and new schema, then migrate independently.
• Test migrations against production-sized data in staging: a migration that's instant on 1k rows can lock for minutes on 50M.
• Have a rollback plan for the code, and a forward-fix plan for the schema: since rolling schema back is usually riskier than rolling forward.

The short version

• Downtime comes from locking DDL and from coupling code to schema.
• Use expand-contract: additive change → migrate/backfill → remove old, each as its own deploy.
• Backfill in batches; never one giant UPDATE.
• Mind engine-specific traps (CONCURRENTLY, NOT NULL scans, volatile defaults, lock_timeout).
• Run migrations as an explicit pipeline step, tested on realistic data.

Zero-downtime migration is just the discipline of never making a change that requires old and new code to disagree about the schema. Slower per change, dramatically safer in aggregate.

---

Designing safe deployment and migration practice is part of my consulting work, reach out.