System Design Interview: How Would You Reliably Sync a Search Index with CDC?

Prompt and Applicable Roles

A product database is the source of truth for search. Design a near-real-time CDC pipeline that reliably syncs inserts, updates, and deletes to a search index. It must support an initial snapshot, catch-up, duplicate delivery, consumer interruption, schema evolution, and index rebuilds. Explain how you would prove that events are not lost and an older event cannot overwrite a newer one.

This fits backend, data-infrastructure, search-platform, and system-design interviews. Assume the source can expose commit order or an equivalent log position, and the search index is a rebuildable derived system. Kafka, Debezium, and Elasticsearch are options, not requirements; define semantics and failure boundaries first.

What the Interviewer Is Testing

The interviewer wants to see whether you separate “the database write committed” from “the index is eventually visible,” with an observable contract for each stage. A strong answer defines the event key, operation, transaction or log position, and schema version; chooses log-based capture instead of an unsafe timestamp poll; and handles snapshot overlap, at-least-once replay, per-key ordering, delete tombstones, and alias cutover. A diagram with only a database, queue, and search box cannot prove reliability without checkpoints, replay, and reconciliation rules.

Questions to Clarify Before Answering

• What is the freshness target? Five seconds after commit or minutes? This sets buffering, alerts, and fallback budgets.
• Which ordering is required? Usually one product must follow source commit order; a global order across products is unnecessary. Cross-table invariants may require an aggregate event.
• Are deletes hard or soft? Hard deletes need durable tombstones or delete events; soft deletes need visibility rules in the indexed document.
• May writes continue during the snapshot? If yes, define a snapshot position and retain changes after that position to cover the snapshot window.
• How does the schema evolve? Can old consumers ignore added fields? Do removed or retyped fields require dual read/write, a new event version, or a rebuild?
• Must rebuilds be zero-downtime? If yes, write a new index, cut an alias atomically, and retain a replay point for old consumers.

30-Second Answer Framework

“I would treat the primary database as the source of truth and capture committed inserts, updates, and deletes from its log. Each event carries a key, operation, source LSN, transaction ID, schema version, and before/after values. The initial load starts from a consistent snapshot while recording its log position; events after that position continue through the same replayable stream, and consumers idempotently write by product key.

Delivery is at least once. The checkpoint advances only after the index side effect succeeds, and duplicate events are rejected by a key plus version or LSN condition. A stopped consumer resumes from its checkpoint. I would expose lag, oldest-event age, slot retention, and database-to-index version samples. A rebuild writes the same stream to a new index, waits until it catches up, and then atomically switches the alias.”

Step-by-Step Deep Dive

Step 1: Define the event contract and capture boundary

Log-based CDC reads committed database changes and preserves source order or a log position. In PostgreSQL, logical decoding extracts changes from the WAL, and a replication slot represents a stream that can be replayed in origin order. A slot retains required WAL, so slot retention must be monitored; a stalled connector can consume the primary’s disk.

Each event should include entity_id, operation, source_position, transaction_id, schema_version, before, and after. Use source_position for audit and deduplication, not message arrival time as business order. If one transaction changes several products, decide whether the index may expose them one by one or whether the stream must aggregate at a transaction boundary.

Step 2: Connect the snapshot and stream with one position

The dangerous overlap is a snapshot reading an old row while the stream delivers a newer event for the same key. Record a log position P0 when the snapshot starts. Snapshot documents represent the state at the start; events after P0 remain available and are applied after the snapshot result.

P0 = captureSourcePosition()
startStreaming(after=P0)
for row in consistentSnapshot():
  indexUpsert(row, version=P0)

for event in stream:
  if event.position > indexedVersion[event.key]:
    applyIdempotently(event)
  checkpoint(event.position)  # only after index write succeeds

Real connectors may use snapshot windows, primary-key chunks, and buffers to resolve collisions between READ and UPDATE events. In an interview, explain that this prevents an old snapshot row from overwriting a committed update; “start the stream after the snapshot finishes” is not sufficient.

Step 3: Make ordering, idempotence, and recovery explicit

Partition by entity_id so one product’s events retain source order while different products run in parallel. Use an external version, conditional write, or versioned document so an event can overwrite only an older position. A DELETE writes a tombstone or versioned delete and retains enough metadata to prevent a late UPDATE from resurrecting the document.

The checkpoint means “the side effect for this event is durably complete.” Do not commit it after pulling a message or sending an HTTP request. A crash after the index write but before the checkpoint causes a duplicate, so the target write must be idempotent. If the checkpoint advances before the index, data is lost; either define a verifiable commit boundary or use replayable index work plus reconciliation.

Step 4: Handle replay, schema evolution, and rebuilds

