Keeping Derived Stores in Sync (CDC & Outbox)

The dual write: the most common production sync bug

The moment you have a primary database plus any derived store (a Redis cache, an Elasticsearch index, a read replica, an analytics warehouse), you have a sync problem. The naive solution is the dual write: in your request handler, write to the DB, then write to the cache/index in the same code path.

handler:
  db.save(order)          # write 1
  cache.set(order)        # write 2   <-- if this fails or the process
  search.index(order)     # write 3       dies here, stores diverge

This is broken because the writes are not atomic and there is no shared transaction across a database and a cache. Any of these happens routinely: write 1 commits and the process crashes before write 2 (cache stale forever); write 2 succeeds but write 1's transaction rolls back (the cache holds a row the DB never persisted); or two concurrent requests apply their DB writes in one order and their cache writes in the opposite order (the cache ends on the older value). Under load, partial failure is a steady drip of divergence you discover weeks later as "search shows a product that was deleted."

The disciplined fix, in two parts

Transactional outbox: stop writing to the second system from the handler. Instead, in the same database transaction as your business write, insert a row into an outbox table describing the event ({id, aggregate_id, type: OrderPlaced, payload, created_at}). The business change and the intent-to-publish commit together or not at all. A separate relay process reads unpublished outbox rows and publishes them to a message broker (Kafka), marking them sent.

Log-based change data capture (CDC): rather than write an outbox by hand, tap the database's own replication log, which already records every committed change durably and in order. Debezium reads Postgres logical decoding, the MySQL binlog, or the MongoDB oplog and emits an ordered stream of row changes to Kafka. Downstream consumers (a cache updater, an Elasticsearch sink, a warehouse loader) subscribe and apply. The outbox is the right tool when you need domain events (OrderPlaced) rather than raw row diffs; CDC is the right tool when you want to mirror table state to derived stores with no application changes.

The honest delivery guarantee

Exactly-once end-to-end is a fantasy across a broker and heterogeneous sinks: the relay can crash after publishing but before marking the outbox row sent, so it republishes. The realistic and correct target is at-least-once delivery plus idempotent consumers. Make every consumer safe to re-apply the same event: key the cache/index write by the event's primary key and use last-writer-wins on a version/LSN, or dedupe on event id. Then a duplicate is a no-op.

Operational reality: you also need backfills and replays (snapshot the current table state to bootstrap a brand-new index, then switch to the live stream), and you must monitor replication slot / consumer lag. A Postgres logical replication slot that a stalled Debezium connector stops advancing will pin WAL and eventually fill the disk, taking the primary down.

Interview nuance: if the interviewer says "just write to the DB and the cache," name the dual-write problem explicitly and reach for outbox or CDC. If they push on "why not exactly-once," say the honest thing: at-least-once plus idempotent, versioned consumers is simpler and strictly more robust, and it is what Kafka-based pipelines actually run.

Recap: never dual-write to a DB and a derived store; commit the change and its event together via a transactional outbox, or tap the DB log with CDC (Debezium), publish through Kafka, and make consumers idempotent so at-least-once delivery is correct, while monitoring replication-slot lag and supporting snapshot backfills.