Search and ranking
Inverted indexes, relevance, and why your database LIKE query does not scale.
The inverted index
intermediateA normal index maps a row to its values. An inverted index turns that around: each term points at the list of documents containing it, so a search for shoes reads one posting list rather than examining every row. That inversion is the entire reason full-text search is fast, and it is why the structure has to be different rather than merely bigger.
Text is normalised before it is indexed, and the same pipeline runs over the query so the two can meet. Lowercasing, tokenisation, stemming so running matches run, stop word removal, and often synonyms. Indexing and querying must use the same analyser: a mismatch produces a search that finds nothing for reasons no error message explains, which is among the more frustrating afternoons available.
A LIKE query with a leading wildcard cannot use a B-tree at all, because a B-tree is ordered by prefix and a leading wildcard removes the prefix. So it scans every row, which is fine at ten thousand rows and hopeless at ten million. That is not the database being slow; it is the wrong data structure for the question.
Postgres will do this for you with a GIN index over a tsvector, and that is usually the right first move. It gives stemming, ranking with ts_rank and phrase search inside the database you already run, with no second system to keep in sync. Trigram indexes cover the other common case, fuzzy and substring matching, which full-text search deliberately does not do.
A dedicated engine starts paying when you need what an engine has and a database does not: faceting and aggregations over results, per-field boosting and tuneable relevance, typo tolerance, suggestions, and a scale where the index outgrows the primary. Those are real features rather than a performance argument, and they are the honest reason to take on a second datastore. The cost of that decision is one thing, repeated: the index is a second copy of the data and it can drift. Everything else, the mappings, the analysers, the ranking, is tuning. The copy is the design problem, and it is why the next topic exists.
Which problem do trigram indexes solve that full-text search does not?
Evidence · 1 source
- 01PostgreSQL: full text search
That Postgres provides stemming, ranking and phrase search over a tsvector with a GIN index, and that trigram indexes cover the fuzzy and substring cases full-text search deliberately does not.
Answers are generated and can be wrong. The topic above is the reviewed version.
Relevance and ranking
advancedMatching is the easy half and it is the half that gets built. Ranking decides which of ten thousand matches appear in the first five results, and since almost nobody looks past those, ranking is what users experience as the quality of your search.
The classical scoring is TF-IDF and its better-behaved successor BM25: a term counts for more when it appears often in a document and less when it appears in many documents, so rare words carry the signal and common ones do not. BM25 adds saturation, so the twentieth occurrence of a word adds almost nothing, and length normalisation, so a long document does not win simply by containing more words. That saturation is the practical difference and it is why BM25 is the default in every serious engine.
Text score alone produces results that are correct and feel wrong, because relevance in a product is not only about words. Real ranking blends the text score with business signals: recency for news, popularity or conversion rate for commerce, stock availability, distance, and whatever personalisation you can justify. Each is a weight, and the weights are a product decision rather than a technical one.
Two search-specific mechanics are worth knowing. Boosting weights a field or a condition, so a match in the title counts for more than one in the body, which is usually the single highest-value tuning change available. Faceting turns the result set into counts by attribute, which lets people narrow rather than reformulate, and it is often more valuable than any ranking improvement because it changes what the user can do rather than what they are shown.
Measure it, or the tuning is a matter of taste. Click-through on the top results, the rate of searches that return nothing, the rate that lead to a session ending without a click, and the position of the item people eventually chose. Those numbers tell you whether a change helped, and they are the difference between search as an engineering exercise and search as a product.
The failure worth naming is the zero-result search. It is the clearest signal you have and it is usually caused by something fixable: no synonym handling, no typo tolerance, or a filter silently applied. Logging those queries and reading them weekly is the cheapest search improvement available, and it needs no model at all.
Which is usually the highest-value tuning change in a product search?
Evidence · 1 source
- 01Elasticsearch: theory behind relevance scoring
That BM25 replaced TF-IDF as the default scoring function, and what saturation and length normalisation change about the result.
Answers are generated and can be wrong. The topic above is the reviewed version.
Keeping the index in sync
advancedThe search index is a second copy of the data, so it can drift. Dual writes, writing to the database and the index in the same request, fail the moment one succeeds and the other does not, and nothing about that failure is visible until somebody searches for the missing thing.
Drift is silent and cumulative. Each individual miss is rare, a failed call here, a timeout there, but nothing corrects them, so the index degrades steadily and the only signal is a user saying they cannot find a product that plainly exists. By then the gap is weeks old and there is no log of which writes were lost.
The reliable pattern is to write to the database and derive the index from its change log, so the database stays the single source of truth and the index is always a function of it. Deletes come through as well, which polling an updated_at column misses entirely: a deleted row simply stops appearing in the query, and the document stays in the index forever.
You also need a full reindex path, and you will use it more than you expect. Mappings change, an analyser is wrong, a bug corrupts a field. Build into a new index under a versioned name and swap an alias when it is ready: the swap is atomic, search never goes down, and the previous index is still there when the new one turns out to be worse.
Why build a reindex into a new index and swap an alias?
Evidence · 1 source
- 01Debezium documentation
The implementation described here: reading a database's replication log and publishing each committed change, so the index is derived from the database rather than written alongside it.
Answers are generated and can be wrong. The topic above is the reviewed version.
Autocomplete and suggestions
advancedAutocomplete looks like a smaller version of search and is a different problem. The latency budget is a keystroke, so under about a hundred milliseconds end to end, and every character typed is a request, which means a search box generates an order of magnitude more traffic than the search itself.
The data structure follows from the prefix requirement. A trie holds strings by shared prefix so a lookup walks the typed characters and returns the subtree, and the practical version precomputes the top few completions at each node so the answer is a read rather than a traversal plus a sort. Where a trie is inconvenient to operate, an inverted index with an edge n-gram analyser gets close enough by indexing every prefix as a term.
Ranking matters more than matching here, because a prefix of three characters matches thousands of things and the box shows five. Popularity is the usual backbone, weighted toward recency so that what people searched for this week outranks last year, with personal history above both because someone's own previous searches are the strongest signal available about what they mean.
The client is half the system. Debounce so a fast typist produces a handful of requests rather than one per character, cancel in-flight requests when a newer keystroke arrives, and be careful about out-of-order responses: the reply for a three-letter prefix arriving after the four-letter one will replace correct suggestions with stale ones unless each response is checked against the current input.
Everything about this favours caching. Prefixes are short, popular ones are extremely popular, and results change slowly, so an edge cache with a short expiry absorbs most of the traffic. The remaining engineering is mostly about what not to suggest: profanity, other people's private data, and the long tail of queries that returned nothing, which should not be offered to the next person.
Why is autocomplete usually served from precomputed results rather than from a search query?
Evidence · 1 source
- 01Elasticsearch: suggesters
That the completion suggester is backed by a finite state transducer held in memory rather than reusing the normal search path, because prefix lookup and relevance search have different latency budgets.
Answers are generated and can be wrong. The topic above is the reviewed version.