Zero-downtime database migration: dual-write, backfill, and a safe cutover for a live system

Swapping a database engine, splitting a monolithic schema into multiple services, or moving to a different cloud provider are infrastructure changes no growing system can avoid — but for a product serving real customers 24/7, "take the system down at 2am to migrate" is no longer an acceptable plan the way it was a decade ago. Customers span multiple time zones, orders can arrive at any hour, and a maintenance window of just a few hours is enough to cause real revenue loss or breach a committed SLA. This post lays out a four-stage architecture for migrating data with zero downtime — along with the most common trap engineering teams hit at each step.

The four stages of a safe migration

A zero-downtime migration isn't a single "cutover" step — it's four sequential stages, each of which can pause and roll back if something looks wrong:

• **Dual-write** — every new write goes to both the old and new database simultaneously, while the application still reads from the old one
• **Backfill** — all historical data (everything that existed before dual-write was turned on) is copied to the new database in batches
• **Verify** — data is reconciled between both databases to confirm there's no discrepancy before trusting the new one
• **Cutover** — reads shift to the new database in steps, and only then is dual-write and the old database retired

Dual-write: the easiest trap is manual double-writes in application code

The most intuitive approach — and also the easiest to get wrong — is editing every write function in the application to call both databases. The problem: miss just one write path (a background job, a rarely-used endpoint, an error-handling branch that writes raw SQL directly), and data between the two databases silently drifts apart with no error ever logged. A safer approach is Change Data Capture (CDC) — a tool like Debezium reads the old database's write-ahead log directly and replicates every change to the new database at the infrastructure layer, entirely decoupled from application logic. The application keeps writing to a single place as before; CDC guarantees the new database stays in sync without touching business logic.

Another issue that needs explicit handling: when a write to the new database fails but the write to the old one succeeds (or vice versa), the system needs to recognize this as a "drifted" record that needs re-backfilling, not a silent failure. The outbox pattern — writing a change event to a dedicated table within the same transaction as the primary write — is a common way to guarantee this consistency without needing a distributed transaction across two databases.

Backfill: moving historical data without slowing down production

Once dual-write is running stably, historical data (everything that existed before dual-write was enabled) still only lives in the old database. Backfill copies this bulk of data to the new database — but running a single `INSERT INTO ... SELECT` over a table with tens of millions of rows will lock the table or saturate I/O, directly impacting real traffic. The right approach is splitting the work into small batches by primary key or timestamp, recording a watermark (a cursor marking how far the backfill has progressed) after each batch so it can pause and resume without starting over, and throttling the rate so the backfill job always yields resources to production traffic.

Verify: reconciling data before trusting the new database

This is the step most likely to get skipped when a team is under time pressure — but skipping it means cutting over on faith instead of evidence. An effective verification approach combines two layers: comparing aggregate checksums per batch between both databases (catching large-scale discrepancies quickly), and a deep field-by-field comparison on a random sample of records (catching subtler bugs like a wrong data type, lost decimal precision, or character-encoding errors). The rule at this stage: there's no "acceptable discrepancy" threshold — any mismatch needs a clear explanation (CDC timing, or an actual bug) before moving to cutover.

Cutover: shifting reads gradually, not a big bang

A safe cutover isn't swapping a connection string once for all traffic. Use a feature flag to shift the percentage of reads to the new database in steps — 1%, then 10%, 50%, and finally 100% — monitoring error rate and latency at each step before increasing further. This is the same progressive-rollout principle we apply when designing an alert pipeline — a large change should always move through small, observable, reversible steps, not a single all-at-once switch. Only after 100% of reads have run stably on the new database for a sufficiently long window should dual-write be stopped and the old database formally retired.

When you don't need the full architecture

The four stages above suit a system with 24/7 traffic that can't accept downtime even for a few minutes — but not every system is at that scale. An internal tool used only during business hours, or an early-stage product with a few hundred users, can often accept a short, announced maintenance window at far lower engineering cost than standing up a full dual-write/CDC pipeline. This is exactly the kind of assessment we make before proposing an architecture — a 3–4 week Module Sprint for a small system shouldn't carry the cost of applying a pattern designed for enterprise-scale infrastructure wholesale.

Conclusion

Zero-downtime database migration isn't a single technical maneuver — it's a chain of verifiable decisions at every step: safe double-writes via CDC instead of manual code changes, watermarked backfill instead of one giant statement, evidence-based reconciliation instead of faith, and a gradual cutover instead of a single switch. This is the kind of infrastructure problem we solve within the Server & Database layer of every Pilot Build — including consolidating 4 legacy systems, as in the internal portal for a 14-team engineering org we delivered. Get in touch if your system needs a safe migration plan for data currently serving real customers.