Engineering Atlas › System Design Interviews

Keeping copies of data consistent

14 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

When the pattern shows up in interviews

Listen for any phrase that implies a second copy of data: keyword search over a relational catalog, a fast denormalised order history, "notify shipping when an order is paid", or a cached profile. The interviewer is waiting to see whether you notice the consistency gap and close it deliberately, or draw an arrow from the API server to both stores and move on.

Why dual writes fail

The order service commits to Postgres, then publishes OrderPaid to Kafka. If the process dies in between, the order is paid but never shipped; reverse the order and a crash publishes an event for a rolled-back order. Even without crashes, two updates can commit as A then B while their publishes land as B then A, leaving the copy stale forever. Reordering the two writes only moves the failure, and in-memory retries die with the process (Confluent). Two-phase commit would fix atomicity, but most brokers, search engines and caches cannot take part in one.

The transactional outbox

Write the business row and an outbox row (payload, aggregate ID, increasing sequence) in the same local transaction. A relay reads unsent outbox rows in order, publishes them, and marks them sent (microservices.io). Atomicity comes from one database transaction, and a rolled-back business write takes its event with it. The relay can crash after publishing and before marking, so delivery is at least once. AWS's guidance draws the same conclusion: expect duplicates and make the consumer idempotent by tracking what it has processed (AWS Prescriptive Guidance). You also control the event shape: a domain event rather than a raw row image.

Change data capture

Databases already keep an ordered, durable log of committed changes for recovery and replication. CDC tools such as Debezium read that log and emit row-level change events: before and after images, operation, and log position. CDC needs no application changes and catches every write path, including migrations and manual fixes, but couples consumers to your table shapes. A popular combination is letting CDC tail the outbox table: domain events with log-based delivery and no polling (Debezium blog). Document stores offer the same shape: Azure's reference design writes the entity and its event in one Cosmos DB transactional batch on one logical partition and relays them from the change feed (Azure Architecture Center).

Polling

A job running WHERE updated_at > :last_seen is the baseline. It misses hard deletes, skips rows whose transactions commit late with an earlier timestamp, and loads the primary. Polling by an increasing ID has the same hole: a transaction that took ID 188 can commit after the one holding 189, so a reader that has moved past 189 never sees it (Precision Nutrition). Outbox relays that poll need the same care, which is one reason to let CDC read them.

Making consumers correct

Delivery is at least once and only ordered within a partition. Partition the stream by the entity key so all changes to one row arrive in order on one consumer. Carry a version with every event (an LSN, an outbox sequence number, or a row version column) and have the consumer apply an update only when its version is higher than the stored one. Elasticsearch supports this directly with external versioning, which indexes a document only when the supplied version is strictly higher than the stored one (Elasticsearch docs); for a cache, use a compare-and-set script. Deletes become tombstones that keep their version, so a late, older upsert cannot resurrect the row.

Bootstrapping and rebuilding

A new derived store needs history. Record the current log position, take a consistent snapshot, load it, then stream from the recorded position; overlap is harmless when consumers compare versions. To rebuild a search index, build a new one in the background and swap an alias once it catches up.

Down the stack

Layer 1The contract

The source database promises durability and a total order of committed transactions. The pipeline promises that every committed change is delivered at least once, in commit order per key, with no lost updates. The derived store promises eventual convergence: after writes stop, every replica reaches the source's last state, with lag usually measured in seconds. It does not promise read-your-writes, and it does not promise atomic visibility of multi-row transactions unless you group events by transaction ID.

Layer 2The mechanism

Postgres writes every change to the WAL, which is divided into 16 MB segment files by default (Postgres docs) and addressed by a log sequence number (LSN). With wal_level = logical, a logical replication slot runs an output plugin (pgoutput for built-in replication) that decodes WAL records into BEGIN, INSERT, UPDATE, DELETE and COMMIT messages, emitted only for committed transactions and in commit order. The slot stores confirmed_flush_lsn, the point up to which the consumer has acknowledged, and Postgres retains the WAL the slot still needs even when no consumer is connected (Postgres docs). Before Postgres 17, logical slots lived only on the primary, so a failover lost them and forced a re-snapshot; 17 added failover slots that a standby keeps in sync when sync_replication_slots is on (Postgres 17 release notes). Debezium reads the slot, emits one Kafka record per row change keyed by primary key, and periodically commits its offset back to the slot. MySQL's equivalent is the ROW-format binlog with GTIDs. Downstream, a compacted Kafka topic keeps the latest record per key, with null-valued records as delete tombstones.

