A grocery marketplace runs lexical search in Elasticsearch and semantic retrieval in a separate vector service. Prices and stock change all day so documents are partially updated constantly and reindexing lags by hours; fixing bad data can take a day. The team proposes adding a third store - a dedicated vector database - to improve relevance. Reviewing this proposal, what is the right change?
Show the full answer Hide the answer
What is actually required
Read the symptom again: the complaint is relevance, the evidence is all about writes. Partial updates to price and availability across a very large catalogue, indexing that cannot keep up, and a day to correct bad data. That is a write-amplification problem wearing a relevance costume, and adding a store makes it worse by adding a third copy of an item that changes several times an hour.
Instacart published this exact progression: full-text search in Elasticsearch, then full-text search in Postgres, then semantic search with FAISS alongside it, and now a hybrid where lexical and embedding retrieval both run in Postgres with pgvector. Their stated reason for leaving Elasticsearch was not query quality - it was that the cluster did not scale well for their denormalised model and write pattern, with frequent partial document updates overloading indexing and bad data taking days to fix. The fix was a normalised model where features and model coefficients live in separate tables with different write frequencies and are joined at query time, with GIN indexes and a modified ranking function doing the text matching.
Why consolidation is the right call here
One copy of the catalogue means one write path. A price change updates one row instead of triggering a document rebuild in two or three engines. Joining features at query time, rather than denormalising them into a document, is what removes the amplification: the thing that changes often and the thing that changes rarely stop sharing a write.
Why the other options fail
- Add the vector database and keep Elasticsearch. The textbook "best tool for each job" answer, correct when the corpus is static and relevance is the binding constraint. Here it triples the update fan-out on a catalogue that changes all day, and adds a third consistency boundary to debug when a shopper sees a price that no store agrees with.
- Keep both and add change-data-capture. This fixes freshness lag and leaves the amplification untouched - the same number of index writes, now with a pipeline to operate and a new failure mode where the stream falls behind silently. It is the right move when the engines are genuinely needed and only the plumbing is wrong.
- Everything in the vector store. Embeddings lose exact matching, which grocery search depends on: brand, pack size, "2% milk", a specific SKU. It also makes ranking unexplainable to the merchandisers who have to answer for why a product ranked where it did.
What would flip the decision
| If this changes | Choose | Because |
|---|---|---|
| The catalogue becomes near-static | The specialised engines | write amplification stops being the cost and recall quality dominates |
| The relational store is already write-saturated | Keep retrieval separate | you cannot solve a write problem by adding reads to the same primary |
| Query volume outgrows the primary | Read replicas or a dedicated retrieval replica | separate the serving path without separating the source of truth |
| Relevance needs a model the store cannot host | Specialised engine for that path only | one path moves out - not the catalogue |
When this is the wrong answer
Where the data arrives once and is queried forever - a documentation corpus, a legal archive, a media library - the specialised engine is the right answer and consolidation buys nothing. The discriminator is not the kind of search. It is how often the indexed thing changes relative to how often it is read, and who has to fix it when it is wrong.