Full-Text Search in Postgres for Hybrid Retrieval

Postgres has had full-text search since 8.3, and it is more than good enough to be the lexical half of a hybrid retriever. This page builds it — generated tsvector column, GIN index, ranked query — and then joins it to a pgvector search in a single statement using reciprocal rank fusion, which is the part every Postgres full-text tutorial stops short of.

Why lexical search still earns its place

Embeddings are bad at exactly one thing, and it is a thing users do constantly: matching a rare literal string. A product code, an error number, a surname, an API parameter name. The embedding of ERR_MODULE_NOT_FOUND is near the embedding of every other error constant, because that is what the model learned about it. BM25 treats it as a rare term and ranks the one document containing it first.

The failure runs the other way too, which is why you want both rather than either — the cases each covers for the other are catalogued in semantic search vs keyword search and hybrid search. What follows is how to run both in one database, which removes the usual objection that hybrid retrieval means operating a second search engine.

The tsvector column

A tsvector is a sorted list of normalised lexemes with their positions. Building it on the fly for every query is possible and slow; store it in a generated column, which Postgres has supported since version 12 and which cannot drift from the content the way a trigger-maintained column can.

ALTER TABLE chunks
  ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;

CREATE INDEX chunks_tsv_gin ON chunks USING gin (tsv);

Two decisions are embedded in that one statement and both are worth making deliberately.

The configuration. 'english' applies an English stemmer and stop-word list: running and runs both become run, the disappears. Applied to German text it produces confident nonsense. If your corpus is mixed, either store a language column and use to_tsvector(lang_column, content) — still allowed in a generated column, as long as the expression is immutable, which requires naming the configuration by regconfig rather than by a runtime-resolved string — or use the 'simple' configuration, which lowercases and does nothing else.

GIN, not GiST. GIN is larger and slower to update but much faster to search, which is the right trade for a corpus that is read far more often than written. GiST is worth considering only if you are writing constantly.

To weight a title above a body, concatenate with setweight. The four labels A through D are ranked by ts_rank with default weights of 1.0, 0.4, 0.2 and 0.1:

ADD COLUMN tsv tsvector GENERATED ALWAYS AS (
  setweight(to_tsvector('english', coalesce(title, '')),   'A') ||
  setweight(to_tsvector('english', coalesce(content, '')), 'B')
) STORED;

Querying and ranking

There are four functions that turn a user’s string into a tsquery and only one of them is right for a search box. to_tsquery requires operators and raises a syntax error on plain prose. plainto_tsquery ANDs every word, so a five-word query usually returns nothing. phraseto_tsquery requires the exact phrase. websearch_to_tsquery, added in Postgres 11, accepts what people actually type — quoted phrases, or, a leading minus for exclusion — and never raises a syntax error.

SELECT id,
       ts_rank_cd(tsv, q) AS lex_score,
       ts_headline('english', content, q,
                   'MaxFragments=2, MinWords=8, MaxWords=25') AS snippet
FROM chunks, websearch_to_tsquery('english', 'postgres "index bloat" -mysql') q
WHERE tsv @@ q
ORDER BY lex_score DESC
LIMIT 50;

@@ is the match operator and the only thing the GIN index can answer. ts_rank_cd is cover density ranking, which rewards documents where the query terms appear close together; plain ts_rank ignores proximity. Neither is BM25 — Postgres does not implement BM25 in core, and the scores are not comparable across queries. That last fact is why the fusion below does not use them directly.

Fusing lexical and vector results

Reciprocal rank fusion combines ranked lists by summing 1/(K + rank) across the lists a document appears in, with K conventionally 60. The method and that constant come from Cormack, Clarke and Buettcher’s 2009 SIGIR paper on reciprocal rank fusion. It uses only the ranks, never the scores, which is exactly what you want when the two scores are on scales that have nothing to do with each other.

WITH lex AS (
  SELECT c.id,
         row_number() OVER (ORDER BY ts_rank_cd(c.tsv, q) DESC) AS rank
  FROM chunks c, websearch_to_tsquery('english', $1) q
  WHERE c.tsv @@ q
  ORDER BY ts_rank_cd(c.tsv, q) DESC
  LIMIT 50
),
sem AS (
  SELECT c.id,
         row_number() OVER (ORDER BY c.embedding <=> $2) AS rank
  FROM chunks c
  ORDER BY c.embedding <=> $2
  LIMIT 50
)
SELECT c.id,
       c.content,
       coalesce(1.0 / (60 + lex.rank), 0) +
       coalesce(1.0 / (60 + sem.rank), 0) AS rrf_score
