Dual-write
Writing the same change to two stores, e.g. Postgres and a vector index. Done naively it drifts; the outbox pattern plus CDC makes it reliable.
A dual-write happens whenever one change must land in two places: the main database and the search index, or the old index and the new one during a migration. The naive way, write to A then write to B in application code, breaks when the process crashes between the two writes, leaving them out of sync. The reliable way is to write once to the database (plus an 'outbox' row in the same transaction) and let a separate process copy changes to the second store using change data capture.
Two ledgers kept by one clerk who writes in the first, then walks to the second. If the clerk is interrupted on the walk, the ledgers disagree and nobody knows which is right.
Assuming 'write A, then write B, retry on failure' is good enough. It works in every test and drifts in production on the first deploy that kills pods mid-request.
Application-level dual writes have no atomicity across stores: write A succeeds, write B times out, and you have drift with no record of it. Retrying B in-process helps until the process dies. The transactional outbox fixes this: in one Postgres transaction, update the domain row and insert an outbox(event_id, aggregate_id, payload) row. A relay (Debezium reading the WAL via logical replication, or a poller) publishes outbox events to Kafka, and an idempotent consumer upserts into the vector store. Ordering matters for updates and deletes: partition the topic by document id so a later Tombstone can't overtake the earlier upsert. Even with outbox+CDC, run Reconciliation job because bugs in consumers and manual fixes still cause drift. During migrations, dual-write to old and new index from the same consumer, and backfill historical data separately with a version check so the backfill never overwrites a newer CDC write.
Postgres transactional outbox; Debezium Postgres connector (pgoutput) → Kafka topic keyed by entity id; idempotent upsert consumer with version check (WHERE incoming_version > stored_version); nightly reconciliation by content hash; outbox rows deleted after 7 days.
A clinical-intake platform stores notes in Postgres and serves semantic search from pgvector in a separate cluster. Each note save writes notes and outbox in one transaction. Debezium streams the outbox to Kafka (intake.notes, 12 partitions keyed by note_id); the embedder consumer chunks, embeds and upserts with id note_id|chunk_no|v3. A deleted note emits a tombstone on the same partition. Reconciliation at 02:00 compares 3.1M note hashes against the index and finds 14 drifted, all from a consumer bug fixed that week.
My default is: never write two stores from request code. Write the source of truth plus an outbox row in one transaction, relay with Debezium to Kafka, idempotent consumer into the index, reconciliation nightly. The follow-up: 'what's the consistency lag?' Seconds typically, so the UI should not promise read-your-writes from search; show the saved record from the database instead.
Naive dual-write
- Two network writes in request
- Crash between = silent drift
- No ordering between updates
- No replay
Outbox + CDC
- One local transaction
- WAL is the durable record
- Partitioned by doc id = ordered
- Replay from Kafka offset
Numbers worth knowing
- Typical CDC end-to-end lag
- 1–5 sDebezium + Kafka at moderate load, excluding embedding time
- Embedding added to lag
- 50–300 ms per batchhosted embedding API, batches of 64 chunks
- Naive dual-write drift
- non-zero on every crash between writesproportional to deploy frequency and timeout rate
How to decide
| When | Do this |
|---|---|
| Second store is derived (index, cache, search) | Outbox + CDC; never write it from request code. |
| Migrating old → new index | Consumer writes both; backfill history with version guards. |
| You can't add an outbox (legacy DB) | Log-based CDC directly on the source tables, plus reconciliation. |
| UI needs read-your-writes | Read the record from the source DB; don't wait on the index. |
Cost maths
Kafka + Debezium overhead for 2M changes/day is small: a managed Kafka (e.g. 3-broker MSK or Confluent basic) ~$300–600/month. Embedding the changes: 2M × 300 tokens = 600M tokens/day; at $0.02/M (illustrative Oct 2026) ≈ $12/day. The alternative cost is a full re-index to repair drift: 50M chunks × 300 tokens at $0.02/M ≈ $300 plus a day of compute, each time drift is noticed.
Operating it
- Reconciliation finds drift >0.01% repeatedly: fix consumers before adding stores
- CDC lag p95 >60 s: add partitions and consumers
- A third derived store appears: standardise on the event stream rather than adding another writer
- CDC lag (source commit → index upsert): alert p95 >60 s
- Outbox rows unpublished older than 5 min: alert >0
- Reconciliation drift: alert >0.01% of records
- Consumer error rate into DLQ: alert >0.1%
When it goes wrong
Deleted customer-support tickets kept appearing as cited sources in an assistant's answers, including for a customer who had requested erasure.
A GDPR erasure audit sampled deleted ticket ids and found 2.3% still retrievable.
The API deleted from Postgres, then called the vector DB delete; when the second call timed out, the error was logged and swallowed.
Outbox row in the delete transaction, tombstone via Debezium, idempotent consumer delete, and a daily erasure reconciliation report.
Related terms
Sources
- Pattern: Transactional outbox — Chris Richardson, microservices.io, 2018docs
- Reliable Microservices Data Exchange With the Outbox Pattern — Gunnar Morling, Debezium, 2019blog
- Designing Data-Intensive Applications — Martin Kleppmann, 2017book
Check yourself
Three questions. Every option is explained once you answer.