Your Dual-Write Migration Is Lying to You: Catching Silent Divergence Before Cutover

You flip the read path to the new database. Within a minute, a fraction of a percent of lookups start returning 404, and a handful return data that's a few minutes stale. The write path has been dual-writing to both stores for three weeks. Every write returned 200. The backfill job logged "complete." By every dashboard you had, the migration was done.

It wasn't. The old store and the new store had been quietly drifting apart the entire time, and the cutover is exactly the moment you discover it — when the new store becomes the source of truth for reads and its gaps become user-visible.

Why "both writes succeeded" doesn't mean "both stores match"

The seductive thing about dual-write is that it looks correct locally. Your write path does something like this:

def save_order(order):
    old_db.upsert(order)          # legacy Postgres
    new_db.upsert(order)          # Spanner
    return 200

Both calls return, you return 200, and the request looks clean in traces. But this code has at least three ways to diverge the two stores, and none of them show up as an error on the happy path:

Partial failure. old_db.upsert succeeds, then new_db.upsert throws (timeout, deadline exceeded, a transient UNAVAILABLE). Now the row exists in the old store and not the new one. If your handler retries the whole request, you may double-write the old store too.
Ordering races. Two concurrent updates to the same key can hit the two databases in different orders. Old store ends at value B, new store ends at value A. Both writes "succeeded."
Backfill vs. live-write races. The backfill copies a row, then a live dual-write updates it, then a slow backfill worker overwrites it with the stale snapshot it read earlier. Last-writer-wins, and the last writer was the backfill.

Each of these is individually rare. Across tens of millions of rows and weeks of traffic, "rare" is a steady leak. And because the new store isn't serving reads yet, nothing surfaces the drift. You are accumulating an unknown number of mismatches with no counter pointed at them.

The ladder that actually gets you to cutover

A safe migration isn't "turn on dual-write, wait, cut over." It's a ladder where each rung proves something before you climb the next. The read path only moves at the very end, and only after a number you trust says it's safe.

The rungs, in order:

Backfill the new store from a consistent snapshot of the old one.
Dual-write all new mutations to both, with the old store still authoritative.
Shadow-read: on every read, fetch from both stores, return the old store's answer to the user, and compare the two off the hot path.
Reconcile: a batch job that scans for keys present/different across the two stores and repairs drift, feeding a mismatch counter.
Cut over reads only when the mismatch rate has sat at zero long enough to trust it — then decommission dual-write.

Shadow reads turn drift into a metric

The shadow read is the rung people skip, and it's the one that converts an invisible leak into a graph you can watch. The key is that the comparison must never affect the user's response or latency:

def get_order(order_id):
    result = old_db.get(order_id)          # authoritative; user gets this
    # fire-and-forget comparison, never blocks the response
    executor.submit(shadow_compare, order_id, result)
    return result

def shadow_compare(order_id, expected):
    try:
        actual = new_db.get(order_id, timeout=0.25)
        if normalize(actual) != normalize(expected):
            metrics.increment("migration.shadow_mismatch",
                              tags={"table": "orders"})
            log.warning("shadow mismatch", order_id=order_id)
    except Exception:
        metrics.increment("migration.shadow_error")

Two details matter more than they look. First, normalize() — the two stores will represent timestamps, decimals, and null-vs-empty differently, and if you don't canonicalize before comparing, you'll drown in false mismatches and learn to ignore the metric. Second, the comparison runs off the response path, so a slow or failing new store can never degrade live traffic while it's still shadow.

Backstop the shadow reads (which only cover keys that actually get read) with a full reconciliation sweep for the long tail of cold rows:

-- keys that diverge between the two stores, run in batches
SELECT o.id
FROM   old_orders o
LEFT JOIN new_orders n ON n.id = o.id
WHERE  n.id IS NULL                       -- missing in new store
   OR  n.updated_at <> o.updated_at        -- or stale/divergent
ORDER  BY o.id
LIMIT  5000;

Cutover is a decision made against these two signals: the shadow-mismatch rate and the reconciliation backlog. Both at zero, held there across a full traffic cycle including your peak, is the go condition. "It's been three weeks" is not.

Where an AI agent genuinely helps — and where it must not

Here's the honest part about the "automate the migration" pitch behind tools like Antigravity CLI, or any coding agent you point at this. The reason dual-write migrations get done badly is rarely that the pattern is unknown — it's that doing it right means writing the same scaffolding for forty tables: a dual-write shim, a backfill job, a shadow comparator with per-column normalization, and a reconciliation query, each one slightly different because each table's schema is slightly different. That's pure toil, and it's exactly what an agent is good at: given the two schemas, it can generate the shim, the normalizer, and the reconciliation SQL per table far faster than a human grinding through them, and keep them consistent.

What the agent must not own is the go/no-go. The cutover decision is a judgment call about whether a zero on a dashboard means the stores are genuinely equivalent or whether your comparator has a blind spot — a column it isn't checking, a normalization bug hiding real drift, a peak you haven't hit yet. That's a human reading real telemetry and staking the data's integrity on it. Let the agent remove the toil that makes teams cut corners; keep the irreversible decision on a person who can be wrong and knows it. The failure mode of over-trusting here isn't a bad code review comment — it's a data-loss cutover you can't cleanly roll back.

Lesson

Dual-write proves your writes reach two places. It proves nothing about whether those two places agree. A migration is only as safe as the counter you point at the divergence — build the shadow reads and the reconciliation job first, and treat "both writes returned 200" as the start of verification, not the end of it. Automate the scaffolding all you want; keep the cutover a decision a human makes against a number they actually trust.

Hitting something like this in production? I help teams with performance engineering, SRE/observability, and AI-driven root cause analysis — work with me.

Search This Blog

Lets Architect the Performance Engineering