Most production outages caused by database migrations are not caused by the migration itself. They are caused by a sequence of small assumptions that compound: the migration will run quickly, the application can be updated atomically with the schema, the new column has a sensible default, the old column can be dropped right after the new one ships. Each one of those assumptions is false in a meaningful fraction of real-world deployments, and the failure mode when they fail at the same time is the kind of multi-hour incident that nobody wants to debug at 3 a.m.
Zero-downtime migrations are not magic. They are a small number of disciplined patterns applied in a strict order. This piece walks through what that order actually is, how to think about the rollout in stages, and where the common failure modes are. It is opinionated toward Postgres, which is what we use most at 137Foundry, but the patterns transfer with minor adjustments to MySQL, MariaDB, and most other relational databases.
Photo by panumas nikhomkhai on Pexels
The Mental Model: Forward-Compatible, Then Backward-Compatible
The core mental model is that a migration is not a single atomic operation. It is a sequence of states, and the application code has to work correctly in every intermediate state.
A migration that adds a column called email_verified to a users table goes through these states:
- Old code, old schema. Application reads and writes the old columns only.
- Old code, new schema. The new column exists but is unused by the application.
- New code, new schema. Application reads and writes the new column.
- New code, old columns dropped. The old columns are gone.
The migration is broken if state 2 or state 3 leaves the application unable to serve traffic. Most failures happen because someone tried to compress two of these states into a single deploy, and the rollback path got lost in the process.
The pattern that survives is called expand-and-contract, sometimes called expand-migrate-contract. Expand the schema first (add new columns, tables, indexes without dropping anything). Migrate the application (start writing to and reading from the new shape). Contract the schema only after the application has been running on the new shape long enough that you are confident you do not need to roll back.
The Five-Stage Playbook
The playbook that ships safely, in order:
Stage 1: Add the New Schema (Forward-Compatible)
Add the new columns, tables, or indexes. Critically, do this in a way that does not break the existing application code.
For Postgres, this means:
- Add new columns as nullable. Adding a
NOT NULLcolumn to a populated table requires either a default value (which in older Postgres versions rewrites the entire table and locks it) or a multi-step backfill. The Postgres documentation on ALTER TABLE is the canonical reference for which operations rewrite tables and which do not. Postgres 11 and later support adding aNOT NULLcolumn with a constant default without a rewrite, which made this much safer than it used to be. - Add indexes concurrently.
CREATE INDEX CONCURRENTLYbuilds the index without blocking writes to the table. The non-concurrent version takes anACCESS EXCLUSIVElock that blocks every read and write for the duration. On a table with millions of rows, that can be minutes of effective downtime. - Add foreign keys without validation.
ALTER TABLE ... ADD CONSTRAINT ... NOT VALIDadds the constraint for new rows without checking existing data. ThenALTER TABLE ... VALIDATE CONSTRAINTdoes the check separately, with a weaker lock. The two-step pattern lets you stage the constraint without holding a long lock. - Add tables freely. New tables are safe. They are not referenced by existing code, so nothing breaks when they are created.
The output of this stage: the new schema shape exists. The application has not changed. If something goes wrong, the only rollback you need is to drop the new objects, which is fast.
Stage 2: Dual-Write (Application Knows About Both Shapes)
Deploy a version of the application that writes to both the old shape and the new shape on every relevant operation. The reads still come from the old shape.
This is the riskiest stage in terms of application code complexity. The application now has to keep two parallel data structures in sync on every mutation, and the failure modes are subtle: an insert that succeeds against the old shape but fails against the new shape, or vice versa, leaves the two out of sync.
A few patterns that help:
- Write to both in the same transaction. If the database is the same database (which it usually is when adding columns), wrap the two writes in a single transaction so they succeed or fail together. The PostgreSQL documentation on transactions explains the ACID guarantees.
- Log mismatches loudly. Add monitoring that detects when the dual writes diverge. Any divergence means a bug in the dual-write code, and you want to know about it before the migration moves on.
- Make the new write idempotent. If a retry happens, the new write should produce the same outcome whether or not it has run before. This is generally good practice; in a migration, it is essential.
Photo by betül nur akyürek on Pexels
Stage 3: Backfill Historic Data
The dual-write covers new mutations going forward. The backfill covers everything that existed before the dual-write deployment.
The backfill pattern that survives:
- Backfill in batches. Do not run a single
UPDATE users SET email_verified = false WHERE email_verified IS NULL. On a large table, this takes a long lock and floods the WAL log. Instead, loop in batches of 1,000 to 10,000 rows, with a small sleep between batches to let replication catch up. - Use a unique key for batching. Order the batches by primary key, not by the column being backfilled. The query plan is faster and more predictable.
- Track progress. Write the highest key processed to a table or a Redis key so the backfill can resume if it gets interrupted.
- Monitor replication lag. If you have read replicas, the backfill can produce enough write volume to push replication lag into the seconds or minutes. Either rate-limit the backfill or accept the lag, but know which one you chose.
The output of this stage: every row in the table has the new column populated.
Stage 4: Cut Over Reads (Application Trusts the New Shape)
Deploy a version of the application that reads from the new column instead of the old one. The dual-write continues so the old column stays in sync, which gives you a rollback path.
This is the deploy that is most likely to expose a bug in the migration. The new column was added, dual-written, and backfilled, but until reads cut over, nothing in the application actually depended on the new shape. The first request that hits the new code path is also the first request that exercises the new schema in anger.
Roll this out behind a feature flag or a percentage-of-traffic rollout if your infrastructure supports it. The LaunchDarkly feature flag documentation covers the general pattern, and most modern platforms have something equivalent. Start with 1 percent of traffic. Watch error rates. If anything misbehaves, flip the flag back. If everything is fine after a few hours at 1 percent, move to 10 percent. Then 50 percent. Then 100 percent.
"The migrations that break in production are almost always the ones that skip the gradual cutover. Once your team has done two or three migrations behind a percentage rollout, the temptation to ship the next one as a single flip goes away, because the percentage version is just so much less stressful." - Dennis Traina, founder of 137Foundry
Stage 5: Contract the Schema (Drop the Old Shape)
After the new shape has been running in production at 100 percent traffic for long enough that you are confident no rollback is needed (a week is reasonable for most teams; longer for critical systems), drop the old columns and remove the dual-write code.
The contraction step is the simplest in terms of database operations but the easiest to get wrong in terms of timing. Dropping a column locks the table briefly. On most modern Postgres versions the lock is short, but the pgexperts ALTER TABLE reference is worth checking for your specific version.
Removing the dual-write code from the application is just a normal code change.
The Operational Disciplines That Hold This Together
The five stages assume a few operational disciplines that are worth naming explicitly:
You can deploy your application multiple times per day. A team that can only deploy once a week cannot reasonably stage a five-step migration. The whole pattern depends on shipping small changes safely and often. If your team is not there yet, the better first investment is in the deploy pipeline, not in the migration itself.
You have observability into the database and the application. A migration is a process, not a single event, and you need to see whether the process is healthy at each stage. Query latency, lock wait times, replication lag, and error rates by code path all matter. The Datadog database monitoring documentation covers what to instrument; if you use a different APM the same metrics apply.
You have a rollback plan written down. Each stage should have a documented rollback that gets you back to the previous stage. "Roll back the application deploy" is usually most of the answer for stages 2 and 4. "Drop the new objects" is the answer for stage 1. Stages 3 and 5 are harder to roll back, which is why they happen later in the sequence.
Photo by Walls.io on Pexels
Common Failure Modes
Three patterns that cause most of the actual production incidents on migrations:
Adding a NOT NULL column with no default on an old Postgres version. The migration rewrites the table, takes an exclusive lock, and blocks production traffic for the duration. The fix is to add the column nullable, backfill the value, then add the NOT NULL constraint with NOT VALID, then validate. The pgKit migration guide (and most other Postgres operational references) cover this exact pattern.
Skipping the dual-write stage. A team adds a new column, backfills it directly with a SQL statement, then ships the application code that reads from the new column. The window between the backfill and the deploy is a window where new writes go only to the old column. After the deploy, the most recent writes are missing.
Dropping the old column before the new code is fully rolled out. The application deploy that reads the new column is at 50 percent traffic, behind a feature flag. Someone notices the schema migration list shows the old column as "to be dropped" and runs the drop. The 50 percent of traffic still on the old code path immediately errors.
The defensive habit is to write the migration plan down, with the stages numbered, the rollback for each stage spelled out, and a checklist that requires acknowledgement before each stage runs.
When Zero-Downtime Is Not Worth It
A note that does not always get said: zero-downtime migrations are expensive in engineering time. Five deploys, dual-writing code, backfill scripts, monitoring, gradual rollout. For a small internal tool with three users and a maintenance window every week, the right answer might be to take the maintenance window, run the migration in one shot, and be done.
The zero-downtime pattern earns its complexity on systems where downtime has real cost (customer-facing applications, high-volume transactional systems, systems where the migration would take longer than any acceptable maintenance window). For everything else, the simpler pattern of taking a planned outage is fine, and pretending otherwise creates work without buying much value.
The judgment call about which path to take is what the web development service at 137Foundry helps teams think through, especially for systems that have grown past the point where the old maintenance-window approach is still working. The services hub covers the adjacent practices (data integration, technical SEO, AI automation) that often need similar discipline on the database side.
For more on how 137Foundry approaches engineering work in general, https://137foundry.com/about covers the operating philosophy.
The Shortest Possible Summary
Expand the schema before you change the application. Dual-write before you change the reads. Backfill before you cut over. Cut over gradually. Contract last.
Five stages. None of them are clever. All of them are mandatory when the system has to stay up. The teams that ship migrations confidently are the ones that have made this sequence routine, not the ones that found a shortcut around it.
