Reconciling Divergence Between Dual-Written Tables

The cutover plan was clean: dual-write to orders and the new orders_v2 for two weeks, then flip reads. On flip day a spot check finds orders_v2 is missing 312 rows and has 1,847 rows whose total does not match the legacy table. Nothing logged an error. The dual-write path simply failed for some writes — a deploy gap where only the legacy write was live, a transient error on the second write that was swallowed, a race where one write landed and the other did not — and each silent miss left the two tables a little further apart. Before you can trust the new table enough to read from it, you have to detect exactly where it diverges and repair only the divergent rows, without a full re-copy and without overwriting newer data with stale data.

This is the validation gate that stands between a dual-write and a safe cutover. The write-path correctness that minimizes divergence in the first place is covered in Preventing Data Loss During Dual-Write Migrations; this page assumes some divergence has already crept in and shows how to find and fix it.

Symptom / Error Signatures

Divergence is silent by nature, so you detect it with active checks rather than waiting for an error.

• A row-count gap: SELECT count(*) on the legacy table and the new table return different numbers after accounting for any legitimately filtered rows.
• A checksum mismatch: an aggregate hash over a key range differs between the two tables even when counts match, meaning the same rows hold different values.
• Spot-check failures: sampled primary keys present in the legacy table are absent from the new table, or present with stale column values.
• Application bugs after a premature read flip: users see an old address, a missing recent order, or a total that does not match the legacy system of record.
• Reconciliation job logs showing a steady trickle of repaired rows that never reaches zero — a sign the dual-write path is still dropping writes, not just that there is a historical backlog.

The last signal is the important diagnostic: a shrinking divergence count means you are catching up on a fixed backlog; a non-zero steady-state count means the live write path is still broken and reconciliation alone will never converge.

Root Cause Analysis

A dual-write performs two writes that are not a single atomic unit unless they share one transaction. Whenever they do not — because the new table lives on a different connection, because the second write is fire-and-forget, or because a deploy briefly ran code that wrote only the legacy table — a failure between the two leaves the tables inconsistent. Three divergence shapes result, and each needs a different repair:

Detecting these efficiently is the core challenge. A naive row-by-row comparison of two large tables is itself an expensive scan that competes with production traffic. The scalable technique is to compare aggregate checksums over key ranges: hash each block of rows on both sides and only drill into the blocks whose hashes differ. Matching blocks are skipped entirely, so the comparison cost is proportional to the divergence, not the table size.

The repair must respect which table is the source of truth. During dual-write the legacy table is authoritative, so reconciliation copies legacy → new, and it must be idempotent and must never overwrite a newer value in the new table with a stale legacy one — which means comparing an updated-at timestamp or version, not blindly overwriting. This is the same upsert discipline that governs all safe data movement, detailed in Making Data Backfills Idempotent With Upserts.

Immediate Mitigation

When divergence is found, stabilize before repairing, and never flip reads onto a table you have not reconciled.

1. Do not flip reads. If a cutover is mid-flight, keep reads on the legacy table — it is the system of record until the new table is proven equal.

2. Measure the gap first with a cheap row count, then a range checksum to find where the rows differ.

-- PostgreSQL · read-only, safe anytime · row-count gap by key range
-- Run on the primary so you compare consistent, non-lagging data.
SELECT 'legacy' AS src, count(*) FROM orders   WHERE id BETWEEN 1 AND 1000000
UNION ALL
SELECT 'new'    AS src, count(*) FROM orders_v2 WHERE id BETWEEN 1 AND 1000000;

-- PostgreSQL · read-only · per-range checksum; differing block = divergence
-- md5 over an ordered, concatenated projection of the columns that must match.
SELECT (id / 1000) AS block,
       md5(string_agg(id || ':' || coalesce(total::text,'') , ',' ORDER BY id)) AS h
FROM   orders
GROUP  BY block ORDER BY block;
-- Run the identical query on orders_v2 and diff the (block, h) pairs.

-- MySQL · read-only · equivalent range checksum using GROUP_CONCAT + MD5
-- Raise group_concat_max_len so large blocks are not silently truncated.
SET SESSION group_concat_max_len = 1024000;
SELECT id DIV 1000 AS block,
       MD5(GROUP_CONCAT(CONCAT_WS(':', id, COALESCE(total,'')) ORDER BY id)) AS h
FROM   orders GROUP BY block ORDER BY block;

3. Confirm the live dual-write is healthy before repairing history. If new writes are still being dropped, fix the write path first — otherwise reconciliation chases a moving target. Verify both writes share a transaction or have a reliable retry, per Preventing Data Loss During Dual-Write Migrations.

