Your search index and your database disagree — some products appear in search that were deleted, and some new ones never appear. How do you make this reliable?

Why it drifts

Almost always a dual write: the application writes to the database and then to the search index as two independent operations. One can succeed and the other fail — a crash, a timeout, a validation difference, a deployment mid-request — and the two diverge silently.

Deletions failing to propagate is the classic asymmetry, because a failed delete leaves a document that no subsequent write will ever correct.

Secondary causes worth ruling out: an indexing job that skipped records on error without alerting, a partial reindex that was never completed, and index-time filters that silently exclude records.

The fix: one write, then propagate

Transactional outbox. Write the business change and an index-intent record in the same database transaction. A relay reads the outbox and updates the index. Now there is one atomic write, and the index update is at-least-once — which is fine, because indexing the same document twice is idempotent by nature.

Or CDC. Tail the database log and drive the index from it. Same guarantee, no application change, and it captures changes made by any writer including migrations and manual corrections — which a dual write in the application layer does not.

Either way the principle is the same: the database is the source of truth and the index is derived, so there is exactly one write and the derivation is replayable.

What you still need

Reconciliation. Even with an outbox, bugs in projection logic, index mapping changes and manual interventions cause drift. A periodic job comparing counts and checksums per segment — by category, by date range — detects it, and the difference should be a monitored metric rather than a discovery.

Full rebuild capability, exercised. The fix for any index-level bug is to rebuild from the source. If that has never been run at production volume, it does not work — and it is needed at exactly the moment nobody wants to be discovering that.

Lag monitoring. Time between a database write and its visibility in search, alerted against a stated tolerance. A stalled indexer is otherwise invisible: searches succeed, results are wrong, nothing errors.

What a strong answer adds

Naming the general principle, which extends well beyond search: any derived store — index, cache, read model, warehouse — must be rebuildable from its source, and the rebuild must be practised. Reliability of a derived store comes from a correct derivation plus a working rebuild, not from attempting to make every incremental update perfect.