Skip to content

Search and ranking

Inverted indexes, relevance, and why your database LIKE query does not scale.

01

The inverted index

intermediate

A 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.

Check yourself

Which problem do trigram indexes solve that full-text search does not?

at index timeat query timesame pipelinelookup, not scanDocumentRunning ShoesQueryrunning shoeAnalyserlowercase, stemTermsrun, shoePosting liststerm to documents
Data storeClientServiceDashed = asynchronous
The same analyser on both sides, or nothing matches
Evidence · 1 source
  1. 01
    PostgreSQL: 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.

Ask about this

Answers are generated and can be wrong. The topic above is the reviewed version.

02

Relevance and ranking

advanced

Matching 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.

Check yourself

Which is usually the highest-value tuning change in a product search?

text scoreblend, with weightscheapest large winrankedrankedor it is tasteQuery10,000 documentsmatchBM25saturation and lengthnormBusiness signalsrecency, popularity,stock, distanceField boostingtitle over bodyTen resultsand facets to narrowwithMeasuredzero-result rate,click position
ClientServiceData storeExternalDashed = asynchronous
Match, score, then blend the signals that are not about words
Evidence · 1 source
  1. 01
    Elasticsearch: theory behind relevance scoring

    That BM25 replaced TF-IDF as the default scoring function, and what saturation and length normalisation change about the result.

Ask about this

Answers are generated and can be wrong. The topic above is the reviewed version.

03

Keeping the index in sync

advanced

The 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.

Check yourself

Why build a reindex into a new index and swap an alias?

write oncechanges, in commit orderServiceDatabasesource of truthSearch indexderived from thechange log
ServiceData storeDashed = asynchronous
Derive the index from the log, so the database stays the only source of truth
Evidence · 1 source
  1. 01
    Debezium 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.

Ask about this

Answers are generated and can be wrong. The topic above is the reviewed version.

04

Autocomplete and suggestions

advanced

Autocomplete 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.

Check yourself

Why is autocomplete usually served from precomputed results rather than from a search query?

3 characterson missprecomputed offlinearrives after the next keystrokeKeystrokedebouncedEdge cacheshort expiry, hotprefixesPrefix storetop completions pernodeRankingpopularity, recency,historyLate responsediscarded, inputmoved on
ClientEdge / CDNData storeServiceExternalDashed = asynchronousConsidered, not chosen
Precompute the answer, cache the prefix, cancel the stale reply
Evidence · 1 source
  1. 01
    Elasticsearch: 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.

Ask about this

Answers are generated and can be wrong. The topic above is the reviewed version.