Your LIKE '%term%' query takes 4 seconds against 10 million rows. Your users expect 100 milliseconds. You reach for Elasticsearch. Before you do, understand what changes when you cross that boundary.
A database query and a full-text search are not the same problem. A database index answers "which rows have this value?" A search index answers "which documents contain this word?" The question is inverted. The data structure is inverted. The cost model is inverted.
The Structural Inversion
A B-tree indexes ordered keys. Find user 42, find orders between two dates — the search key is exact and the tree walks in O(log N). This is the right structure when you know exactly what you are looking for.
Full-text search breaks this assumption. "Find documents about distributed caching" does not map to a range on a sorted key. The user might type three words. The most relevant document might use synonyms. A B-tree cannot answer this.
The inverted index can. For each term in the corpus, store the list of documents that contain it. This list is called a posting list. A query for "caching database" intersects the posting list for "caching" with the posting list for "database" and ranks the intersection.
The direction of the mapping is what matters. Documents point to words in the source. The index makes words point to documents. Every full-text search system built since 1960 rests on this inversion.
The Tradeoff: AT4 — Precomputation vs On-Demand
The inverted index is the most extreme form of precomputation in production systems. At index time, every document is tokenised. Every term is scored. Every posting list is sorted.
The cost is real. A full-text index for one billion documents runs to hundreds of gigabytes. Index build time is measured in hours. Every write triggers work in the index pipeline.
The benefit is also real. Query latency drops to milliseconds. Without the index, answering "find all documents containing the word 'cache'" requires reading every document. That operation takes hours on a large corpus.
The tradeoff resolves cleanly when query frequency is high. For a corpus queried once per day, a full scan is more economical than maintaining the index. For a corpus queried thousands of times per minute, the index pays for itself in the first hour.
What Happens Inside the Pipeline
Text does not enter the index unchanged. The pipeline runs four steps.
First, lowercase everything. "Cache" and "cache" are the same token.
Second, split on whitespace and punctuation. "Distributed Caching, Redis!" becomes three tokens.
Third, remove stop words. Words like "the", "a", "is" appear in nearly every document and carry no signal. Drop them.
Fourth, stem or lemmatise. "Running" becomes "run". "Caches" becomes "cache". Plural and tense forms collapse to a root.
Query text passes through the same pipeline. This is why a search for "running shoes" matches a document titled "shoe designed for runners" — both sides reduce to the same root tokens. Get the tokenisation wrong and the index answers different questions than the user asked.
Scoring: TF-IDF and BM25
Intersecting posting lists gives you candidate documents. Ranking them requires a scoring model.
TF-IDF is the original answer. Term Frequency measures how often a term appears in a document. Inverse Document Frequency measures how rare the term is across the corpus. Multiply them. A document with many occurrences of a rare term scores high. A document with few occurrences of a common term scores low. Common terms like "the" have IDF near zero.
BM25 is the modern replacement. It fixes two problems in raw TF-IDF. Term frequency saturates — the tenth occurrence of a word adds less relevance than the second. Document length matters — a single mention in a tweet signals more than a single mention in a ten-thousand-word article. BM25 tunes both with two parameters, k1 and b.
Every modern search engine — Elasticsearch, Solr, Lucene under both — scores with BM25 by default.
Near-Real-Time Indexing and AT2
New documents are not immediately searchable. Elasticsearch and Lucene use segment-based architecture. Writes land in an in-memory buffer. Every refresh interval — one second by default — the buffer flushes to a new immutable segment on disk. Only then does the document become searchable.
This is AT2 — Latency vs Throughput expressed as a knob. Reduce the refresh interval to 100 milliseconds and documents become searchable faster. Background I/O climbs. Write amplification climbs. Segments proliferate and compaction cost rises.
Increase the refresh interval to 10 seconds and I/O drops. Documents wait longer to appear in results. For product search, one second is invisible. For news search or live chat search, one second is unacceptable.
The default is not a decision. Pick the number your workload requires.
Where It Fails: FM5 — Latency Amplification
A posting list for a very common term can contain millions of entries. "Data" in a technical corpus. "Function" in a code search index. "Product" in an e-commerce catalogue.
Intersecting two posting lists means traversing and merging both. A query for one common term and one rare term must walk the entire posting list of the common term to find documents that also contain the rare one. The rare term's list is short. The common term's list dominates the cost.
The result: two queries that look identical to the user perform an order of magnitude apart. A search for "titanium bracket" runs in 5 milliseconds. A search for "steel bracket" runs in 200 milliseconds. Same shape. Same corpus. Different posting list sizes.
Stop word filtering removes the most extreme cases. Domain-specific common terms slip through. Posting list compression, early termination, and the WAND algorithm exist because this failure mode is real and permanent.
The Second Failure: Unbounded Index Growth
Deletes do not remove entries from posting lists. They mark documents as deleted and filter them at query time. The posting list keeps its entry.
A high-churn corpus — e-commerce products created and discontinued monthly — accumulates tombstoned entries. Every query pays to filter them. Latency climbs quietly over months.
Segment merging with deletion handling reclaims the space. Compaction rewrites the merged posting lists without the deleted entries. Compaction itself consumes I/O and competes with queries. On a write-heavy index, continuous segment creation and compaction can overwhelm disk bandwidth. This is the operator burden that a hosted database index does not carry.
The Signal That Tells You This Applies
Your query latency is bimodal. Some queries return in tens of milliseconds. Others in seconds. The slow queries share a common word — a domain term that appears in a large fraction of your documents.
That is FM5 in production. The fix is not a bigger cluster. The fix is understanding which term dominates the posting list traversal and either filtering it, splitting the index, or accepting that a full-text index is a different tool than a database and priced accordingly.
The harder question the article did not answer: when your users type "comfortable running shoes" and the best matching product describes itself as "cushioned trainers for daily wear", the inverted index returns nothing. Text matching fails on meaning. What structure answers a query the document does not literally contain?
The full framework treatment — compression blocks, three-level exercises, and the complete AT/FM mapping — is in Book 3: Scaling Systems, Chapter 11 (Search Infrastructure). Free chapter available at computingseries.com/books/book3.