Zero-Downtime Database Migration: A Production Playbook

Most migration outages come from one mistake: shipping a schema change and the code that depends on it in the same breath. Decouple them, run old and new side by side for a while, and the scary parts get boring. Here is the playbook we use to change schemas and move databases in production without a maintenance window.

6 min read8 sectionsJune 22, 2026

Written by

Kostadin Stamboliev

Founder and Imaginator

Founder and Imaginator at Pineido. Practitioner across IT consulting, app and web development, technical SEO, and AI / automation. Ships production code in 30-day sprints with fixed scope and clear ownership.

LinkedIn

What "zero downtime" actually means

Zero downtime does not mean magic. It means there is never a moment where the running application cannot read and write what it needs. The honest version, borrowed from people who move large databases for a living, is "near-zero cutover": the new state mirrors the old closely enough that users never notice a gap.

Two different problems hide under the same phrase, and they need different tactics. The first is schema evolution: changing tables, columns, types, and constraints inside a database that stays put. The second is relocation: moving data to a new database, a new engine, or a new region. The unifying rule across both is the same. A migration must complete while the old application code is still running, and the new code must work against both the old and new shapes during the overlap. Decouple the schema change from the deploy and the whole thing stops being a single terrifying event.

The expand-contract pattern is the whole game

Almost every safe schema change is a variation of expand-contract, also called parallel change. Instead of one atomic migration that flips everything, you split the work into phases that each ship independently and each remain backward-compatible.

Expand: add the new column, table, or index alongside the old. Nothing breaks because nothing is removed. The application starts writing to both representations (dual-write) but still reads from the old one.

Migrate: backfill historical rows into the new shape, then verify, then switch reads to the new column while dual-write continues.

Contract: stop writing the old column, rename it so any forgotten reference fails loudly, observe, and only then drop it.

The reason this works is that the boundary between phases is where you ship and watch. If anything looks off, the previous phase is still fully functional. You can pause indefinitely rather than reverse a half-applied change while pagers are going off. Purely additive changes are the easy case, old code simply ignores the new column. The discipline matters most when you are changing or removing something the live app depends on.

Dual-write: keep both copies honest

During the overlap window, every write has to land in both the old and new locations or the new copy quietly falls behind. You have two ways to do this and the choice has real consequences.

Application-layer dual-write means your code writes both places explicitly. It shows up in traces and logs, so when something diverges you can debug it through the observability stack you already trust. Database triggers can keep columns in sync with less app code, but the behavior happens inside the database where your normal tooling cannot see it, which makes debugging miserable. For most teams the small cost of an explicit application write is worth the traceability.

The sharp edge of dual-write is that two writes are not one atomic transaction. If the first succeeds and the second fails, the copies drift. For anything where that matters, route writes through a transactional outbox or a queue-based propagation so a single committed transaction is the source of truth and the second write is derived from it. This is also where a tool like pgroll takes a different tack on Postgres: it exposes version-specific views so old and new app versions each see the schema they expect, and handles the dual logic at the database layer instead of in your code.

Backfill without melting the database

New writes go to both copies the moment dual-write is on, but historical rows do not move themselves. Backfill copies the existing data into the new shape. Do it in batches, typically a few thousand rows at a time, with a deliberate pause between batches so you do not saturate I/O and starve live traffic.

Keep backfill in a separate job from the schema change. Adding a NOT NULL column with a default and backfilling millions of rows in the same migration is how a two-second change becomes an hours-long lock. On Postgres, add a NOT NULL constraint as NOT VALID first, which is instant because it skips the table scan, then run VALIDATE CONSTRAINT separately, which takes a lighter lock that lets reads and writes continue.

Schedule heavy backfills for low-traffic windows, throttle them, and watch your replicas. If you run read replicas, they can briefly carry a different schema than the primary, so the app has to tolerate that lag during the migration.

Validate before you trust the new copy

Counting rows is not validation. Row counts can match while the contents differ. Before you move reads, verify with checksums or hashes on the critical tables and invariant checks on sampled data, not just totals. If you are already reading from both copies for some queries, an application-level comparison that flags mismatches in real time is the strongest signal you can get.

Backfill is also your chance to fix data quality. Migrating historical rows surfaces the inconsistencies that production has been quietly tolerating for years, so treat the backfill as a cleanup pass, not just a copy.

Do not cut dual-write too soon. Keep both copies live and synchronized for at least one full business cycle after you switch reads. Disabling the old path early hides mismatches you have not caught yet, and it removes your cheapest rollback.

Locking is the real source of downtime

The DDL statement itself is usually fast. The outage comes from lock contention around it. A common failure: an ALTER requests an exclusive lock, gets stuck behind a long-running transaction or a forgotten open session, and every query that arrives after it queues behind the lock. A simple schema change escalates into a service-wide stall.

Defend against it. Always set a short lock_timeout so a blocked DDL fails fast instead of jamming the queue. On Postgres, build indexes with CREATE INDEX CONCURRENTLY so writes proceed during the build, and remember it waits for transactions that started before it, so kill stray long-running queries first. Postgres also wraps DDL in transactions, so a failed migration rolls back cleanly.