4. Repair only the diverging ranges identified by the checksum, not the whole table.

Permanent Fix / Long-Term Pattern

The durable solution is a reconciliation job that runs continuously during the dual-write window, detects divergence by range checksum, and repairs it with a version-aware idempotent upsert — so the tables converge and stay converged until cutover.

The repair upsert copies the authoritative legacy value into the new table, inserting missing rows and updating stale ones, but only when the legacy row is newer or the new row is absent. The version guard is what prevents reconciliation from clobbering a fresh dual-write with a stale snapshot.

-- PostgreSQL · run on primary as a throttled job · idempotent, version-guarded repair
-- Copies legacy -> new only when new is missing or older; safe to re-run.
INSERT INTO orders_v2 (id, total, updated_at)
SELECT o.id, o.total, o.updated_at
FROM   orders o
WHERE  o.id BETWEEN :lo AND :hi
ON CONFLICT (id) DO UPDATE
  SET total = EXCLUDED.total, updated_at = EXCLUDED.updated_at
  WHERE orders_v2.updated_at < EXCLUDED.updated_at;   -- never overwrite newer data

-- MySQL · run on primary as a throttled job · idempotent, version-guarded repair
INSERT INTO orders_v2 (id, total, updated_at)
SELECT id, total, updated_at FROM orders WHERE id BETWEEN :lo AND :hi
ON DUPLICATE KEY UPDATE
  total      = IF(VALUES(updated_at) > orders_v2.updated_at, VALUES(total), orders_v2.total),
  updated_at = IF(VALUES(updated_at) > orders_v2.updated_at, VALUES(updated_at), orders_v2.updated_at);

Orphan rows in the new table (present in new, absent in legacy) are handled separately and conservatively: while legacy is the source of truth, a row in orders_v2 with no orders match is either a delete that missed the new table or a write that should not have happened, so log it for review rather than auto-deleting unless you are certain legacy is authoritative for deletes.

Run the whole job under the same controls as any large data operation: iterate ranges with a keyset cursor, throttle against replica lag, and treat every write as idempotent so a retried range never double-applies. Those throttling and resumability mechanics are the subject of Optimizing Backfill Scripts for Zero-Downtime Deploys. Cutover reads only after a full-table checksum sweep reports zero diverging blocks across two consecutive passes — the gate the parent Dual-Write Synchronization section defines for declaring the new table trustworthy.

Verification Checklist

• A row-count comparison per key range matches between the legacy and new tables.
• A range-checksum sweep reports zero diverging blocks across two consecutive passes before reads are flipped.
• The reconciliation upsert is version-guarded (updated_at / version compare) so it never overwrites newer data with a stale legacy value.
• The live dual-write path is confirmed healthy — the divergence count trends to zero rather than holding at a non-zero steady state.
• Orphan rows (present in new, absent in legacy) are logged for review, not silently auto-deleted, while legacy remains the source of truth.
• The job iterates with a keyset cursor and throttles against replica lag, so reconciliation never causes its own incident.

Frequently Asked Questions

How do I detect divergence without scanning two huge tables row by row? Compute an aggregate checksum (md5 of an ordered, concatenated projection) per key-range block on both tables and compare the per-block hashes. Blocks whose hashes match are identical and skipped; you only scan the blocks that differ. The cost is proportional to the amount of divergence rather than to the table size, which makes it practical against production-scale tables.

Won’t running reconciliation overwrite recent dual-writes with stale data? Not if the repair upsert is version-guarded. The ON CONFLICT/ON DUPLICATE KEY clause updates the new row only when the legacy row’s updated_at (or version) is newer than the existing new row. A reconciliation pass carrying a stale snapshot then skips any row that has since been updated by a live write, so it can only ever fill gaps, never regress fresh data.

The divergence count keeps coming back after I repair it. Why? Because the live dual-write is still dropping writes — reconciliation is repairing history while the present keeps re-diverging. A count that trends to zero means a fixed backlog; a count that holds steady means the write path itself is broken. Fix the dual-write (shared transaction or reliable retry) before expecting reconciliation to converge.

What do I do about rows in the new table that aren’t in the legacy table? Treat them conservatively while the legacy table is the source of truth. An orphan is usually a delete that hit only the legacy side or a write that should not have happened. Log orphans for review rather than auto-deleting, and only purge them once you have confirmed the legacy table is authoritative for deletes as well as inserts and updates.