Skip to main content
← PROBLEMS / ARCHITECTURE
An end-to-end domain learning journey

Eliminating Dual-Write Drift Between
DB & Search.

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.
Start the journey5 stages
Loading diagram…
Eliminating Dual-Write Drift Between DB & Search architecture sketch diagram
FIRST PRINCIPLES. PRODUCTION SCALE.
The idea to keep with you

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.

01
ARCHITECTURE · STAGE 01L4 · FUNDAMENTALS

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.

ChoiceWhat it buysWhat it costs
Trade-off 12PC (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;
02
ARCHITECTURE · STAGE 02L5 · APPLIED DESIGN

Implementation & Guardrails (2).

Combine single-transaction outbox writes, sku_id-keyed Kafka partitions, and ES version_type=external.

ChoiceWhat it buysWhat it costs
Trade-off 1Adds ~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
03
ARCHITECTURE · STAGE 03L6 · SCALE & TRADE-OFFS

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.

ChoiceWhat it buysWhat it costs
Trade-off 1Requires 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)
04
ARCHITECTURE · STAGE 04L6 · SCALE & TRADE-OFFS

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.

ChoiceWhat it buysWhat it costs
Trade-off 1Keyset 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;
05
ARCHITECTURE · STAGE 05L7 · STAFF / PRINCIPAL

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.

ChoiceWhat it buysWhat it costs
Trade-off 1Soft-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 outbox row 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 version column on skus.
  • 03Watch out for: Replica lag causing the backfill to read a 2-second-old version—safely ignored by ES version_type=external.
Behavioral Progression · Eliminating Dual Write Db Search Drift

Eliminating Dual Write Db Search Drift — Normal → Load / Fault → Recovery

Send Request
1. Normal Request Ingress
Traffic: 1x baseline · Latency (p99): 4 ms
Process State
2. Core Execution & State Lookup
Traffic: 1x baseline · Latency (p99): 9 ms
Inject 10x Spike
3. Scale Spike / Failure Injection
Traffic: 10x burst · Latency (p99): 185 ms

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.

Synthesis / One connected design

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.

A candidate architecture, not a universal answer

Putting Eliminating Dual-Write Drift Between DB & Search Together

Step 1
Step 2
Step 3
  1. Increment skus.version and insert into catalog_outbox in one ACID transaction.
  2. Stream catalog_outbox via Debezium pgoutput into Kafka partitioned by sku_id.
  3. Bulk-index into ES with version_type=external and treat 409 as idempotent.
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`.
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.

Practice / Explain the consequences

Make the trade-off yourself.

Practice Check

Test your understanding

Question 1 of 3

Why store events in an outbox table?

Key takeaway

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.