In a service that saves each product to a relational database and also to a search index, why are dual writes from request code unsafe?

seniorshould knowfreq 56%

answer

two commits, no shared atomicity
crash between the writes
updates can arrive out of order
same local transaction as the change
idempotent consumer with version check

basics

~20 s

The two writes are not atomic: a crash or failure between them leaves the stores diverged, and concurrent updates can reach the index out of order. Write only to the database and propagate changes asynchronously, via an outbox or change log.

solid answer

~50 s

A dual write means the request handler writes to the database and then to the search index as two separate operations. Nothing makes them atomic, so a crash, timeout or index outage after the database commit leaves the index stale, and writing the index first can leave it showing data the database rolled back. Two concurrent updates can also reach the index in the opposite order from the database commits, so the index keeps the older version indefinitely. The robust pattern is a single source of truth: commit the change, and in the same local transaction write an outbox row, or read the database's change log; a relay then delivers changes to an idempotent consumer that applies them only if their version is newer. Add periodic reconciliation or a full reindex, and accept that search results lag briefly behind writes.

A **dual write** means the request handler writes to the database and then to the search index as two separate operations. Nothing makes them atomic, so a crash, timeout or index outage after the database commit leaves the index stale, and writing the index first can leave it showing data the database rolled back. Two concurrent updates can also reach the index in the opposite order from the database commits, so the index keeps the older version indefinitely. The robust pattern is a **single source of truth**: commit the change, and in the same local transaction write an **outbox** row, or read the database's change log; a relay then delivers changes to an idempotent consumer that applies them only if their **version** is newer. Add periodic **reconciliation** or a full reindex, and accept that search results lag briefly behind writes.

code

pseudocode · 9 lines

pseudocode

onProductChanged(productId):
  row = db.findById(productId)
  if row is null:
    searchIndex.delete(productId)
    return
  indexed = searchIndex.getVersion(productId)
  if indexed is not null and indexed >= row.version:
    return   // duplicate or stale event, nothing to do
  searchIndex.upsert(productId, toDocument(row), row.version)

go deeper

for a junior

Recall that writing the same data to two stores in two separate steps can leave them disagreeing if anything fails between the steps.

for a middle

Explain the failure cases, partial failure and out-of-order updates, and how an outbox row written in the same transaction fixes the capture side.

for a senior

Show how you make the consumer idempotent and version-aware, monitor the pipeline, and keep a reconciliation or rebuild path for drift.

for a principal

Treat the synchronisation pipeline as part of the cost of adding a store, and decide which stores are derived and rebuildable versus authoritative.

Why polyglot persistence creates a synchronisation problem

Once a product uses more than one store, the same fact often lives in two places. A classic case is a product catalog held in a relational database as the system of record, with a search engine's index as a derived store for relevance-ranked search. Whenever a product changes, both must eventually agree. The tempting implementation is the dual write:

pseudocode

handleUpdate(product):
  db.update(product)        // commit 1
  searchIndex.upsert(product) // separate call, separate failure

It works in testing and fails quietly in production.

How dual writes go wrong

Partial failure. The database commit succeeds, then the process crashes, the index times out or the index is down. The index now shows the old price, with nothing recording that it is wrong.
Reverse partial failure. If you write the index first, a later database failure or rollback leaves the index showing a product state that never became true.
Ordering races. Request A sets price to 10 and request B sets it to 12. The database commits A then B, but B's index call happens to finish first and A's arrives later, so the index ends at 10 while the database says 12.
Retry side effects. Retrying a failed index write from the request path adds latency and can still interleave with newer updates.
Coupled availability. An index outage now fails or slows product updates, even though search is not the source of truth.

A distributed transaction spanning both systems is rarely an option, because many derived stores do not participate in such protocols and the coordination cost is high.

The robust pattern: one writer, asynchronous propagation

The fix is to write only to the source of truth in the request path and let a separate pipeline carry changes onward.

Approach	How changes are captured	Main trade-off
Transactional outbox	Handler inserts an outbox row in the same local transaction as the change; a relay publishes it	Extra table and relay process; delivery is at-least-once
Change data capture	A reader tails the database's own change log	No application changes, but depends on log access and schema awareness
Periodic reconciliation	A job compares or rebuilds the index from the database	Simple safety net; slow to converge on its own

The outbox write is atomic with the business change because it is the same local transaction:

sql

BEGIN;
UPDATE products SET price = 1200, version = version + 1 WHERE id = 42;
INSERT INTO outbox (aggregate_id, event_type, created_at)
VALUES (42, 'ProductChanged', CURRENT_TIMESTAMP);
COMMIT;

Either both rows commit or neither does, so a change can never be lost between the two stores.

Making the consumer safe

The relay delivers at least once, so the consumer must tolerate duplicates and reordering:

Idempotent upserts: applying the same change twice leaves the same result.
Version checks: each change carries a monotonically increasing row version, and the consumer skips a change older than what the index already holds.
Read current state: many consumers re-read the latest row by ID instead of trusting the event payload, which naturally converges on the newest state.
Per-key ordering: route all changes for one product to the same consumer partition so they are processed in sequence.
Deletes as tombstones: a missing row means remove the document, not ignore the event.

What you accept in return

Replication lag: search results trail writes by the pipeline's delay, so a user who edits a product may not find the change in search immediately.
Operational work: the relay, its backlog and its failures must be monitored; a growing outbox is an early warning.
Rebuild capability: because the index is derived, you keep a tested way to rebuild it from the database, which also covers mapping changes and corruption.

