A user types "wireless headphones" into your search box. You have ten million products.
The obvious query:
SELECT * FROM products WHERE description LIKE '%wireless headphones%';This is unusable, for two independent reasons.
It cannot use an index. A B-tree index is sorted by the beginning of a value, so it can find things starting with "wireless" quickly. A leading wildcard makes that useless, forcing a full scan of ten million rows on every search.
It answers the wrong question. It finds the exact substring, so it misses "wireless bluetooth headphones", misses "headphones, wireless", misses "Wireless Headphone" (singular), and has no notion of which result is best. Search is not string matching — it is ranked retrieval.
The inverted index
Search engines flip the data around. Instead of documents pointing to their words, words point to their documents.
A forward index — what a database holds — looks like:
doc 1 -> "wireless bluetooth headphones"
doc 2 -> "wired gaming headset"
doc 3 -> "wireless mouse"The inverted index looks like:
"wireless" -> [1, 3]
"bluetooth" -> [1]
"headphones" -> [1]
"wired" -> [2]
"mouse" -> [3]Now "wireless headphones" is two dictionary lookups and a set intersection — [1,3] ∩ [1] = [1]. That is roughly constant work regardless of corpus size, rather than a scan proportional to it.
This one inversion is the entire foundation. Everything else is refinement.
Analysis: turning text into terms
Before indexing, text passes through a pipeline that decides what a "word" even is. Getting this right is most of search quality.
Tokenisation splits text into terms. Harder than splitting on spaces — what about "state-of-the-art", "C++", or languages without spaces?
Lowercasing so "Wireless" and "wireless" match.
Stop word removal drops extremely common words like "the" and "a", which appear everywhere and carry little meaning. Modern engines often keep them, since dropping them breaks phrase searches like "to be or not to be".
Stemming or lemmatisation reduces words to a root so "running", "runs" and "ran" match "run". Stemming is crude and fast (chopping suffixes); lemmatisation uses a dictionary and is accurate but slower.
Synonyms map "laptop" to "notebook".
Relevance: which result is best?
Matching gives a set. Ranking orders it, and ordering is what users actually judge you on.
The classic scheme is TF-IDF, built from two intuitions:
Term frequency — a document mentioning "headphones" eight times is probably more about headphones than one mentioning it once.
Inverse document frequency — a term appearing in almost every document is uninformative. In a corpus of electronics listings, "wireless" is weak evidence while "Sennheiser" is strong. Rare terms should count for more.
Multiply them and you get a score that rewards documents which use a distinctive term often.
BM25 is the modern refinement most engines actually use. It adds two corrections: term frequency saturates, so the tenth mention adds far less than the second, and it normalises for document length, so a long document does not score highly merely by containing more words.
Real systems then blend in signals beyond text — popularity, recency, click-through rate, personalisation, and for commerce, margin and stock. Text relevance is one input, not the answer.
Architecture
Diagram, as text
- App leads to Primary DB (write)
- Primary DB leads to Change stream
- Change stream leads to Indexing pipeline
- Indexing pipeline leads to Search cluster
- User leads to Search cluster (query)
The key structural point: the search index is a derived read model, not your source of truth.
Your database holds authoritative data. The search cluster holds a transformed, denormalised copy optimised for retrieval. This matters because it tells you how to handle failure — if the search cluster is lost, you rebuild it from the database. Losing it is an availability problem, never a data loss problem.
Search clusters shard the index across nodes and replicate each shard. A query fans out to all shards, each returns its top N, and a coordinating node merges them — scatter-gather.
Keeping the index in sync
The hard operational problem, and where most real bugs live.
Dual writes — write to the database and the index in the same request — look simplest and are wrong. There is no transaction across two systems, so if the second write fails you are silently inconsistent, and the failure is invisible until a user notices missing results.
Change data capture is the robust approach: read the database's replication log and feed changes into an indexing pipeline. The database remains the single source of truth, updates flow reliably, and a consumer that falls behind catches up rather than losing events.
Periodic reindexing rebuilds from scratch. Slow, but it repairs drift — and drift will happen. Use the alias pattern: build a new index alongside the old, then atomically switch the alias so there is no window with no index.
Accept that search is eventually consistent. A product edited now may take seconds to appear updated in results. That is almost always fine, and worth stating explicitly rather than pretending otherwise.
Features users expect
Autocomplete needs prefix matching, usually via a specialised structure like an edge n-gram index or a finite state transducer — not the main text index.
Typo tolerance via fuzzy matching within a small edit distance. Keep it small; edit distance 2 on short words matches almost anything.
Faceting — the counts beside filters ("Brand: Sony (42)") — is an aggregation over matching documents, and one of the main reasons to use a search engine rather than a database.
Highlighting returns the matching snippet with terms marked, which requires the index to store term positions.
Check your understanding
Common mistakes
Using your primary database for full-text search at scale. Postgres full-text search is genuinely good and often enough — but it competes for the same resources as your transactional workload.
Making the search index authoritative. It is a derived view. Never store data that exists nowhere else.
Analyser mismatch between index and query. Silent, confusing, and common.
Ignoring relevance tuning. Correct matching with poor ranking still feels broken to users. Measure with click-through and zero-result rates.
Reindexing without an alias. Deleting and rebuilding leaves a window where search returns nothing.
Fuzzy matching everything. Typo tolerance on every term produces noisy results and slow queries.
Practice
Your product search returns results a user considers wrong: searching 'red dress' surfaces a blue dress with 'red' in a review. Describe how you would diagnose and fix this without breaking other queries.
Write your own answer first, on paper or in a note. Then compare.
Show a hint
Separate whether the document should match at all from whether it should rank highly.
Show a strong answer
First separate the two questions, because they have different fixes:
- Should it match? If reviews are in the same searchable field as the product description, then yes, it legitimately matches — the term is present. The problem is the field mapping, not the ranking.
- Should it rank highly? If it matches but ought to be far down, the problem is scoring.
Diagnosis: look at why the document scored as it did. Most engines can explain a score, showing which fields matched and their contribution. That answers the question in seconds instead of by guessing.
Likely fix, in order:
- Separate the fields and weight them. Title and attributes should count far more than review text. A colour attribute of
blueshould outweigh the wordredappearing in a customer's sentence. - Use structured attributes for structured facts. Colour is not free text — it is a facet. Searching "red dress" should filter on
colour=redand match "dress" textually, rather than treating both as words. This is the real fix and it removes a whole class of similar complaints. - Only then adjust relevance weights, because tuning scores to fix a problem that is really a modelling problem produces a tangle that breaks other queries.
How to avoid breaking other queries: keep a set of judged query-result pairs and measure before and after. Relevance changes are global — every tweak that improves one query moves thousands of others, and without measurement you are trading complaints you can see for complaints you cannot.
A product is updated and the change must appear in search. Describe two ways to keep the index in sync, with failure modes.
Write your own answer first, on paper or in a note. Then compare.
Show a hint
One couples search to the write path; the other decouples it and accepts delay.
Show a strong answer
Option one — write to the index synchronously with the database write.
- Failure mode: two systems, one transaction that cannot span both. If the database commits and the index write fails, they diverge silently — search shows stale data with nothing reporting an error. If the index write is inside the request path, search being slow makes product updates slow, and search being down makes updates fail entirely. You have coupled the availability of a secondary system to your primary write path.
Option two — publish a change event and index asynchronously.
- The database write commits, an event is emitted, and an indexer consumes it. The index lags by whatever the pipeline takes, typically seconds.
- Failure mode: the lag is visible to users ("I saved it, why can't I find it?"), and if the consumer falls behind or fails, divergence grows silently unless monitored.
Prefer option two, with three additions that make it survivable:
- Emit the event from the same transaction that writes the data — an outbox table — so an event cannot be lost when the write succeeds, nor emitted when it fails.
- Make indexing idempotent and keyed by version, so a replayed event cannot apply an older state over a newer one.
- Reconcile periodically. A background job that compares database and index and repairs differences is what turns silent divergence into a bounded, self-healing error — and it is the part teams most often skip.
Key takeaways
LIKE '%term%'cannot use an index and answers the wrong question — search is ranked retrieval, not substring matching.- The inverted index maps terms to documents, turning search into dictionary lookups and set intersections independent of corpus size.
- Analysis defines quality — tokenising, lowercasing, stemming and synonyms — and the same analyser must run at index and query time.
- TF-IDF and BM25 rank by rewarding distinctive terms used often, with BM25 correcting for saturation and document length.
- The search index is a derived read model, so losing it is an availability problem, never data loss.
- Avoid dual writes; drive indexing from a change stream, and reindex behind an alias to avoid an empty window.
- Search is eventually consistent by design, and engines buffer writes for throughput.
Further reading
- Justin Zobel and Alistair Moffat, "Inverted Files for Text Search Engines" (ACM Computing Surveys, 2006).
- Stephen Robertson and Hugo Zaragoza, "The Probabilistic Relevance Framework: BM25 and Beyond" (Foundations and Trends in Information Retrieval, 2009).
- Christopher Manning, Prabhakar Raghavan and Hinrich Schütze, Introduction to Information Retrieval (Cambridge University Press, 2008).
- Doug Cutting and Jan Pedersen, "Optimizations for Dynamic Inverted Index Maintenance" (SIGIR 1990) — segment merging.
- Gunnar Morling, "The Outbox Pattern" (Debezium blog, 2019) — reliable change events for index synchronisation.