Database Migrations in Production: Schema Changes Without Downtime
The migration ran in 40 milliseconds and still caused an outage. Production schema changes fail in two ways: the old code can't handle the new schema during a rolling deploy, or a quick ALTER waits on a lock and freezes every query behind it. Here's the expand/contract pattern, the locking traps, and how to change a schema under live traffic.
A 40-Millisecond Outage That Lasted Ten Minutes
The change was small: rename users.name to users.full_name. It passed review, the tests were green, and on staging the migration took 40 milliseconds. In production, the deploy pipeline ran the migration and then began rolling out the new code.
For the next ten minutes, every request handled by an old instance failed with column "name" does not exist. The rollout was halfway through, so about half of all traffic was failing. Rolling back the code didn't help, because the old code needed a column that was gone.
Nothing about the migration was slow. The problem was that for a few minutes, two versions of the application were running against one schema, and the schema only worked with one of them. That's the first rule of production migrations, and the second one, about locks, is how a 40ms statement can still freeze a database.
Rule 1: Old Code and New Code Run at the Same Time
On one server with a maintenance window, you can stop the app, migrate, and start the new version. In production that's rarely an option. Rolling deploys, blue-green deploys, and canaries all have a period where old and new code serve traffic together, and the database is shared by both.
So every schema state you pass through has to work with both the version before it and the version after it. A change that breaks the old code, like a rename, a drop, or a new NOT NULL column the old code doesn't fill in, can't be done in one step. It has to be split.
Step through Rename in one migration first. The moment the rename runs, every running instance breaks, and there's no safe rollback. Then switch to Expand / contract. It takes seven steps instead of four, but every step keeps every running version working, and you can roll back the code at any point.
The Expand/Contract Pattern
The idea, also called parallel change, is to never change something in place. Add the new thing alongside the old one, move everything over, and remove the old one only once nothing uses it.
Expand: add the new structure
Add the new column, table, or index in a way the current code ignores: nullable, or with a default. Old code keeps working because it doesn't know the column exists.
Write to both
Deploy code that writes to both the old and new column but still reads the old one. Old rows don't have the new value yet, so reading it would be wrong.
Backfill
Copy existing data into the new column in small batches. Because new writes already go to both columns, once the backfill finishes they stay in sync.
Read from the new structure
Deploy code that reads the new column, still writing both. If something is wrong, you can roll back, because the old column is still complete.
Contract: stop writing the old one, then drop it
Deploy code that no longer uses the old column. After that version is fully rolled out and you're confident you won't roll back past it, drop the old column.
Each step is its own deploy, sometimes days apart. That's slower than one migration, and it's the price of never having a moment where the running code and the schema disagree.
✅ Tell your ORM first
Many ORMs select every column they know about, and some cache the schema at startup. Before dropping a column, deploy a version where the model ignores it (Rails has ignored_columns; most ORMs have an equivalent). Otherwise the drop breaks queries that never actually used the column.
Rule 2: Fast Statements Can Still Block Everything
The second way migrations cause outages has nothing to do with the code. It's about locks.
Most ALTER TABLE statements in PostgreSQL take an ACCESS EXCLUSIVE lock, which conflicts with everything, including plain SELECTs. Usually that's fine, because the lock is held for milliseconds. The problem is getting it. If a long-running query or an idle transaction is using the table, the ALTER has to wait. And while it waits in the lock queue, every new query on that table queues up behind it.
From the outside, the table is simply frozen. Connection pools fill up with blocked queries, request latency climbs, and health checks start failing, all because of a statement that would have taken 40 milliseconds.
The fix is to never let a migration wait indefinitely:
SET lock_timeout = '3s'; -- give up if the lock isn't free within 3s SET statement_timeout = '30s'; ALTER TABLE users ADD COLUMN full_name text; -- If it times out, nothing changed. Retry later, ideally with backoff.
A migration that fails and retries is a minor annoyance. A migration that waits is an outage.
Which Operations Are Safe?
Some changes are quick metadata updates. Others rewrite or scan the whole table while holding a lock. The details vary by database and version, so check the docs for yours; this is the PostgreSQL picture for recent versions:
| Change | Cost in PostgreSQL | Safer approach |
|---|---|---|
| Add nullable column | Metadata only | Safe (with lock_timeout) |
| Add column with a constant default | Metadata only since PG 11 | Safe on 11+; on older versions add it nullable, then backfill |
| Add NOT NULL to existing column | Scans the whole table under lock | Add CHECK (col IS NOT NULL) NOT VALID, VALIDATE it, then SET NOT NULL (PG 12+ skips the scan) |
| Add foreign key | Validates every row under lock | Add it NOT VALID, then VALIDATE CONSTRAINT separately |
| Create index | Blocks writes for the whole build | CREATE INDEX CONCURRENTLY (can't run inside a transaction) |
| Change column type | Usually rewrites the table | Expand/contract: new column, dual write, backfill, switch |
| Rename column or table | Metadata only, but breaks running code | Expand/contract, or a view for compatibility during the switch |
| Drop column | Metadata only, but breaks running code | Only after no deployed version references it |
💡 Drop the helper constraint separately
Once SET NOT NULL has succeeded, the CHECK constraint is redundant, so drop it. Do that in its own statement. If the drop is part of the same ALTER TABLE as SET NOT NULL, PostgreSQL doesn't guarantee the subcommands run in order, and it may scan the whole table after all.
MySQL has its own rules. InnoDB online DDL supports many changes with ALGORITHM=INSTANT or INPLACE, and tools like gh-ost and pt-online-schema-change handle the rest by building a shadow table and swapping it in. The principle is the same: know whether a statement rewrites the table or blocks writes before you run it on a big one.
⚠️ Backfills are migrations too
UPDATE users SET full_name = name on 50 million rows is one huge transaction. It holds row locks for its whole duration, generates a burst of WAL that your replicas have to replay (watch replication lag), and in PostgreSQL leaves 50 million dead row versions for vacuum. Backfill in batches of a few thousand rows by primary key range, commit each batch, and pause between them.
Running Migrations Safely
A few practices prevent most migration incidents:
- Decouple migrations from deploys. Run schema changes as their own step, not automatically on app startup. If ten instances start at once, you don't want ten copies racing to run the same migration.
- Make every migration backward compatible with the running code. If a change isn't, it's actually several migrations with deploys in between.
- Set timeouts on every migration.
lock_timeoutandstatement_timeoutturn a potential outage into a retry. - Test against production-sized data. A migration that takes 40ms on a 1,000-row staging table can take an hour on a 200-million-row production one. A restored snapshot is the best place to time it.
- Use a linter. Tools like
squawk(PostgreSQL) andstrong_migrations(Rails) flag dangerous operations, like a non-concurrent index or an unsafeNOT NULL, in code review. - Prefer roll-forward for schema. Down migrations that drop data are rarely safe to run in production. With expand/contract, rolling back the code is enough, because the schema works with both versions.
Key Takeaways
- Two versions of your code share one schema during every deploy. Each schema change has to work with both.
- Expand, migrate, contract. Add the new thing, write to both, backfill, switch reads, stop writing the old, then drop it.
- Renames and drops are the dangerous "fast" changes. They're instant for the database and break the running app.
- Lock waits cause more outages than slow statements. Always set
lock_timeoutso a blocked migration fails instead of freezing the table. - Know which operations rewrite or scan. Use
CONCURRENTLY,NOT VALIDconstraints, and batched backfills for large tables. - Treat migrations as deploys of their own, tested on realistic data and checked by a linter.