Chrysolites Tech Academy

Data and Search for AI Apps · Course book

Chapter 2: Combining Keyword and Vector Search

Pairs with Lesson 2: Hybrid Search in PostgreSQL

There are two ways to search text. Keyword search finds documents containing the words you typed. Vector search finds documents that mean something similar. Each is strong exactly where the other is weak, so the best search systems run both and merge the results, an approach called hybrid search. This chapter explains how each side ranks its results, how the two rankings are combined, and why doing all of it inside one PostgreSQL database is attractive.

Two kinds of search, two kinds of failure

Suppose a collection of help articles and two different questions.

The first question is "error E4021". Keyword search handles this perfectly: it finds the one article containing that exact code. Vector search does badly, because an arbitrary code has no meaning for an embedding model to capture, and it may return articles about errors in general.

The second question is "my screen keeps going dark". The relevant article is titled "Adjusting display brightness and sleep settings" and contains none of the words in the question. Keyword search finds nothing useful. Vector search finds it at once, because the meanings are close.

Kind of queryKeyword searchVector search
Exact codes, part numbers, identifiersStrongWeak
Unusual names and rare technical termsStrongOften weak
Questions phrased in different words from the documentWeakStrong
Synonyms, such as "car" and "automobile"Weak unless specially configuredStrong
Explaining why a result matchedEasy: the words can be highlightedHard: only a similarity score

Real users send both kinds of query, often in the same sentence. A system that offers only one kind of search will fail on a predictable share of them.

How keyword ranking works: BM25

Finding the documents that contain a word is the easy part. A database builds an inverted index, which is like the index at the back of a book: for each word, a list of the documents in which it appears. The hard part is ordering the matches, and the standard method is a formula called BM25. It rests on three ideas.

Rare words matter more. If you search for "the quantum", a document's use of "quantum" tells you far more than its use of "the". BM25 gives each word a weight called its inverse document frequency, which is high for rare words and low for common ones. With N documents in total, of which n contain the word, the weight is:

weight = ln( (N - n + 0.5) / (n + 0.5) + 1 )

Take a collection of 1,000 documents. A word found in only 10 of them gives (990.5 ÷ 10.5) + 1 = 95.33, and the natural logarithm of that is about 4.56. A word found in 500 of them gives (500.5 ÷ 500.5) + 1 = 2, whose logarithm is about 0.69. The rare word counts more than six times as much.

Repetition helps, but less and less. A document that mentions your word five times is probably more relevant than one mentioning it once, but not five times more. BM25 uses a curve that rises quickly and then flattens. Ignoring document length, the contribution is proportional to f ÷ (f + 1.2), where f is the number of times the word appears and 1.2 is a typical setting.

0.450.630.810.890.94 00.51 051020times the word appears in the document share of the maximum score
Figure 2.1 — Diminishing returns in BM25. The first mention of a word earns 45 percent of the maximum; going from ten mentions to twenty adds only five points more.

The values are easy to check: 1 ÷ 2.2 = 0.45, 2 ÷ 3.2 = 0.63, 5 ÷ 6.2 = 0.81 and 10 ÷ 11.2 = 0.89. This flattening is what stops a page stuffed with one repeated word from dominating the results.

Long documents get no free advantage. A long document naturally contains more words, so it would match more often by chance. BM25 compares each document's length with the average and scales the score down for long ones and up for short ones.

Notice that the first idea needs statistics about the whole collection: how many documents there are and how many contain each word. A ranking that uses them is described as corpus-aware, and it adapts as the collection grows.

How vector ranking works

The other half was covered in Chapter 1. Every document is passed through an embedding model once, in advance, to give a vector, for example of 384 numbers. At query time the question is embedded by the same model and the database returns the documents whose vectors are nearest to it.

PostgreSQL does not store or search vectors by itself, but it can be extended. The pgvector extension adds a vector column type, operators for measuring distance and indexes for approximate nearest-neighbour search. Related extensions add index types designed to stay fast when the collection is too large to hold in memory. Because these are extensions to an ordinary database, the vectors live in the same table as the title, text and date of each document.

The output is the same kind of thing as before: an ordered list of documents, best first. But the scores behind the two lists are not comparable. A BM25 score might be 14.2 and a cosine similarity 0.83. They measure different things on different scales, so you cannot simply add them.

Merging two rankings with Reciprocal Rank Fusion

The neatest solution ignores the scores altogether and uses only the positions. In Reciprocal Rank Fusion, or RRF, each document earns points from every list it appears in:

points from one list = 1 / (k + rank)

Here rank is the document's position in that list, with 1 for the top, and k is a constant, conventionally 60. A document's final score is the sum of its points from all lists, and documents missing from a list earn nothing from it.

QueryKeyword searchBM25 rankingVector searchnearest embeddingsFinalresults Rankfusion
Figure 2.2 — Hybrid search. The two searches run side by side, and only their rank positions are passed to the fusion step.

Work through an example with four documents. Keyword search returns A, B, C in that order. Vector search returns B, D, A.

DocumentKeyword rankVector rankPointsTotalFinal position
A131/61 + 1/63 = 0.01639 + 0.015870.032272
B211/62 + 1/61 = 0.01613 + 0.016390.032521
C3not listed1/63 = 0.015870.015874
Dnot listed21/62 = 0.016130.016133

Document B wins. It was not first in the keyword list, but it did well in both, and agreement between two independent methods is strong evidence of relevance. Documents A and B, found by both searches, score about twice as much as C and D, which were found by only one. The constant 60 softens the difference between neighbouring positions, so being first instead of second is a small advantage and no single list can dominate.

RRF needs no tuning and no knowledge of how either score was produced, and it is short enough to write directly in SQL: number the rows of each result list, compute one over sixty plus the row number, and add up the points for each document.

Why keep it all in one database

A common design uses a relational database for the data, a separate search engine for keywords and a separate vector database for embeddings. It works, but every document now exists in three places that must be kept in step. If an article is edited and one system misses the update, searches return stale or missing results, and such faults are hard to trace.

Keeping everything in PostgreSQL removes that whole category of problem. A document's text, its keyword index entry and its embedding can be written in a single transaction, which means either all of the changes take effect or none of them do. There is one system to back up, one to secure and one query language for everything, including ordinary filters such as "only articles from this year".

The trade-off is that a specialised search engine may offer more features or scale further. For collections up to some millions of documents, a single well-indexed database is usually simpler and fast enough.

Measure, do not guess. Placing EXPLAIN (ANALYZE, BUFFERS) in front of a query makes PostgreSQL run it and report the plan it chose, the time each step took and how much data it had to read. If the report shows a sequential scan, meaning every row was read, where you expected an index to be used, you have found the reason a search is slow.

Check your understanding

  • Give one query that keyword search handles better than vector search, and one where the reverse is true.
  • In a collection of 1,000 documents, a word appears in 100 of them. What is its BM25 weight? (Answer: (900.5 ÷ 100.5) + 1 = 9.96, and the natural logarithm of 9.96 is about 2.30.)
  • Using f ÷ (f + 1.2), what share of the maximum does a word earn when it appears 3 times? (Answer: 3 ÷ 4.2 = 0.71.)
  • Why can a BM25 score and a cosine similarity not simply be added together?
  • A document is ranked 1st by keyword search and 1st by vector search. What is its RRF score with k = 60? (Answer: 1/61 + 1/61 = 0.0328.)
  • What does writing a document and its embedding in one transaction guarantee?

Install Chrysolites Tech Academy

Android / Chrome / Edge: open the browser menu and choose Install app or Add to Home screen.

iPhone / iPad (Safari): tap the Share button, then Add to Home Screen.