Full-Text Search & the Inverted Index

Why LIKE cannot power search

A relational WHERE description LIKE '%wireless headphone%' cannot power real search: it does a full scan, cannot rank by relevance, cannot handle typos or word stems, and dies at scale. That is why a dedicated search tier exists. Its core data structure is the inverted index: instead of mapping a document to its words, it maps each word (term) to a posting list of the documents that contain it. Query "wireless headphones" and the engine intersects the posting lists for wireless and headphone in milliseconds, no scan required.

Terms do not go into the index raw; they pass through an analysis pipeline. Tokenize the text into words, lowercase them, stem ("running", "ran", "runs" all collapse to "run"), drop stopwords, and expand synonyms ("tv" also indexes as "television"). The same analyzer must run at index time and query time so the terms match. Typo tolerance comes from fuzzy matching (edit distance) or n-gram indexing, so "hedphones" still finds "headphones".

From documents to a queryable inverted index

Step 1 / 3

Stage 1 of 3: Terms never enter the index raw: the analysis pipeline tokenizes, lowercases, stems ('running' collapses to 'run'), drops stopwords, and expands synonyms.

Ranking, queries, and filters

The default ranking is BM25 (a refined TF-IDF): a term matters more when it is rare across the corpus (high IDF) and appears often in a short document. On top you apply boosting (title matches worth more than description, in-stock and popular items lifted) and filters. Crucial distinction: a query contributes to the relevance score; a filter is a yes/no constraint (brand = Sony, price < 100) that does not score and, because it is deterministic, is cached as a bitset and reused cheaply across requests. Facets and highlighting come from the same index.

At scale you run Elasticsearch or OpenSearch, which shards the index. A shard is a self-contained inverted index (a Lucene index); documents are routed to a primary shard by hash of the id, and each primary has replica shards for read throughput and failover. A 50M-document catalog might use 10 primaries; size shards to the tens-of-GB range because oversharding wastes memory.

Keeping the index in sync

Search is not a system of record. The truth lives in your primary DB; the index is a derived, rebuildable store. Feed it with a CDC / indexing pipeline: capture DB changes (Debezium on the binlog, or an application event) onto a stream, and an indexer applies them to Elasticsearch. This is eventually consistent, so a product edit shows in search a second or two later, which is fine. Because it is derivable, plan for full reindexing: mapping changes require building a fresh index and switching an alias over atomically, with zero downtime.

Interview nuance: the classic trap is deep pagination. from: 100000, size: 10 forces every shard to sort 100,010 docs and is O(offset). Use search_after (a cursor on the last sort value) for deep result sets, and cap the max page. Also be ready to say why you would not make Elasticsearch your primary DB: weaker durability and consistency guarantees, and no transactions.

Recap: search runs on a dedicated tier built on an inverted index plus an analysis pipeline, ranks with BM25 and boosting, separates scoring queries from cached filters, shards across primaries and replicas, stays in sync as an eventually-consistent derived store fed by CDC, and paginates deep sets with search_after, never large from offsets.