EventsGENERALDATABASE-SPECIFICSIMPLIFIED
Keeping a Search Index in Sync
Database change to event to indexer to search engine — and the fact that when it breaks, nothing errors and search is quietly wrong.
The requirement, the obvious build, and why it breaks
Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.
The question
How does a search index stay consistent with the database, and how would I even know that it had not?
The requirement
Users search products by name and description. Results must reflect edits, and deleted or unpublished products must not appear.
The obvious build
After saving the product, call the search engine to index it. Two lines in the same handler, right after the database write.
Why it breaks
The index call is inside the transaction, so a slow search cluster holds a database connection and a lock for the duration of a network call (External Calls Inside a Transaction).
How it breaks in production
- The index call is inside the transaction, so a slow search cluster holds a database connection and a lock for the duration of a network call (External Calls Inside a Transaction).
- The index call is outside the transaction, so a crash between commit and index leaves a product that exists and cannot be found. No error is raised anywhere.
- The index write succeeds and the transaction then rolls back, so search returns a product that does not exist and clicking it 404s.
- A bulk import updates 50,000 rows and issues 50,000 individual index calls, overwhelming the cluster and stalling the import.
- Two edits to the same product race, the older one is indexed last, and the index permanently shows the previous title until someone edits it again.
- A product is deleted; the delete call to the index fails; the product remains searchable forever, including to users who should never see it.
Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale
What is actually happening
- A search index is a derived store. The database is the source of truth and the index is a projection of it, which means the only real question is how the projection is kept current and how divergence is detected.
- The dependable shape is: transaction commits the change and an outbox row together → a relay publishes the change event → an indexer consumes it → the indexer writes to the search engine (The Transactional Outbox).
- Every hop introduces lag, and lag is the thing to measure. "Search is eventually consistent" is only acceptable if you know what "eventually" currently is (Eventual Consistency in Practice).
- Search engines are near-real-time, not real-time: a document that has been accepted is typically not searchable until the engine refreshes its segments. That interval is part of your end-to-end lag and is not something the indexer controls.
- Out-of-order application is the characteristic correctness bug. The fix is a monotonic version per document — the engine or the indexer rejects a write whose version is not newer than the stored one, so a late arrival cannot resurrect stale data.
- This pipeline fails silently by construction. There is no user-facing error when a document is missing from an index. The absence of a result looks exactly like the absence of a matching product, which is why nobody notices until a customer says "I cannot find the thing I just published".
The pipeline, and where the truth can diverge
Every arrow below is a place the index can start disagreeing with the database, and only one of them produces anything resembling an error. That asymmetry is the reason reconciliation is not optional paranoia — it is the only component that can detect what the rest of the pipeline cannot report.
Note that the reconciler reads the source of truth directly. A reconciler that consumes the same events as the indexer verifies nothing, because it shares the failure it is supposed to catch.
UserLLMAgentToolDataDecisionHumanGuardrail
Six ways search goes quietly wrong
Every row here returns HTTP 200. That is the point of the table: the entire class of failure is invisible to error-rate dashboards, and the responses column is what you have to build in advance because none of it can be added mid-incident.
Silent divergence between database and index
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| Indexer stopped two hours ago | Search works. Results are two hours old. Nothing alerts. | No progress metric; error rate is zero because nothing is running. | Alert on oldest-unprocessed-event age and on a synthetic write-then-search probe. |
| Delete event lost | A removed or unpublished product is still findable, possibly by users who should never see it. | Delete treated as a best-effort side call rather than as an event. | Deletes travel the same outbox path as updates; reconciliation checks for documents with no source row. |
| Two edits processed out of order | Search shows the previous title permanently. | Last-write-wins on an unordered stream. | Monotonic document version applied as an external version; the engine rejects the older write. |
| Direct SQL migration updates 200k rows | Those rows are stale in search forever. | The application outbox only sees application writes. | Emit events from the migration, or run a targeted reconciliation immediately after it. |
| Mapping changed, no reindex | Old documents match differently from new ones; results depend on document age. | Analysis is applied at index time, not query time. | Reindex into a new index and swap the alias — a rehearsed path, not an improvised one. |
| Reindex overlaps live writes | Recently-edited products revert to their pre-edit content. | The reindexer read the row before the edit and wrote after it. | External versioning again: the reindexer's stale write loses to the live one. |
| Permission revoked for a user | The document still matches their queries. | Permissions denormalised into the document and never refreshed. | Treat permission changes as index events, or filter by permission at query time and accept the cost. |
Indexing that cannot go backwards
SIMPLIFIEDAssumes the engine supports an externally-supplied version with conflict rejection. Where it does not, the equivalent is a compare-and-set in a small version table owned by the indexer, checked before the index write — same invariant, one more store to keep consistent.
The version check is the smallest change that removes an entire class of bug. Without it, correctness depends on the order the queue happened to deliver in; with it, correctness is a property of each individual write.
The second thing to notice is that a version conflict is not an error. It is the mechanism working: a stale write was correctly refused. Counting conflicts is useful; alerting on them as failures will train the team to ignore the alert.
Applying a product update to the index
Last write wins
await index.put({
id: product.id,
body: toDocument(product),
})
// whichever indexer call lands last wins,
// regardless of which edit was newerVersioned, batched
batch.push({
id: product.id,
body: toDocument(product), // a projection, not the row
externalVersion: product.rowVersion, // monotonic per document
})
// flushed on size or on a time bound:
const res = await index.bulk(batch)
for (const item of res.items) {
if (item.status === VERSION_CONFLICT) {
metrics.increment('index.version_conflict') // expected, not an error
} else if (item.error) {
await deadLetter(item) // real failure: keep the payload
}
}The versioned write makes each document's state a function of the newest event that has arrived, rather than of arrival order — so redelivery, reindexing and out-of-order consumers all become harmless. Batching is what makes the indexer able to keep pace with a bulk import instead of collapsing under it.
How to build it
Most important first.
- Never write to the search engine from the request path. Emit a fact in the transaction and let an indexer consume it (Event-Driven Backends).
- Carry a monotonic document version — an updated-at sequence or a row version — and apply it as an external version on the index write so out-of-order updates are rejected rather than applied.
- Batch. Index in bulk with a size and a time bound, because per-document round trips are the difference between an indexer that keeps up and one that does not.
- Index a projection, not the row. The index needs searchable text plus filter fields, not every column, and shaping it deliberately is what stops schema changes from breaking search (Three Models, Not One).
- Run a reconciliation job on a schedule: walk the source of truth in ranges, compare id and version against the index, and repair or report differences. This is the only mechanism that finds silent loss.
- Build the full reindex path before you need it, using a new index and an alias swap, so a mapping change or a corruption is a routine operation rather than an outage.
- Handle deletes and visibility changes as first-class events. Unpublishing is a delete from the index's perspective, and it is the case most likely to be forgotten (Cache Invalidation is the same problem in a different store).
What can go wrong
Failure modes
- Indexer dead, no alert: search silently freezes at whatever it knew an hour ago. Every query succeeds.
- Reconciliation job that reports differences into a log nobody reads, which is functionally identical to not having one.
- Delete event lost, so a removed product is searchable indefinitely — the worst variant, because it is both a correctness and a disclosure problem.
- A mapping change deployed without a reindex, so old documents keep the old analysis and new ones do not, and results differ by document age.
- Reindex driven from a read replica with lag, so the "authoritative" rebuild bakes in stale rows (Read Replicas From the Application).
- Bulk operations bypassing the application layer — a direct SQL migration that updates rows and emits no events — leaving the index permanently behind for those rows.
- The reconciliation job itself falling behind at scale, so the safety net covers a shrinking fraction of the data.
What can race
- Two updates to one document indexed out of order, so the older version is stored last — prevented by an external version check, not by hoping the queue is ordered.
- A delete and an update for the same document racing, leaving a resurrected document if the update lands after the delete.
- A full reindex running concurrently with live indexing, where the reindex writes a row it read before a live update and overwrites the newer document (Optimistic Concurrency).
Security
- A document that should no longer be visible but is still indexed is an authorization failure with a search box attached. Deleted, unpublished, and access-revoked all mean "remove or re-scope in the index", and all fail silently.
- Filter by tenant and visibility inside the query sent to the engine, not by post-filtering results in the application — post-filtering leaks through result counts, pagination and aggregations even when documents are hidden (Tenant Isolation).
- Permission changes must trigger reindexing of the affected documents when permissions are denormalised into the index. A permission model baked into documents is stale the moment a role changes (Object-Level Authorization).
- Search engines are frequently deployed without authentication on an internal network. An index with everyone's documents and no auth is a full data export waiting for an SSRF (SSRF — When the Backend Fetches a URL).
Misreads
- "Indexing succeeded, so search is correct." The write was accepted. It may not be searchable yet, it may be an older version applied late, and a delete may have been lost months ago.
- "Search is eventually consistent, so staleness is fine." Eventual consistency converges only while the pipeline is alive. A dead consumer means never, and "eventual" is doing a lot of work in that sentence (Eventual Consistency in Practice).
- "We can just reindex if something goes wrong." Only if you know something went wrong, and only if the reindex path has been exercised. Both are usually untrue at the moment you need them.
- "The index is a cache, so a TTL solves it." Search indexes have no per-document expiry semantics and no read-through path — a missing document is simply absent, forever, with no signal (When Not to Cache).
- "Post-filtering search results by permission is equivalent to filtering in the query." It is not: counts, facets and pagination all leak information about documents you removed.
Operating it
How you see it in production
- End-to-end indexing lag: the delta between a row's updated-at and the moment the document is searchable. This is the number users feel.
- Reconciliation output as a metric, not a log: documents missing, documents stale, documents present that should not be. Alert on any sustained non-zero value.
- Document count in the index versus row count in the source, per tenant. Divergence in either direction is a bug.
- A synthetic probe that writes a record, polls search for it, and records how long until it appears — the only check that exercises every hop the way a user does (Health Checks: Startup, Readiness, Liveness).
- Bulk indexing rejection rate and version-conflict rate. Version conflicts are healthy in moderation and a redelivery storm signal in volume.
What changes at 10x and 100x
- At 10x writes, per-document indexing becomes the bottleneck and batching stops being an optimisation and becomes a requirement.
- At 100x, a full reindex takes long enough that it must run against a parallel index with an alias swap, and the reconciliation job must be sharded to keep its coverage window bounded.
- Fan-out grows: one product change may touch many documents if you denormalise categories or permissions, so a single row update becomes hundreds of index writes (Fan-Out: Waiting for the Slowest of Seven is the Performance domain's treatment).
- The index cluster becomes a capacity planning problem of its own — indexing throughput and query throughput compete for the same resources, so a reindex during peak degrades search for users.
What this costs
- Asynchronous indexing means a user can save an edit and not find it immediately. That is usually acceptable and must be a stated bound, not a surprise.
- Batching improves throughput and increases lag by exactly the batch window. You are choosing where on that curve to sit.
- Denormalising permissions into documents makes queries fast and makes permission changes into reindexing work. Filtering at query time is always correct and often slower.
- Reconciliation costs a continuous scan of the source of truth. It is the only defence against silent loss and it is not free.
Where this applies
Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.
- GENERALSource of truth, derived projection, versioned application and reconciliation apply to any secondary store — search engine, analytics warehouse, recommendation store or read model.
- DATABASE-SPECIFICWhere the change events come from differs sharply. An application outbox works anywhere and captures only what the application does; change-data-capture from the database log also captures direct SQL and migrations, at the cost of coupling the pipeline to the engine's replication format and operational quirks.
- SIMPLIFIEDTreats "the search engine" generically. Engines differ in whether they expose external versioning, how refresh intervals interact with read-after-write, and whether aliases exist for atomic swaps — all three change the concrete design.
Where the depth lives
This domain teaches the application-side mechanism and hands the rest off.
Domains that do not exist yet
- — Distributed Systems — keeping two independent stores convergent when neither can see the other's failures.