This is the real cost of polyglot persistence. Choosing a second store is not just choosing its data model; it is committing to a pipeline that keeps it honest.

Interview framing

Name the failure first (two non-atomic writes plus ordering races), then the capture fix (outbox or change log), then the consumer rules (idempotent, versioned, per-key ordered), and finish with the safety net (reconciliation and a tested rebuild). That order shows you understand both halves of the problem.

## Why polyglot persistence creates a synchronisation problem Once a product uses more than one store, the same fact often lives in two places. A classic case is a **product catalog** held in a relational database as the **system of record**, with a **search engine's index** as a **derived store** for relevance-ranked search. Whenever a product changes, both must eventually agree. The tempting implementation is the **dual write**: ```pseudocode handleUpdate(product): db.update(product) // commit 1 searchIndex.upsert(product) // separate call, separate failure ``` It works in testing and fails quietly in production. ## How dual writes go wrong 1. **Partial failure.** The database commit succeeds, then the process crashes, the index times out or the index is down. The index now shows the old price, with nothing recording that it is wrong. 2. **Reverse partial failure.** If you write the index first, a later database failure or rollback leaves the index showing a product state that never became true. 3. **Ordering races.** Request A sets price to 10 and request B sets it to 12. The database commits A then B, but B's index call happens to finish first and A's arrives later, so the index ends at 10 while the database says 12. 4. **Retry side effects.** Retrying a failed index write from the request path adds latency and can still interleave with newer updates. 5. **Coupled availability.** An index outage now fails or slows product updates, even though search is not the source of truth. A distributed transaction spanning both systems is rarely an option, because many derived stores do not participate in such protocols and the coordination cost is high. ## The robust pattern: one writer, asynchronous propagation The fix is to write only to the source of truth in the request path and let a separate pipeline carry changes onward. | Approach | How changes are captured | Main trade-off | |---|---|---| | **Transactional outbox** | Handler inserts an outbox row in the same local transaction as the change; a relay publishes it | Extra table and relay process; delivery is at-least-once | | **Change data capture** | A reader tails the database's own change log | No application changes, but depends on log access and schema awareness | | **Periodic reconciliation** | A job compares or rebuilds the index from the database | Simple safety net; slow to converge on its own | The outbox write is atomic with the business change because it is the same local transaction: ```sql BEGIN; UPDATE products SET price = 1200, version = version + 1 WHERE id = 42; INSERT INTO outbox (aggregate_id, event_type, created_at) VALUES (42, 'ProductChanged', CURRENT_TIMESTAMP); COMMIT; ``` Either both rows commit or neither does, so a change can never be lost between the two stores. ## Making the consumer safe The relay delivers **at least once**, so the consumer must tolerate duplicates and reordering: - **Idempotent upserts**: applying the same change twice leaves the same result. - **Version checks**: each change carries a monotonically increasing row version, and the consumer skips a change older than what the index already holds. - **Read current state**: many consumers re-read the latest row by ID instead of trusting the event payload, which naturally converges on the newest state. - **Per-key ordering**: route all changes for one product to the same consumer partition so they are processed in sequence. - **Deletes as tombstones**: a missing row means remove the document, not ignore the event. ## What you accept in return - **Replication lag**: search results trail writes by the pipeline's delay, so a user who edits a product may not find the change in search immediately. - **Operational work**: the relay, its backlog and its failures must be monitored; a growing outbox is an early warning. - **Rebuild capability**: because the index is derived, you keep a tested way to rebuild it from the database, which also covers mapping changes and corruption. This is the real cost of polyglot persistence. Choosing a second store is not just choosing its data model; it is committing to a pipeline that keeps it honest. ## Interview framing Name the failure first (two non-atomic writes plus ordering races), then the capture fix (outbox or change log), then the consumer rules (idempotent, versioned, per-key ordered), and finish with the safety net (reconciliation and a tested rebuild). That order shows you understand both halves of the problem.

Why does the consumer re-read the row from the database instead of trusting the event payload?
Events may arrive late, twice or out of order. Re-reading the current row means every event, however stale, results in writing the newest state, so the index converges. Combined with a version check it also avoids overwriting a newer document. The cost is an extra read per event, which is usually acceptable for a derived store.
How would you detect that the search index has silently drifted from the database?
Monitor the outbox backlog and relay error rate, compare document counts and checksums or version numbers per key range between the two stores on a schedule, and sample-read products from both. When drift is found, reindex the affected keys, and keep a tested full rebuild from the database for larger incidents.
Why not simply wrap both writes in a try-catch and roll back the database if the index write fails?
The database transaction may already be committed, and a compensating update can itself fail or race with other writers. It also makes product updates depend on index availability. A crash between the two steps skips the catch block entirely, so the divergence the pattern was meant to prevent still happens.

saying these in an interview costs you the question

Writing to the database and then the index in one handler is atomic enough
If both writes usually succeed, divergence is not worth handling
Writing the index first avoids inconsistency
Outbox delivery happens exactly once, so consumers need no idempotency
A derived search index never needs a rebuild path

In a service that saves each product to a relational database and also to a search index, why are dual writes from request code unsafe?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Why polyglot persistence creates a synchronisation problem

How dual writes go wrong

The robust pattern: one writer, asynchronous propagation

Making the consumer safe

What you accept in return

Interview framing

▾common follow-ups3 questions

▾red flags5 flags

Why polyglot persistence creates a synchronisation problem

How dual writes go wrong

The robust pattern: one writer, asynchronous propagation

Making the consumer safe

What you accept in return

Interview framing

answer

go deeper