In a search-indexing pipeline consuming product change events, how do you stop a stale event from overwriting a newer document?

## Why change events arrive out of order An indexing pipeline reads change events for products and applies them to a search index. Even when the database committed the changes in a clear order, the events can reach the index in a different one: - **Retries**: a write that failed is retried after a newer write for the same product has already succeeded. - **Redelivery**: queues usually deliver **at least once**, so an old event can arrive again after an indexer restart. - **Parallel consumers**: two workers handling the same product at the same time finish in either order. - **Backfills**: a bulk reload of old data runs beside the live stream and writes older state on top of newer state. An index write is normally a plain **upsert**, so whichever write lands last wins. Without extra information the index cannot tell a late old write from a new one. ## Defence one: keep each product's events together Partitioning the event stream by **product ID** sends every change for a product to the same partition, and a single consumer processes each partition in order. This removes most races cheaply. It does not remove them all: retries inside a consumer, redelivery after a rebalance, and backfills can still reorder writes. So ordering by key is necessary but not sufficient. ## Defence two: version-guarded writes Each event carries a **version** from the source of truth, and the index stores the version with the document. A write is applied only if its version is greater than the stored one. ```pseudocode on_event(e): doc = (e.op == DELETE) ? TOMBSTONE : e.document // compare and write in one atomic step inside the index result = index.write_if_newer(e.product_id, e.version, doc) if result == REJECTED_STALE: metrics.increment("stale_events_skipped") ack(e) ``` The comparison must be **atomic** in the index. Reading the stored version, deciding in the indexer, and then writing leaves a gap in which another worker can write. Many engines support a conditional write keyed on an external version; where one does not, serialize writes per product. A duplicate event carries a version equal to the stored one and is skipped, which makes the write **idempotent**. ## Where the version comes from | Source | Monotonic per product? | Notes | |---|---|---| | Row version counter incremented on each update | Yes | Simple; must be bumped in the same transaction as the change | | Commit-log position of the change | Yes, across the whole database | Comes for free with log-based change capture | | Application server wall-clock timestamp | No | Clock skew between servers can give a newer change an older timestamp | | Indexer arrival time | No | Records when the event arrived, which is exactly what is unreliable | ## Deletes and tombstones Deletes are the classic trap. If a delete simply removes the document, the stored version disappears with it. A delayed update for that product then finds nothing to compare against and recreates the product, so a delisted item reappears in search. The fix is a **tombstone**: keep a marker with the delete's version for longer than the longest expected delay, reject older writes against it, and purge tombstones later. ## The refetch alternative Some pipelines treat an event only as a **trigger**: the indexer reads the current row from the database and indexes that. This is self-healing, because every read returns the latest committed state. Two cautions remain: 1. Two workers can read the row at different moments and write their results in the wrong order, so the row's version should still guard the write. 2. Every event costs a database read, which matters at high change rates and during bulk updates. ## What to monitor - The count of stale events skipped, which should be small and stable. - A sudden rise in skips, which often points to a replay or backfill running unexpectedly. - Reconciliation mismatches, which show whether the guards are actually holding.