Layer 3The machine

Each commit already costs a WAL fsync (sub-millisecond on local NVMe, often milliseconds on networked block storage). CDC adds no fsync; it reads WAL that was written anyway, often from the page cache. The danger is disk: if the consumer stalls, the slot pins WAL and the primary's disk fills at the rate of write traffic, which can be gigabytes per hour on a busy database. Kafka appends to segment files sequentially and serves consumers from the page cache, using sendfile to copy bytes to the socket without passing through user space. That zero-copy path is plaintext only; with TLS the broker reads, encrypts and writes in user space (KAFKA-13799). On the sink side, Elasticsearch accepts batches through the _bulk API and makes them searchable at the next refresh, one second by default on self-managed clusters and five seconds on Elastic Serverless. If you leave the interval unset, a shard that has had no searches for 30 seconds stops refreshing until the next search arrives (Elasticsearch docs). End-to-end lag is usually decoding plus batching plus refresh.

Why it's built this way

  1. You cannot get atomic writes across heterogeneous systems cheaply, so pick one system to commit and derive the rest.
  2. The database must already write an ordered log for recovery, so reusing it costs almost nothing on the write path.
  3. Logs are ordered but networks redeliver and consumers restart, so every event carries a version and consumers compare instead of trusting arrival order.
  4. Logs grow without bound, so retention is capped, which forces a snapshot-plus-stream bootstrap for new consumers.
  5. Retained WAL fills disks, so the slot's lag becomes a first-class alert.

Diagram

sequenceDiagram
    participant API as Order API
    participant DB as Postgres
    participant CDC as Debezium
    participant K as Kafka
    participant ES as Search index
    API->>DB: BEGIN, UPDATE orders, INSERT outbox, COMMIT
    DB-->>API: committed (WAL fsynced)
    CDC->>DB: read logical slot from confirmed LSN
    DB-->>CDC: change event (key, LSN, after image)
    CDC->>K: produce, key = order_id
    CDC->>DB: advance confirmed_flush_lsn
    K-->>ES: consumer bulk upsert with version = LSN
    ES-->>K: commit consumer offset

Worked example

A marketplace has 50 million product rows in Postgres and needs keyword search. Sellers make about 2,000 updates per second at peak. Each change event is roughly 1.5 KB, so the stream carries about 3 MB/s, trivial for Kafka. The topic is keyed by product_id with 24 partitions, so each partition sees about 85 events per second and per-product order is preserved.

Six indexers send _bulk requests of up to 500 documents or 200 ms, whichever comes first, each document using version_type=external with the source LSN so reordered events are rejected. Lag is roughly 200 ms of batching, under 100 ms of decoding and transport, and up to one second of refresh: about 1.5 seconds at p99. The product page reads from Postgres, so sellers see their own edits immediately.

For a mapping change, the team builds products_v2 from a snapshot. At 10,000 documents per second, 50 million rows take about 85 minutes. The indexer starts consuming from the LSN recorded before the snapshot, catches up, and then the products alias is switched atomically.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
Dual write in app codeNo infrastructureSilent divergence on crash or raceNever for data you care about
Polling by updated_atSimple, any databaseMisses deletes, late commits, scan loadLow volume, coarse freshness
Transactional outboxAtomic with business write, domain-shaped eventsExtra table and relay, at-least-onceEvents to other services
Log-based CDCNo app changes, catches every write, low overheadCouples consumers to schema, slot operationsSearch, cache, warehouse replicas

Pitfalls in production

Leadership lens

Check yourself

  1. Why does publishing to Kafka inside the database transaction (before COMMIT) not fix the dual-write problem?

    Show answer

    The publish is not rolled back if the transaction later aborts, so consumers see an event for a change that never happened.

  2. A consumer receives version 7 of a row after it already stored version 9. What should happen, and what mechanism ensures it?

    Show answer

    It should be discarded. The sink compares the event's version (LSN or row version) with the stored one and applies only higher versions.

  3. Why does a deleted row need a tombstone that carries a version rather than simply removing the key?

    Show answer

    If the key is removed, a delayed older upsert looks new and resurrects the row; a versioned tombstone lets the consumer reject it.

  4. What happens to the primary if a CDC connector is down for a day?

    Show answer

    Its replication slot retains all WAL since its confirmed position, so disk usage grows with write volume until the connector catches up or the slot is dropped.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →