Step 1 · The pattern
Two copies of the same data
Sellers save listings. The app commits each one to the database, the source of truth, then writes the same listing to the search index, which is shaped for fast text search. On a good day both writes succeed and search matches the database. (All numbers are illustrative.)
- save
- in both
In short
Keeping search in sync: how it works and how it breaks
Search engines hold a copy of your data shaped for searching; the database stays the source of truth. The tempting way to keep them in step is a dual write: the app saves to the database, then writes the same change to search. Any failure between the two leaves them disagreeing. The row is committed, the search write times out, and nothing ever retries it, so the product is saved but never appears in search, or a deleted item keeps showing up. Retrying in the app only helps until the app itself crashes between the two writes. Instead, write only to the database and feed the index from the database's change log (change data capture): every committed change becomes an event, and an indexer applies it in order, retrying until it lands. Search lags by moments but converges, and you can rebuild it from the log.
What this simulation simplifies
- One search write in four fails, at random; real failures cluster during search cluster overload, deploys or network problems.
- In the trap the failed listing is simply never indexed; real apps sometimes have repair jobs that find some of the drift later.
- The change stream is drawn as one box; in practice it is a connector reading the log, a topic such as Kafka, and an indexer service.
How this comes up in interviews
"Design product search for a marketplace whose listings live in PostgreSQL. How do you keep the search index up to date?"
Then they ask
- Why not have the API write to both PostgreSQL and the search engine?
- How fresh is search after a seller edits a listing?
- The index is corrupted by a bad deploy. How do you recover?
A strong answer covers
- Keep the database as the source of truth; search is a derived copy.
- Avoid dual writes: they drift on any failure between the two writes.
- Use change data capture (or an outbox) to emit every committed change, and an idempotent indexer that retries.
- Expect a short lag: indexing plus the search engine's refresh; read your own writes from the database.
- Support full rebuilds from a snapshot plus the change stream, switching an alias when ready.
Points to cover, not a script. Say them in your own words and tie them to a system you have worked on.
On one page: the track cheat sheet.
Sources: Debezium documentation: PostgreSQL connector ↗ · Elasticsearch documentation: Near real-time search ↗ · Report a problem
Diagrams are our own simulations: the timings, rates and counts in them are illustrative, not measurements. Captions are original wording.