Give each consumer an independent slot or equivalent progress, rather than having consumers compete for one single-consumer cursor. Before replay, freeze or label the target index’s version policy, bound the replay range, and ensure old events can only write older versions. Define compatibility rules: old consumers can often ignore an added optional field, while a removed or retyped field may need a new event version, dual read/write, or reindexing.

Do not empty the live index for a rebuild. Create a new index and replay from the same snapshot position until its applied position reaches a cutover gate. Atomically switch the alias and continue consuming the same stream. If cutover fails, keep the old alias and the new index’s progress, repair, and catch up again; do not guess a new starting point.

Step 5: Prove reliability with metrics and reconciliation

Track CDC read latency, partition backlog, oldest-event age, slot WAL retention, each consumer checkpoint, index-write failures, retries, dead letters, and the version difference from primary-key samples in the database and index. Deletes deserve their own tombstone and residual-document counters.

Test by stopping the consumer, duplicating delivery, reordering messages across partitions, updating a key during a snapshot, delivering a late delete, changing the schema, and failing over the primary. A reconciliation tool should re-read the current database version, replay the stream to a chosen position, and emit the smallest inconsistent sample. An empty queue alone does not prove that events were not skipped or index writes did not fail.

High-Quality Sample Answer

“I would define the contract first: the database is authoritative and the index is rebuildable; the target is searchable within five seconds of commit, with source order preserved per product and no global order across products. Events carry the key, insert/update/delete, LSN, transaction ID, schema version, and before/after values.

I would use log-based CDC. At snapshot start I record P0 and continue consuming after P0. Snapshot READs can collide with stream UPDATEs, so a snapshot window or equivalent key-version rule must discard the stale READ; simply starting the stream after the snapshot leaves a gap. Consumers partition by key and use an external version or conditional write. Deletes retain a tombstone version so a late update cannot resurrect a document.

The checkpoint advances only after the index side effect succeeds. Crashes therefore create at-least-once replay, which the target must tolerate idempotently. While a connector is down I monitor WAL retained by the slot; after recovery it resumes from the last safe position. For a rebuild I write the same stream to a new index, wait for catch-up, and atomically switch the alias.

Acceptance is more than an empty queue. I inject updates during the snapshot, duplicates and reordering, a late delete, consumer crashes, schema changes, and primary failover. Then I compare database version, index version, and checkpoint by key. The key signals are oldest-event age, WAL retention, index lag, dead letters, and inconsistent samples; a gap must be replayable from a saved log position.”

Common Mistakes

• Polling an update timestamp as CDC → clock precision, clock movement, and long transactions can hide changes → read the commit log or use a provable cursor.
• Starting snapshot and stream independently → an old snapshot row can overwrite a new event → record P0 and resolve READ/UPDATE collisions.
• Advancing the checkpoint immediately after sending an index request → a crash window loses data → advance only after a verifiable side effect.
• Treating at-least-once as exactly-once → duplicates still occur → use conditional version writes and idempotent deletes.
• Ordering by message arrival → retries reorder the network → partition by key and use the source LSN or version.
• Deleting from the index without a versioned tombstone → a late update resurrects the document → retain delete version metadata.
• Sharing one replication slot among independent consumers → one consumer can consume changes that others never receive → use one slot per consumer or an explicit broadcast layer.
• Clearing the live index for a rebuild → a failed replay creates a large search outage → catch up a new index and switch the alias atomically.
• Using an empty queue as proof → skipped events or failed writes can leave it empty → reconcile positions, versions, and primary samples.

Follow-Up Questions and Responses

Follow-up 1: Can an index expose a transaction that updates both a product and its inventory one row at a time?

Yes, if the business accepts an intermediate search state. If results must reflect both together, carry the transaction boundary and aggregate before updating the document, or build one committed searchable projection. “Almost simultaneous” writes across indexes are not atomic.

Follow-up 2: A long outage makes the replication slot retain too much WAL. How do you contain it?

Protect the primary first: alert, limit further writes or degrade the consumer, and verify that the slot still has a usable starting position. If the slot is invalidated, do not create a new slot and assume continuity; missing LSNs may already be lost. Rebuild from a backup or full snapshot and reconcile the gap.

Follow-up 3: The stream is ordered only within partitions. How do you rank search results across products?

Search reads the index’s current versions and should not assume a global event order. If a ranking field needs a consistent time, use source commit time plus a version rule, or have an aggregator produce a stable rank key with an explicit temporary-skew budget. A global order across partitions costs throughput and should be justified by the product invariant.

Follow-up 4: What happens to the old index when a schema field is removed?

First deploy consumers that can read both event versions, then stop producing the old field, confirm backlog and replay windows are clear, and finally migrate the mapping or rebuild. If the field changes authorization or analytical meaning, deleting a JSON property is insufficient; retain versioned events and a rollback path.