Dual-Writing Across Two Databases During a Cutover

You are moving a table from an old database to a new one — a different engine, a different cluster, or a fresh account you cannot reach in a single transaction — and for the cutover window your application has to write both. Reads still come from the old database, the new one is being backfilled and verified, and every live mutation must land in both so the two never drift while you watch. Then the pager goes off: the new database timed out on a write that the old one already committed, and now one store is a row ahead of the other with no outage and no stack trace to point at the moment it happened. This page is the runbook for that cross-database window — how to order the two writes, what to do when one commits and the other fails, and how to prove the two datastores agree before you move reads over.

Two databases cannot share a local transaction, so the atomicity you rely on inside one engine is gone the instant the second store is a separate system. This is the hardest surface of Dual-Write Synchronization, and it is the write half of the Expand and Contract Methodology: you cannot switch reads or run the contract step until both stores are provably consistent. Examples are PostgreSQL on both ends; the MySQL 8.0 INSERT … ON DUPLICATE KEY UPDATE form is equivalent where noted.

Symptom / Error Signatures

Cross-database dual-write failures rarely raise a clean error at the point of corruption. The write that fails is the second one, after the first has already committed, so the application often logs a warning and moves on. Watch for these signatures instead:

  • A windowed checksum job reports a nonzero delta between the old and new databases: divergent rows: 412 (0.07%).
  • Application logs show context deadline exceeded, driver: bad connection, or ERROR: canceling statement due to statement timeout on the new datastore only, with the same request having succeeded on the old one.
  • pg_stat_activity on the old database shows sessions idle in transaction — a request that committed the old write, then blocked waiting on the slow new store while holding nothing to roll back.
  • Row counts diverge in one direction only: the old database is consistently ahead, which is the fingerprint of an old-first, non-durable ordering.
  • Foreign-key or NOT NULL violations during reconciliation because a parent row reached one database but its child reached the other.

Confirm the drift with a windowed comparison keyed on the primary key and a monotonic timestamp, never a full-table scan across both engines:

-- PostgreSQL (old database) · read-only · safe on primary · batches via LIMIT on indexed id
-- Context: run against the OLD store, then diff the id/updated_at set against the new store.
SELECT id, updated_at
FROM   orders
WHERE  updated_at >= now() - INTERVAL '2 hours'
ORDER BY updated_at DESC
LIMIT  5000;

Root Cause Analysis

Every silent cross-database loss traces to one fact: two independent commits cannot be made atomic by sequencing them. Whatever order you pick, there is a gap between the first commit and the second where a crash, a timeout, or a deploy leaves one database ahead. The order you choose only decides which database ends up ahead and whether the gap is recoverable. Three ordering strategies diverge sharply here:

Ordering strategy Failure mode if the second write fails Recoverable without a scan? Use when
Old-first, best-effort new write Old committed, new lost silently; old is ahead No — only reconciliation finds it Never for a cutover
New-first, best-effort old write New committed, old lost; reads (still on old) miss the row No — user-visible immediately Never
Old-first + transactional outbox Old and outbox commit together; relay retries the new write Yes — the outbox is the durable record Always, for cross-database

The first two are best-effort dual calls and both lose data; they differ only in which store you notice first. The durable option makes the intent to write the second database part of the first database’s transaction: the old row and an outbox row commit atomically, and a relay drains the outbox to the new store with at-least-once, idempotent delivery. That is the same mechanism proven in Preventing Data Loss During Dual-Write Migrations, applied across a database boundary instead of two tables in one engine. Because delivery is at-least-once, every write into the new database must be idempotent and version-guarded, or a retry double-applies and a reordered message clobbers a newer value.

Where the row is lost under each write ordering Old-first best-effort commits the old store then loses the new write; new-first best-effort commits the new store then loses the old write a live read needs; old-first plus outbox commits both together and a relay retries the new write until it lands. first commit second commit outcome Old-first, best-effort Old DB committed New DB fails × row lost silently old DB ends ahead New-first, best-effort New DB committed Old DB fails × live read misses row reads still on old DB Old-first + outbox Old DB + outbox atomic New DB relay retries recovered outbox is durable record
Only the transactional outbox makes the intent to write the second database durable, so a failed second commit becomes a retry rather than a silent loss.

Immediate Mitigation

If your checksum job is reporting active divergence mid-cutover, contain it before it compounds. Reads are still on the old database, so the old store is authoritative — that is your safety net. Execute in this exact order.

  1. Freeze the read cutover. If a feature flag is ramping reads toward the new database, set it back to the old store immediately so no user reads an inconsistent new row.

    # bash · incident response · run from a host with flag-service credentials
# Context: forces reads back to the authoritative OLD database; instant, no data touched.
./bin/flags set read_source=old_db --tenant all

  2. Keep dual-writing, but make new-store failures loud. Do not disable the second write — that widens the gap. Instead flip the relay to fail-fast so a slow new database drops to the retry queue instead of holding old-database connections idle in transaction.

    # bash · run on the relay/worker hosts · takes effect on next poll
# Context: cap the new-store write timeout so a stall enqueues a retry instead of blocking.
./bin/outbox config set new_db.write_timeout=2s new_db.on_timeout=requeue

  3. Quantify the divergence. Run the windowed comparison from the symptom section in batches to size the affected set before repairing anything. Record the oldest divergent updated_at — that is when the gap opened.

  4. Check replication and outbox backlog. A growing unprocessed outbox count is the leading indicator that the new store is falling behind live writes.

    -- PostgreSQL (old database) · read-only · run continuously during the incident
