Your marketplace catalog service updates product prices and inventory in PostgreSQL and immediately calls Elasticsearch's REST `_doc` API in the same request handler. Over 6 months, 3.2% of 15M SKUs have drifted: sold-out items still appear in search results, and flash-sale price drops show stale prices for hours. Root causes include: (1) PostgreSQL transaction rollbacks after ES already succeeded, (2) ES 429/timeout failures after PG committed, and (3) concurrent updates arriving at ES out of order. Redesign the sync pipeline for eventual consistency with zero permanent drift.
From patterns to decisions.
Your marketplace catalog service updates product prices and inventory in PostgreSQL and immediately calls Elasticsearch's REST `_doc` API in the same request handler. Over 6 months, 3.2% of 15M SKUs have drifted: sold-out items still appear in search results, and flash-sale price drops show stale prices for hours. Root causes include: (1) PostgreSQL transaction rollbacks after ES already succeeded, (2) ES 429/timeout failures after PG committed, and (3) concurrent updates arriving at ES out of order. Redesign the sync pipeline for eventual consistency with zero permanent drift.
App-level dual writes fail due.
App-level dual writes fail due to DB rollbacks after ES writes, ES failures after DB commits, and concurrent requests arriving at ES out of order.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | 2PC (XA transactions) is not supported by Elasticsearch and kills throughput. |
Step 1
Trace @Transactional methods calling esClient directly.
Step 2
Check how concurrent price updates to the same sku_id are ordered in ES.
Alternative 1
Eventual consistency via Transactional Outbox + CDC.
Failure Mode 1
Holding DB connections open while waiting for ES HTTP responses exhausts HikariCP.
ALTER TABLE skus ADD COLUMN version BIGINT NOT NULL DEFAULT 1;
Implementation & Guardrails (2).
Combine single-transaction outbox writes, sku_id-keyed Kafka partitions, and ES version_type=external.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Adds ~300-800ms eventual consistency lag vs. synchronous writes, which is well within the 2s SLA. |
Step 1
Increment skus.version and insert into catalog_outbox in one ACID transaction.
Step 2
Stream catalog_outbox via Debezium pgoutput into Kafka partitioned by sku_id.
Step 3
Bulk-index into ES with version_type=external and treat 409 as idempotent.
Alternative 1
Direct CDC on skus table (avoids outbox table, but couples search schema to internal DB columns).
Failure Mode 1
Unbounded catalog_outbox table growth if daily partitions aren't dropped.
connector.class: io.debezium.connector.postgresql.PostgresConnector
plugin.name: pgoutput
table.include.list: public.catalog_outbox
transforms: outbox
transforms.outbox.type: io.debezium.transforms.outbox.EventRouter
transforms.outbox.table.field.event.key: sku_id
Kafka guarantees order within a.
Kafka guarantees order within a single partition, but does not prevent a parallel backfill job or DLQ replay from overwriting newer live CDC updates.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Requires storing/incrementing a monotonic `version` column on `skus`. |
Step 1
Pass row.version in both live CDC events and backfill batches.
Step 2
Handle 409 Conflict in _bulk responses as a safe skip.
Alternative 1
Using PostgreSQL LSN (pg_current_wal_lsn()) as the external version.
Failure Mode 1
ES version max is 2^63 - 1; never pass nanosecond timestamps if a bug could overflow.
for item in bulk_resp["items"]:
res = item.get("index") or item.get("delete")
if res["status"] == 409:
continue # Newer version already in ES
if res["status"] >= 400:
send_to_dlq(res)
Implementation & Guardrails (4).
Stream keyset-paginated batches (WHERE sku_id > last_id ORDER BY sku_id LIMIT 5000) from a PostgreSQL read replica, throttled to 8,000 docs/sec with version_type=external.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Keyset pagination runs in O(log N) per batch vs. O(N) degradation with `OFFSET`. |
Step 1
Query a read replica using keyset pagination (never OFFSET).
Step 2
Throttle _bulk writes to 8,000 docs/sec so live CDC keeps 2,000/s headroom.
Step 3
Rely on version_type=external so any SKU updated live during the 31-minute backfill isn't clobbered.
Alternative 1
Debezium incremental snapshot (signal table) to stream backfill through the exact same Kafka topic.
Failure Mode 1
Replica lag causing the backfill to read a 2-second-old version—safely ignored by ES version_type=external.
SELECT sku_id, price_cents, stock, version
FROM skus
WHERE sku_id > :last_sku_id
ORDER BY sku_id ASC
LIMIT 5000;
Emit an outbox DELETE event.
Emit an outbox DELETE event with version + 1 in the same transaction as DELETE FROM skus, and use ES _bulk delete with version_type=external.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Soft-deletes (`deleted_at IS NOT NULL`) are even safer for version tracking than hard deletes. |
Step 1
Capture op_type = 'DELETE' and version = old.version + 1 in catalog_outbox.
Step 2
Execute ES delete action; ES retains a tombstone version briefly to reject late-arriving UPSERTs.
Alternative 1
Soft-delete in PostgreSQL + ES document status: deleted filter.
Failure Mode 1
ES index.gc_deletes (default 60s) purges delete tombstones after 60s, so backfills shouldn't resurrect hard-deleted rows.
WITH del AS (DELETE FROM skus WHERE sku_id = 10421 RETURNING sku_id, version)
INSERT INTO catalog_outbox (sku_id, version, op_type, payload)
SELECT sku_id, version + 1, 'DELETE', '{}'::jsonb FROM del;
Theory & Tips and Tricks
Practical heuristics & interview traps
- 01Interview anchor: Eliminate application dual writes by committing domain changes and an
outboxrow in a single ACID PostgreSQL transaction, then streaming ordered updates to Elasticsearch via Debezium CDC with versioned optimistic concurrency. - 02Trade-off tip: Requires storing/incrementing a monotonic
versioncolumn onskus. - 03Watch out for: Replica lag causing the backfill to read a 2-second-old version—safely ignored by ES
version_type=external.
Eliminating Dual Write Db Search Drift — Normal → Load / Fault → Recovery
Eliminate application dual writes by committing domain changes and an outbox row in a single ACID PostgreSQL transaction, then streaming ordered updates to Elasticsearch via Debezium CDC with versioned optimistic concurrency.
Putting Eliminating Dual-Write Drift Between DB & Search Together
Eliminate application dual writes by committing domain changes and an `outbox` row in a single ACID PostgreSQL transaction, then streaming ordered updates to Elasticsearch via Debezium CDC with versioned optimistic concurrency.
Putting Eliminating Dual-Write Drift Between DB & Search Together
- Increment
skus.versionand insert intocatalog_outboxin one ACID transaction. - Stream
catalog_outboxvia Debeziumpgoutputinto Kafka partitioned bysku_id. - Bulk-index into ES with
version_type=externaland treat409as idempotent.
Interview Readiness Check
Stream keyset-paginated batches (WHERE sku_id > last_id ORDER BY sku_id LIMIT 5000) from a PostgreSQL read replica, throttled to 8,000 docs/sec with version_type=external.
Walk through bottlenecks, back-of-the-envelope math, and failure recovery.
Make the trade-off yourself.
Practice Check
Test your understanding
Why store events in an outbox table?
Eliminating Dual-Write Drift Between DB & Search
Eliminate application dual writes by committing domain changes and an outbox row in a single ACID PostgreSQL transaction, then streaming ordered updates to Elasticsearch via Debezium CDC with versioned optimistic concurrency.
