Design a search indexing pipeline (CDC to Elasticsearch)

The obvious design is for the catalogue service to write to Postgres and then call Elasticsearch. It fails in two ways. If the second call fails, or the process dies between the two, the index silently misses the change. And two concurrent requests can write Postgres in order A then B but reach Elasticsearch as B then A, leaving a stale price forever. Every team that writes to the database directly (bulk scripts, admin tools, other services) must also remember to update search. CDC fixes all three: the write-ahead log already contains every committed change, in commit order, regardless of who wrote it. Debezium connects to a logical replication slot (pgoutput plugin), turns WAL records into row-level events with before and after images and the LSN, and publishes them to Kafka keyed by primary key. The application is unaware of search. The cost is operational: Kafka Connect to run, replication slots to monitor, and schema changes in the source that flow into the pipeline. An outbox table is a good alternative when you want curated domain events rather than raw row changes.

Correctness rests on three rules. First, partition by product id, so all changes for one product go to one Kafka partition and one indexer thread, in commit order. Second, make every write idempotent and version-checked: index with version_type=external and version = the source LSN (monotonic across the whole database). Elasticsearch rejects any write whose version is not higher than the stored one, so replays after a crash, overlap between backfill and live changes, or a delayed retry can never put an older state back. Treat a version conflict response as success. Third, commit Kafka offsets only after the bulk request succeeds for those items. Bulk responses are per item: a 200 for the batch can still contain failures, so inspect every item, retry 429 (queue full) with backoff, and send mapping errors (400) to a dead-letter topic rather than blocking the partition. Deletes must also carry a version. Elasticsearch keeps deleted-document versions only for a limited time (index.gc_deletes, 60 s by default), so for long delays a soft-delete flag in the document is safer than a hard delete.

Search wants one flat document per product, but the database has product, price, inventory per warehouse, seller and category in separate tables. When a seller's rating changes, thousands of product documents must change. Options: (a) on each change, query Postgres for the full joined row and index it. Simple and always fresh, but at 40k changes/s it hammers the database, so point it at a read replica and batch lookups. (b) A stream processor (Kafka Streams or Flink) keeps the latest row of each table in local state and joins changes as they arrive, re-emitting the full document; no database load, but the state must be rebuilt on deploy and repartitioning. (c) Partial updates: a price change only updates price fields. For a 1-to-many fan-out (seller rating to 50k products), use a separate path: look up the product ids for that seller and enqueue partial updates at a capped rate, or store seller rating in a separate index and join at query time. Reduce stock to a boolean in_stock per region; indexing every quantity change is wasteful.

A logical replication slot makes Postgres keep WAL until the consumer confirms it. If Debezium stops for a weekend, WAL piles up and can fill the primary's disk, taking the catalogue down for a search problem. Set max_slot_wal_keep_size (Postgres 13+) so the slot is invalidated before the disk fills, alert when slot lag passes a few GB, and accept that an invalidated slot means a resnapshot. Slots also live on the primary; after a failover, older Postgres versions lose them (Postgres 17 adds failover slots). Backfills are the other trap: dumping the table and then starting the stream loses changes made during the dump. Either record the LSN before the snapshot and replay from it, relying on versions to resolve overlap, or use Debezium incremental snapshots, which interleave chunked table reads with the live stream using watermarks (the DBLog approach from Netflix). Finally, run a drift checker: sample product ids nightly (or compare per-category counts and checksums) between Postgres and Elasticsearch, and re-index mismatches.

Raw CDC captures everything with no application changes, but couples the index to table structure. An outbox lets the service publish clean 'ProductChanged' events with a stable contract. Large teams often use CDC on the outbox table to get both.

A 1 s refresh_interval gives near-real-time search but creates many small segments and merge load. During a sale price push, a 5 s refresh roughly halves indexing cost. Price shown on the product page should come from the price service anyway, so search can be a little behind.

Full documents are simpler to reason about and self-heal any drift for that product. Partial updates are cheaper on the source but leave stale fields if one path is missed. A good default: partial updates for price/stock, full rebuild of the document for everything else.

Debezium can write to Elasticsearch through a sink connector with little code. A custom indexer costs more to build but handles enrichment, versioning, dead letters and multiple target indexes properly. Kafka also lets other consumers (cache invalidation, recommendations) reuse the same change stream.

20M updates in 10 minutes. Increase bulk size, lengthen refresh_interval temporarily, coalesce multiple changes for the same product inside a batch (keep only the latest), and pre-scale indexing nodes before the sale.

A category rename touches 5M documents. Run it as a throttled background job with its own consumer group so live price updates are not queued behind it.

A Postgres connector reads one slot serially. Split tables across multiple publications and connectors if one cannot keep up, and keep large blob columns out of the publication.

Indexer gets 429s or timeouts; it backs off and stops committing offsets. Kafka holds the changes (7-day retention), and the indexer catches up when the cluster recovers. Search serves slightly stale results.

No events flow; slot lag grows and alerts fire. On restart it resumes from the slot position with no loss. If the slot was invalidated, run an incremental snapshot to resync.

Documents start failing with mapping errors and go to the dead-letter topic. Roll back the indexer or create a fixed index version, then replay dead letters.

With failover slots (Postgres 17 or managed equivalents) the slot continues on the new primary. Without them, recreate the slot and run an incremental snapshot, relying on versions to absorb overlap.