On MySQL the picture is version-dependent. Check that your change supports ALGORITHM=INPLACE, LOCK=NONE, and be aware that some operations before 8.0.29 rebuild the whole table and block writes. For high-row-count tables where even an online ALTER is risky, reach for an external tool: gh-ost or pt-online-schema-change on MySQL, pgroll or pg_osc on Postgres. They build a shadow copy, sync it in chunks, and swap atomically while the original stays fully usable.

Moving to a new database: bulk load, CDC, cutover

Relocating a database, across engines, versions, or regions, follows three phases. First, a bulk load establishes a baseline: a native dump and restore, or a snapshot, into the target. Second, change data capture or logical replication streams every insert, update, and delete from source to target so the gap keeps narrowing toward near-zero lag. For same-engine moves, Postgres logical replication or MySQL binlog replication is usually the cleanest path, and logical replication even spans major versions. Apply the schema on the target first, because logical replication does not carry DDL.

Third is cutover. Plan it as a read-only window of seconds, not minutes: briefly stop writes to the source, confirm replication lag has reached zero, then redirect traffic by swapping the connection string, DNS, or load-balancer route. Watch for sequence and auto-increment drift, if the target's sequence trails the source, new inserts collide with replicated IDs. After cutover, run VACUUM/ANALYZE or OPTIMIZE TABLE, and do not shut the source down. Keep it live, ideally read-only, for at least 24 hours as your rollback. Before any of this touches production, do a full trial cutover in staging against production-scale data, because a change that takes two seconds on a laptop can take hours on real volume.

Get operations insights weekly

Join service-business operators who get one actionable insight on ops, automation, and growth every week.

Checking that you're human. You can send in a moment.

No spam. Unsubscribe anytime.

You’re subscribed! Look for our next issue in your inbox.

Where this fits with how we work

None of these steps are hard in isolation. The risk lives in sequencing them correctly, knowing which phase you can safely pause on, and having a rollback that actually works under pressure. That is exactly the kind of work our fixed-scope 30-day sprints are built for: one clearly defined migration, planned as expand-migrate-contract, with the backfill, validation, and cutover runbook written down and rehearsed before anything ships.

If you are migrating off an old system entirely rather than reshaping the current one, the same principles drive a clean decommission: dual-run, prove the new path, then retire the old one on a documented timeline. And if you would rather have someone own the overall data and infrastructure strategy across several of these changes, that is fractional-CTO territory. Either way, the goal is the same as this whole article: make the migration boring.

Ready to ship?

Tell us where you are stuck. We will review it and send a focused recommendation within one business day.

Frequently asked questions

Can I do a zero-downtime migration in a single deploy?

No, not for anything beyond a purely additive change. A safe migration that changes or removes a column or table needs at least two release cycles: one to add the new structure, dual-write, and backfill, and a later one to switch reads and drop the old structure. The whole point is to keep old code working against the new schema during the overlap, which a single atomic deploy cannot guarantee.

Should dual-write live in the application or in database triggers?

Prefer the application layer for most cases. Explicit dual-writes appear in your traces and logs, so when the two copies diverge you can debug it with the observability tools you already use. Triggers keep the logic out of your code but run inside the database where your normal tracing cannot see them, which makes incidents much harder to diagnose. For atomicity-critical writes, route through a transactional outbox or queue so divergence cannot happen silently.

How do I verify the old and new data actually match?

Do not rely on row counts. Use checksums or hashes on your critical tables plus invariant checks on sampled rows, because counts can match while contents differ. If your app already reads from both copies for some queries, add a real-time comparison that flags mismatches. Keep dual-write running and reconcile for at least one full business cycle after switching reads before you trust the new copy alone.

Why does a fast schema change still cause an outage?

Almost always lock contention, not the DDL itself. An ALTER that needs an exclusive lock can stall behind a long-running transaction or a forgotten open session, and every query arriving after it queues behind the lock, turning a quick change into a service-wide stall. Set a short lock_timeout so blocked DDL fails fast, use CREATE INDEX CONCURRENTLY on Postgres, and clear stray long-running queries before you start.

What about tables too large for a normal ALTER?

Use an online schema change tool that builds a shadow copy and swaps it in. On MySQL, gh-ost or pt-online-schema-change copy data in chunks and swap atomically while the original stays usable. On Postgres, pgroll handles the expand-contract pattern with version-aware views, or pg_osc handles one-off large changes. You still need to understand locking; the tools manage the file and execution, but lock_timeout, CONCURRENTLY, and small safe steps are still on you.

What does a real rollback plan look like?

Rollback gets easier the earlier you are in the phases. From the expand phase, the new column is additive and unused for reads, so you just drop the dual-write code and ship. In the middle of cutover, flip the read flag back to the old column, keep dual-write running, and investigate without losing the new data. When relocating to a new database, keep the source live and read-only for at least 24 hours after cutover so you can redirect traffic back if something surfaces.