A product catalogue lives in PostgreSQL, and users want to type “wireless mouse” and get relevant results in 50 milliseconds, with typo tolerance, filters and ranking. SQL LIKE '%wireless%' can’t do that well at scale, so you add a search engine such as Elasticsearch or OpenSearch. Now the same product exists in two places, and the design question changes from “how do we search?” to “how do we keep the second copy honest?”
The rule that keeps this manageable: the database is the source of truth, and the search index is a derived view of it. You should be able to delete the whole index and rebuild it from the database. Everything else in the design follows from that.
What an inverted index stores
A search engine tokenises each document’s text and builds an inverted index: for every term, the list of documents containing it. Searching “wireless mouse” means looking up both terms’ lists and intersecting them, then ranking the matches by relevance (BM25 by default in Elasticsearch). Analysers decide what counts as a term: lowercasing, stemming “mice” to “mouse”, splitting on punctuation.
That structure is great for retrieval and poor at the things databases are for. There are no multi-document transactions, no foreign keys, and writes aren’t searchable immediately: Elasticsearch makes new documents visible on a periodic refresh, every second by default, which is why it’s described as near-real-time. Treat the index as a fast, approximate, rebuildable projection, not a place to store anything you can’t regenerate.
Getting changes from the database to the index
The first design most people sketch is a dual write: the product service updates PostgreSQL, then calls Elasticsearch. It breaks in the familiar ways. If the process crashes between the two calls, the index never learns about the change. If the Elasticsearch call fails and is retried later, it might apply after a newer change. If an admin script updates the table directly, the index never hears about it at all.
The reliable shape is to derive index updates from the database’s own record of changes:
- Change data capture (CDC). A connector like Debezium reads PostgreSQL’s write-ahead log and publishes every committed row change to Kafka. It sees every write, including scripts and migrations.
- Or a transactional outbox. The service writes an event row in the same transaction as the product change, and a relay publishes it (see the outbox pattern).
- An indexer consumes the topic, builds the search document (often joining a few tables), and writes it to the index.
Partition the topic by product id, so all changes to one product are processed in order by one consumer. That helps a lot, but it doesn’t guarantee order at the index: consumer restarts replay events, backfill jobs run alongside the live stream, and retries can deliver an old event late.
Late events and resurrected deletes
To see why order matters, here’s a tiny inverted index in Node, fed a realistic mix of events. Product p1 is renamed twice, and the two renames arrive in the wrong order. Product p3 is discontinued (deleted), and then a retried, older upsert for p3 arrives:
apply(ev) {
const current = this.docs.get(ev.id)?.version ?? this.tombstones.get(ev.id) ?? 0;
if (this.check && ev.version <= current) return `skip ${ev.op} ${ev.id} v${ev.version} (index has v${current})`;
this.remove(ev.id);
if (ev.op === 'delete') { this.tombstones.set(ev.id, ev.version); return `apply delete ${ev.id} v${ev.version}`; }
this.docs.set(ev.id, ev);
for (const t of new Set(tokenize(ev.title))) (this.postings.get(t) ?? this.postings.set(t, new Set()).get(t)).add(ev.id);
return `apply ${ev.op} ${ev.id} v${ev.version} "${ev.title}"`;
}last write wins (no version check):
apply upsert p1 v3 "Ergonomic wireless mouse, silent clicks"
apply upsert p1 v2 "Wireless mouse (2025 model)"
apply delete p3 v2
apply upsert p3 v1 "Wireless keyboard and mouse combo"
search "wireless mouse" -> p1 "Wireless mouse (2025 model)" | p3 "Wireless keyboard and mouse combo"
with external version checks:
apply upsert p1 v3 "Ergonomic wireless mouse, silent clicks"
skip upsert p1 v2 (index has v3)
apply delete p3 v2
skip upsert p3 v1 (index has v2)
search "wireless mouse" -> p1 "Ergonomic wireless mouse, silent clicks"Without version checks, the index ends up with p1’s older title and a discontinued product back in the results. Both are silent: nothing errors, the data is just wrong, and it stays wrong until that product changes again. With version checks, each event carries the version from the source (a row version column, or the change’s log position), and the index rejects anything not newer than what it has. The deleted product stays deleted because the delete left a tombstone recording version 2.
Elasticsearch supports this directly: index with version_type=external and the version from your database, and it rejects older versions with a conflict error. The tombstone detail matters in practice too. Elasticsearch keeps deleted documents’ versions for a limited time (index.gc_deletes, 60 seconds by default), so an older upsert that arrives later than that can still resurrect a deleted document. For pipelines where late replays are possible, use soft deletes (index the document with deleted: true and filter it out) so the version check always has something to compare against.
Lag you can see and measure
End-to-end lag is the time from a commit in PostgreSQL to the change being searchable: CDC read delay, Kafka, the indexer’s processing, and the refresh interval. Typically that’s a second or two; during a backfill or a Kafka consumer outage, it can be hours. Measure it rather than assuming it: stamp each event with its commit time and have the indexer report now - commit_time as a histogram, or update a canary document every minute and check when it becomes searchable.
Then design the product around the lag. A seller who just created a listing shouldn’t search for it and conclude it’s missing. Show their own listings from the database on their dashboard, perhaps with “publishing to search, usually within a minute”, and only use search for discovery.
Re-check what the index can’t promise
Search results can be slightly stale, which is fine for “which products match?” and not fine for “can this user see this?” or “what does it cost now?” A common approach is to use the index to find and rank candidate ids, then load the current price, stock and visibility of the top results from the source (or a cache that’s invalidated properly). For permissions, index the access rules so filtering happens inside the search, but re-check access when the user opens a result. A stale index should at worst show a result that turns out unavailable, never expose something private.
Rebuilding without downtime
Mappings change, analysers improve, and bugs corrupt documents. You’ll reindex everything eventually, so plan for it from the start. Point all queries at an alias (products) rather than a concrete index. To rebuild:
- Create
products_v2with the new mapping. - Note the current position in the change stream, then backfill from a database snapshot.
- Replay changes from the noted position until
products_v2catches up. Version checks make the overlap between backfill and replay harmless. - Atomically switch the alias from
products_v1toproducts_v2, keep v1 briefly for rollback, then delete it.
Interview practice: the listing that won’t go away
“Sellers occasionally report that a listing they deleted keeps showing up in search for hours, until something else about it changes. Listings are written to PostgreSQL and the service then sends updates to Elasticsearch. What’s happening and how do you fix it?”
Answer
“Until something else changes” tells me the index is in a wrong state that nothing corrects, rather than just lagging. The likely cause is the dual write. Either the delete call to Elasticsearch failed (or the process died before making it), or a delayed retry of an older update arrived after the delete and recreated the document. Both leave a deleted listing in the index with nothing to fix it.
I’d replace the dual write with change data capture: Debezium reads the listings table’s changes from the WAL into a Kafka topic partitioned by listing id, and an indexer applies them. Every document is written with version_type=external, using a version that increases on every change to the row, so stale events are rejected. For deletes, I’d index a soft-delete marker instead of removing the document, so a late older update still loses the version check, and filter deleted: true out of queries. I’d measure end-to-end lag with commit timestamps on events, and run a nightly job comparing listing ids and versions between PostgreSQL and the index to catch any drift. Finally, I’d keep the index behind an alias so a full rebuild is routine if drift is ever found.