Zero-Downtime Database Migrations

Every backend eventually faces the same moment: the schema needs to change, the database is live, and users are mid-request. Most days a migration takes milliseconds and nobody notices — which is exactly what makes the dangerous ones so surprising. Then one day a harmless-looking ALTER TABLE takes a lock on your busiest table under load and requests start timing out, or a renamed column breaks every instance of the old code still running mid-deploy — and migrations stop feeling routine and start feeling like defusing something. The fix isn't heroics or 3 a.m. maintenance windows; it's a pattern, applied consistently, that makes every schema change boring by construction.

The two ways migrations hurt you

Locks. Some schema operations rewrite the entire table or take exclusive locks, blocking reads and writes for as long as they run. On a small table that's invisible; on fifty million rows it's an outage with a progress bar you can't see. The subtler version bites even "instant" changes: on Postgres, a fast ALTER still needs a brief exclusive lock, and if it queues behind one long-running query, every subsequent query queues behind the waiting ALTER — a one-line migration turns your busiest table into a parking lot. Setting a lock_timeout before running migrations is cheap insurance: better a failed migration you retry at a quieter moment than an application that stalls while a lock request waits.

The deploy gap. Code and schema never change at the same instant. During a rolling deploy, old code and new code run side by side against one database for seconds or minutes — and if the schema is only compatible with one of them, that window is a window of errors exactly as wide as your deploy. Renaming a column is the canonical example: deploy code first and the new code queries a column that doesn't exist yet; migrate first and the old code queries a column that's gone. There is no ordering that works — which is the clue that the change itself, as a single step, is the bug.

Expand–contract: the pattern that fixes both

The answer is to break every incompatible change into steps that are each individually backward-compatible — commonly called expand–contract (or parallel change):

Expand: add the new thing (column, table, index) without touching the old one. Purely additive; old code doesn't even notice.
Migrate writes: deploy code that writes to both old and new, still reading from old. Both schemas are now kept current.
Backfill: copy existing data into the new structure — in batches, not one giant statement.
Migrate reads: deploy code that reads from the new structure (and can stop dual-writing once verified).
Contract: once nothing references the old column and you've watched production long enough to believe it, drop it — in a later release.

A rename becomes: add customer_id → dual-write → backfill from user_id → read from customer_id → drop user_id next week. Five boring steps, each independently deployable, verifiable, and reversible — versus one clever step that requires either downtime or luck. If something looks wrong at step 3, you pause and investigate with zero user impact; the one-step version discovers problems live.

The same shape handles nearly every risky change: splitting a table, changing a column's type (add new column, dual-write, backfill, switch), moving data between services. Expand, migrate, contract — always. The discipline it demands is a single sentence: never ship a migration and the code that depends on it in the same breath. The schema must be valid for the code running before the deploy and after it, every time.

The database-specific traps (Postgres edition)

The pattern is universal; the sharp edges are per-database. For Postgres, the ones that matter most:

CREATE INDEX blocks writes for the build — use CREATE INDEX CONCURRENTLY. It's slower and can't run inside a transaction (so it needs its own migration step), but the table stays fully writable while the index builds. For any table with real traffic, this isn't optional.
Adding a column with a default is fast on modern Postgres (11+): the default is stored as metadata, no table rewrite. This reversed years of received wisdom — old advice to add-then-backfill a default is outdated, and knowing which era your advice comes from matters in database land generally.
Adding NOT NULL or a CHECK constraint validates every existing row under lock. The zero-downtime route: add the constraint as NOT VALID — it's enforced for new writes immediately but skips checking history — then run VALIDATE CONSTRAINT separately, which takes a much weaker lock and can churn through the backlog while traffic flows.
Backfills must be batched. One UPDATE across millions of rows holds locks for the duration, bloats the table (every updated row is a new row version until vacuum), and swamps replication. Loop in chunks of a few thousand with short pauses. It's slower, and slower is the feature: the goal of everything in this post is spreading work thin enough that nobody notices it happening.

MySQL has its own map of what's online and what isn't (its online DDL covers a lot; tools like gh-ost exist for the rest). The lesson transfers even when the details don't: know which operations your database performs online at your table sizes — from its documentation and a rehearsal, never from assumption.

Make the safe path the default path

Patterns decay unless the process enforces them:

Migrations live in the repo and get reviewed like code — with review asking the two questions that matter: what does this lock, and what happens while old code is still running? A migration that can't answer both isn't ready.
Run migrations as a separate deploy step, before the code rollout — never at application startup. Ten instances booting simultaneously and racing to run the same migration is a classic self-inflicted outage, and it couples "app restart" to "schema change" forever.
Rehearse against a production-sized copy. Every migration is instant on the hundred-row dev database; the pain is proportional to data volume, so the rehearsal must be too. A staging database restored from a production snapshot turns "we think it's fast" into a measured number.
Write down the contract step and actually do it. The "drop old column" ticket is where expand–contract quietly becomes expand–hoard: dual-writes accumulate, dead columns confuse every future developer, and each half-finished migration is standing bug surface. Finishing is part of the pattern.

The takeaway

Zero-downtime migrations come down to one principle: the database must be able to serve two adjacent versions of your code at every moment. Everything else follows — expand additively, move writes and reads in separate deploys, backfill in patient batches, contract only after production has proven the new path, and learn your database's locking behavior from its docs and a rehearsal rather than from an incident. None of it is glamorous, and all of it is repeatable — which is exactly what you want from changes to the layer holding everyone's data. Boring migrations are a sign of a team that has stopped gambling.