-- Context: rising undelivered count means the relay cannot keep up — page before it becomes drift.
SELECT count(*) AS undelivered,
       now() - min(created_at) AS oldest_pending
FROM   outbox
WHERE  delivered_at IS NULL;

  5. Repair the new store from the authoritative old store. Re-drive the divergent keys through the version-guarded upsert (below) so the new database is overwritten with old-database truth for exactly those rows. Re-verify counts before lifting the freeze and resuming the read ramp.

Containing mid-cutover divergence, old database authoritative throughout A divergence alert triggers five ordered steps: freeze the read cutover, make new-store failures fail fast, quantify the divergence window, check the outbox backlog, then repair the new store from the authoritative old database. The old database stays the source of truth across every step. OLD DATABASE = source of truth for every step below ALERT divergent rows > 0 1 Freeze read cutover flag reads back to old DB 2 Fail-fast the relay timeout to requeue, no stall 3 Quantify divergence windowed diff, oldest ts 4 Check outbox backlog rising undelivered = drift 5 Repair new store from authoritative old store re-drive divergent keys through version-guarded upsert, re-verify, then lift freeze
Reads sit on the old store, so it is authoritative: contain the gap first, size it, then overwrite the new database with old-database truth before resuming the ramp.

Permanent Fix / Long-Term Pattern

The durable pattern is old-first through a transactional outbox, with an idempotent, version-guarded write into the new database, and a read cutover gated on a consistency SLO — never on a calendar date. The mechanism and its data-loss reasoning live in the parent topic, Dual-Write Synchronization; this is how it is shaped for a genuine two-database cutover.

  • Write old-first, into one local transaction with an outbox. The old row and the outbox entry commit together, so the intent to write the new database survives any crash. A relay reads the outbox and applies the new-database write with retries.

    -- PostgreSQL (old database) · application request path · single local transaction
-- Context: legacy row + outbox row commit atomically; new-DB delivery is async, at-least-once.
BEGIN;
  INSERT INTO orders (id, customer_id, total, updated_at)
  VALUES (:id, :customer_id, :total, :updated_at);
  INSERT INTO outbox (idempotency_key, aggregate_id, payload, created_at)
  VALUES (:idem_key, :id, :payload, now());
COMMIT;

  • Make the new-database write idempotent and monotonic. The relay delivers at-least-once, so the write must upsert by a stable key and reject any value older than what is already there. This single predicate makes retries and reordering harmless.

    -- PostgreSQL (new database) · executed by the outbox relay · idempotent + reorder-safe
-- Context: the conflict WHERE keeps the higher version; a stale re-delivery changes nothing.
INSERT INTO orders (id, customer_id, total, version)
VALUES (:id, :customer_id, :total, :version)
ON CONFLICT (id) DO UPDATE
  SET customer_id = EXCLUDED.customer_id,
      total       = EXCLUDED.total,
      version     = EXCLUDED.version
  WHERE EXCLUDED.version > orders.version;

  • Keep the old database authoritative until the SLO holds. Advance the read ramp only after a continuous checksum job reports 99.99% consistency over a rolling 24-hour window. The repair-mode tooling that closes the last divergent rows is detailed in Reconciling Divergence Between Dual-Written Tables; backfilling the pre-cutover rows is covered by Backfill Optimization, and gating the whole ramp behind Feature Flag Rollouts is what lets you disable the second store instantly.

  • Cut over reads in stages, then stop dual-writing last. Ramp reads 1% → 50% → 100% while dual-write stays on, hold at full for the SLO window, and only then disable the old-database write and drain the outbox to empty. Removing the old write path before reads have fully moved is how a synchronization bug becomes a user-visible outage.

Ordered cutover: ramp reads first, remove the old write last Dual-write remains on across the whole ramp while reads move from one percent to fifty percent to one hundred percent of the new database. After the consistency SLO holds, the outbox drains to zero, and only then is the old-database write path removed. DUAL-WRITE ON — old-first through outbox, unchanged across the whole ramp OFF reads 1% canary reads 50% watch checksum reads 100% hold for SLO window outbox → 0 backlog drained old write removed last SLO gate: 99.99% / 24h Order is the safety property Reads move fully first · outbox drains to empty · the old write path comes out only after
Removing the old write before reads have fully moved turns a sync bug into a user-visible outage, so the old write path is always the last thing to go.

Verification Checklist

Return to the parent topic for the full mechanism and decision criteria: Dual-Write Synchronization.

Frequently Asked Questions

Which database should I write to first across the boundary? Write the store that reads still come from — the old database — first, and do it in one local transaction with an outbox row. That keeps the authoritative store correct even if the new-database write fails, and the durable outbox means the intent to write the new store is never lost. New-first ordering fails a live read the moment the old write drops, so it is never right during a cutover.

Can I use two-phase commit instead of an outbox for two databases? You can, but the outbox is almost always better for a cutover. Distributed 2PC blocks all participants when the coordinator fails, turning a transient new-database stall into a stuck old-database transaction holding idle in transaction. The outbox degrades gracefully — the new write simply retries from a durable record — which is exactly the failure handling a cutover window needs.

How do I know it is safe to move reads to the new database? Gate the read ramp on a consistency SLO, not a date. Require a continuous checksum job to report 99.99% agreement over a rolling 24-hour window, an outbox backlog that drains to zero under peak load, and a rehearsed rollback. Ramp reads in stages with dual-write still on, and stop dual-writing only after reads are fully cut over and the SLO has held.

Related Articles