FROM chunks c
LEFT JOIN lex ON lex.id = c.id
LEFT JOIN sem ON sem.id = c.id
WHERE lex.id IS NOT NULL OR sem.id IS NOT NULL
ORDER BY rrf_score DESC
LIMIT 10;

Parameter $1 is the user’s text; $2 is its embedding, which you computed before issuing the query. One round trip, one transaction, one consistent snapshot of the data — which is the concrete argument for keeping both halves in the same database.

The LIMIT 50 inside each CTE is the fusion depth and it is a real parameter. Too small and a document ranked 60th lexically but first semantically never enters the pool; too large and you are paying for candidates that cannot win. Fifty to a hundred per branch for a top-ten output is the usual range.

To weight one retriever above the other, multiply its term — for instance 1.4 × 1/(60 + lex.rank). Doing that honestly requires a labelled evaluation set, because the right weighting is a property of your queries; measuring search quality with NDCG and MRR is where that starts.

Why not just add the scores

Because they are incomparable, in a way that gets worse the more you look at it. Cosine distance is bounded in [0, 2] and, for a given corpus, its top-10 values cluster in a narrow band — real neighbours might all sit between 0.18 and 0.31. ts_rank_cd is unbounded below by nothing and above by term frequency; a document repeating the query term forty times can score an order of magnitude above the second result, and the same query against a different corpus produces different absolute values.

Add them and the lexical score dominates whenever any document happens to be term-dense, which is not correlated with relevance. Normalise them per query — min-max within the result set — and you have made the scores depend on the other results in the list, so adding one document to the corpus changes the ranking of unrelated ones. Rank fusion sidesteps all of it by throwing the magnitudes away and keeping only the order, which is the only part both retrievers agree on the meaning of.

The traps

• ERROR: string is too long for tsvector — a single tsvector is capped at just under 1 MiB. You will hit it the first time somebody uploads a book as one row. It is an argument for chunking anyway; see text chunking strategies.
• The GIN index is not used for ranking. It answers @@ only. ts_rank_cd is computed on every matching row after the index has done its work, so a query matching two hundred thousand rows pays for two hundred thousand rank computations before the LIMIT applies. Constrain the match, not just the output.
• Accents and case. to_tsvector lowercases but does not fold accents. Install the unaccent extension and wrap the input if your users type cafe and your documents say café.
• Typos are not handled at all. Full-text search matches lexemes exactly after stemming. For fuzzy matching add pg_trgm and a gin_trgm_ops index as a third retriever; it is cheap and it catches the misspellings that make users think search is broken.
• GIN’s pending list. By default GIN buffers insertions in a pending list flushed at vacuum, so a burst of writes can make searches temporarily slower as the list is scanned linearly. fastupdate = off on the index trades write throughput for predictable read latency, which is usually the right trade for a search path.

On cost: the tsvector column and its GIN index together typically add somewhere between a quarter and a full copy of your text to the table, depending on how repetitive the vocabulary is — a corpus of technical documentation with a small term set indexes far more compactly than one full of identifiers and numbers. That is a rounding error next to a vector column at 6 kB a row, which is the practical argument for adding lexical search even if you are unsure it will help: it is the cheapest retriever you can add to a table that already holds embeddings.

SELECT pg_size_pretty(pg_relation_size('chunks_tsv_gin'))     AS gin_index,
       pg_size_pretty(sum(pg_column_size(tsv))::bigint)      AS tsvectors,
       pg_size_pretty(sum(pg_column_size(content))::bigint)  AS text
FROM chunks;

One last measurement worth taking before you ship the fusion query: time each branch separately. The lexical branch and the vector branch run sequentially inside that one statement, so its latency is their sum plus the join. If the lexical half is taking 200 ms because a common term matches half the corpus, the fix is in the tsquery and the match constraint, and no amount of tuning the vector index will show up in the total.