Change data capture vs dual writes

Keeping two stores in agreement

You write an order to Postgres, but you also need it in Elasticsearch for search, in Redis for caching, and in a warehouse for analytics. The data must end up in both the database and the derived store. The naive approach — have the application write to each — is where a surprising amount of data corruption comes from. Change data capture is the disciplined alternative.

Dual writes (and why they drift)

The app writes to the database, then writes to the index:

db.save(order)            # 1
search.index(order)       # 2  -- two separate systems, no shared transaction

Two failure modes are baked in:

• Partial failure — step 1 commits, step 2 throws (the index was briefly down). Now the database and index disagree permanently, and there’s no transaction spanning both to roll back. The reverse (index updated, DB write rolls back) leaves a phantom in search.
• Race / ordering — two concurrent updates to the same order can hit the DB in one order and the index in the other, so the index ends on the older value. Forever.

You can paper over it with retries and reconciliation jobs, but the fundamental problem is there’s no atomicity across two independent systems.

Change data capture

Instead of writing twice, write once to the database and let everything else be driven by the database’s own commit log (the WAL/binlog). A CDC connector tails that log and streams every committed change to consumers:

app ──write──> DB ──(WAL/binlog)──> CDC connector ──> search index
                                                   ──> cache
                                                   ──> warehouse

This inverts the guarantees:

• No partial failure — the log only contains committed changes, so a consumer can’t see a write the DB rolled back; if a consumer is down it resumes from its offset and catches up. The DB is the single source of truth.
• Correct order — the log is the database’s real commit order, so derived stores apply changes in the same sequence and converge.
• Decoupled — the app doesn’t know or care about the index; add a new consumer without touching write code.

The cousin worth naming is the transactional outbox: write the row and an “event” to an outbox table in one local transaction, then a relay (often CDC on the outbox) ships the events — atomicity without a distributed transaction.

The side-by-side

Where it shows up

Debezium + Kafka Connect streaming Postgres/MySQL/Mongo changes; keeping a search index or cache in sync with the system of record; building materialized views and feeding data warehouses; the outbox pattern in event-driven services.

The interview cue

When a design needs the same data in a database and a search index/cache, don’t say “the service writes both.” Say: “Dual writes drift — there’s no transaction across two systems, so a partial failure or a race leaves them permanently out of sync. I’d drive the derived stores with change data capture off the DB’s commit log (or a transactional outbox), so there’s one source of truth, correct ordering, and consumers that recover by replaying from their offset.” Naming the dual-write trap and reaching for CDC is the trade-off being tested.