The standard playbook for moving a table to a new store is four phases: dual-write, backfill, verify, and cut over. Every phase has a failure mode that produces silent, permanent divergence, and the order the phases run in is load-bearing.
The one that catches most teams: if you backfill before dual-writing, you lose every row written during the backfill. The backfill reads a snapshot; concurrent writes land in the old store only; you finish, see matching row counts, and ship. The missing rows are the ones written during the window, which is exactly your busiest data.
Dual-write must be live before the backfill starts, not after.
Phase 1: Dual-Write, Old Store Authoritative
from __future__ import annotations
import logging
import time
from dataclasses import dataclass
from enum import Enum
from typing import Any, Protocol
log = logging.getLogger(__name__)
class Phase(str, Enum):
OLD_ONLY = "old_only"
DUAL_OLD_AUTH = "dual_old_auth" # write both, read old, compare async
DUAL_NEW_AUTH = "dual_new_auth" # write both, read new, old is fallback
NEW_ONLY = "new_only"
class Store(Protocol):
def write(self, key: str, value: dict) -> None: ...
def read(self, key: str) -> dict | None: ...
@dataclass
class MigrationRouter:
old: Store
new: Store
phase: Phase
metrics: Any
def write(self, key: str, value: dict) -> None:
if self.phase is Phase.NEW_ONLY:
self.new.write(key, value)
return
# Authoritative store first, and a failure there propagates.
auth, shadow = (
(self.old, self.new)
if self.phase in (Phase.OLD_ONLY, Phase.DUAL_OLD_AUTH)
else (self.new, self.old)
)
auth.write(key, value)
if self.phase is Phase.OLD_ONLY:
return
# Shadow write failures must NOT fail the request. If they do, the
# migration has coupled your availability to the store you are still
# testing - and you will roll back a migration that was fine.
try:
shadow.write(key, value)
except Exception:
self.metrics.incr("migration.shadow_write_failed")
log.exception("shadow write failed key=%s", key)
def read(self, key: str) -> dict | None:
if self.phase in (Phase.OLD_ONLY, Phase.DUAL_OLD_AUTH):
return self.old.read(key)
result = self.new.read(key)
if result is None and self.phase is Phase.DUAL_NEW_AUTH:
# Fallback during cutover. Every hit here is a real gap - alert on it.
self.metrics.incr("migration.new_store_miss")
return self.old.read(key)
return result
Two decisions in there matter more than they look.
1. Swallow and Count Shadow-Write Failures
Shadow-write failures are swallowed and counted. The instinct is to fail loudly. Do not — you have just made your production availability depend on the store you are still validating.
Count them, alert on the rate, and let the reconciler repair them.
2. Alert on new_store_miss
new_store_miss gets an alert, not a log line. Once you flip to new-authoritative, every fallback read is a row that should be in the new store and is not.
If that counter is non-zero, you are not ready to cut over. This is the single metric that tells you so.
Phase 2: Backfill With a Stable Cursor
def backfill(
router: MigrationRouter,
source_scan,
batch_size: int = 500
) -> int:
"""
Copy historical rows.
MUST run with dual-write already live.
Uses a keyset cursor, not OFFSET:
OFFSET pagination over a table taking concurrent writes skips
and duplicates rows as pages shift underneath it.
"""
copied = 0
cursor = ""
while True:
rows = source_scan(after=cursor, limit=batch_size)
if not rows:
break
for key, value in rows:
existing = router.new.read(key)
# Never overwrite. A live dual-write may have already put a NEWER
# value here; the backfill is reading an older snapshot. Clobbering
# it silently reverts a customer's write.
if existing is None:
router.new.write(key, value)
copied += 1
cursor = key
# Crude throttle; prefer a token bucket keyed on replica lag
# so the backfill self-limits.
time.sleep(0.05)
return copied
The if existing is None guard is the second classic corruption.
A backfill that blindly writes will overwrite fresher dual-written values with stale snapshot data, and the damage is invisible until a customer reports that an edit reverted.
For rows that genuinely need updating, you need a version or updated_at comparison rather than a presence check.
The source of truth for that comparison has to be a column the application actually maintains, not a database-generated timestamp that the backfill itself bumps.
Phase 3: Verify Continuously, Not Once
A one-shot comparison at the end tells you nothing about steady state. Sample continuously through the whole dual-write period:
def sample_and_compare(
router: MigrationRouter,
keys: list[str]
) -> dict:
mismatches, missing, checked = [], [], 0
for key in keys:
old_val = router.old.read(key)
new_val = router.new.read(key)
checked += 1
if new_val is None and old_val is not None:
missing.append(key)
elif old_val != new_val:
mismatches.append((key, old_val, new_val))
return {
"checked": checked,
"missing": len(missing),
"mismatched": len(mismatches),
"samples": mismatches[:5],
}
Expect a non-zero mismatch rate from timing alone — you read the two stores microseconds apart, and a write can land between those reads.
Re-check a mismatch after a short delay before counting it:
- A mismatch that persists is real.
- A mismatch that resolves is replication lag.
Failing to make that distinction produces an alert channel everyone learns to ignore, which is worse than no alerting.
Phase 4: Cut Over and Keep the Way Back
Flip:
DUAL_OLD_AUTH → DUAL_NEW_AUTH
Do this behind a runtime flag, not a deploy.
You want the rollback to be a configuration change measured in seconds because the thing you are protecting against is a failure that shows up under production load and could not be reproduced in staging.
Keep dual-write running for at least one full business cycle after the flip — a week, a month-end, or whatever your longest periodic job is.
The rollback path only exists while the old store is still current.
The moment you stop writing to it, NEW_ONLY is irreversible. That step should therefore be a separate, deliberate, scheduled change rather than the tail end of the cutover.
What Makes This Genuinely Hard
Transactions That Span Both Stores
If a single logical operation writes to two tables and only one has migrated, you have lost atomicity for the duration of the migration.
There is no clean fix.
The options are to:
- Migrate the whole transactional unit together.
- Accept a reconciliation job for the migration window.
Pick deliberately rather than discovering the problem mid-migration.
Schema Changes During the Migration
Someone will want to add a column while you are three weeks into a dual-write.
The migration router has to tolerate the change on both sides. That means:
- The new store's schema has to remain a superset.
- The write path has to tolerate unknown fields.
Reads That Are Not Key-Value
Everything above assumes point reads.
Range queries, aggregates, and joins do not dual-read cleanly.
A COUNT(*) disagreeing between stores during a backfill is expected, not necessarily a bug. Any verification built on aggregates will therefore produce noise for the entire migration.
Final Takeaway
A zero-downtime database migration is not simply a matter of copying data and switching traffic.
The dual-write window is the critical safety mechanism. Start dual-writing before the backfill, protect newer writes from stale backfill data, verify continuously, monitor fallback reads, and keep the old store writable long enough to preserve a real rollback path.
We do data platform and legacy modernization work at SoluLab — the InfuseNet case study covers one of these migrations end to end.
Top comments (0)