Hybrid search: keywords and vectors together

conceptThe concept

Run the two retrievers separately, each returning its own ranked top N, then combine. Reciprocal rank fusion (RRF) scores each document by sum(1 / (k + rank)) over the lists it appears in, with k usually 60. It ignores the raw scores, which are on incomparable scales, and rewards documents that rank well in either list or moderately in both.

Full-text search in PostgreSQL: to_tsvector on the content (ideally a stored generated column with a GIN index) and websearch_to_tsquery or plainto_tsquery for the query. For hybrid recall, an OR query over the words often works better than the default AND.

Filters such as audience, tenant, language and freshness belong in both retrievers' WHERE clauses, so neither leg returns rows the user may not see.

whyWhy it matters

Users mix styles: "charged twice" is semantic, "invoice INV-2291" is exact. Hybrid search handles both with one query and measurably improves retrieval quality for RAG compared with either method alone.

Use caseReal-world scenario

A support assistant answers from the help centre. Hybrid retrieval returns the five best chunks for the question, restricted to customer-facing articles, and passes them to the language model as context.

Try itExamples

Run and edit these in the interactive lesson.

SELECT a.title, c.chunk_no,
       round(ts_rank_cd(to_tsvector('english', c.content), q)::numeric, 3) AS rank
FROM kb_chunks c
JOIN kb_articles a ON a.id = c.article_id,
     websearch_to_tsquery('english', 'duplicate payment') q
WHERE to_tsvector('english', c.content) @@ q
ORDER BY rank DESC
LIMIT 5;

SELECT a.title, c.chunk_no,
       round((c.embedding <=> pgm.embed('duplicate payment on my card'))::numeric, 3) AS distance
FROM kb_chunks c
JOIN kb_articles a ON a.id = c.article_id
ORDER BY c.embedding <=> pgm.embed('duplicate payment on my card')
LIMIT 5;

WITH q AS (
  SELECT replace(plainto_tsquery('english', 'duplicate payment on my card')::text, '&', '|')::tsquery AS tsq,
         pgm.embed('duplicate payment on my card') AS emb
),
keyword AS (
  SELECT c.id, rank() OVER (ORDER BY ts_rank_cd(to_tsvector('english', c.content), q.tsq) DESC) AS r
  FROM kb_chunks c, q
  WHERE to_tsvector('english', c.content) @@ q.tsq
  ORDER BY r
  LIMIT 20
),
semantic AS (
  SELECT c.id, rank() OVER (ORDER BY c.embedding <=> q.emb) AS r
  FROM kb_chunks c, q
  ORDER BY r
  LIMIT 20
)
SELECT a.title, k.chunk_no,
       round(coalesce(1.0 / (60 + kw.r), 0) + coalesce(1.0 / (60 + s.r), 0), 4) AS rrf_score,
       kw.r AS keyword_rank, s.r AS semantic_rank
FROM keyword kw
FULL JOIN semantic s ON s.id = kw.id
JOIN kb_chunks k ON k.id = coalesce(kw.id, s.id)
JOIN kb_articles a ON a.id = k.article_id
ORDER BY rrf_score DESC
LIMIT 5;

SELECT a.title, a.audience,
       round((c.embedding <=> pgm.embed('refund approval for an agent'))::numeric, 3) AS distance
FROM kb_chunks c
JOIN kb_articles a ON a.id = c.article_id
WHERE a.audience = 'customer'
ORDER BY c.embedding <=> pgm.embed('refund approval for an agent')
LIMIT 3;

internalsUnder the hood

Each leg can use its own index: a GIN index on the tsvector for keywords and an HNSW index for vectors. Because both legs are LIMITed before the fusion, the expensive part stays bounded no matter how large the table is.

Avoid / preferDo this, not that

SELECT id, 0.5 * ts_rank(tsv, q) + 0.5 * (1 - (embedding <=> $1)) AS score
FROM kb_chunks, q
ORDER BY score DESC LIMIT 5;

-- rank each leg separately with its own index and LIMIT,
-- then fuse ranks with 1 / (60 + rank)

-- retrieve top 5, then drop internal articles in application code

-- WHERE audience = 'customer' (or row-level security) in both legs

PitfallsCommon mistakes

• Computing to_tsvector on the fly at scale instead of a stored generated column with a GIN index.
• Using the default AND semantics for long natural-language questions, so the keyword leg returns nothing.
• Tuning fusion weights on a handful of queries instead of an evaluation set.

LimitsWhen not to use it

If queries are always exact identifiers, use B-tree lookups; if they are always natural-language questions over clean text, a single vector leg may be enough. Add hybrid when evaluation shows each method missing different relevant results.

SecuritySecurity notes

Row-level security applies to both legs automatically if the application connects as a role subject to the policy. That is safer than repeating a hand-written filter in every query, because a forgotten filter is a data leak.

ChallengeTry it yourself

SELECT title, round(best::numeric, 3) AS distance
FROM (
  SELECT DISTINCT ON (a.id) a.id, a.title,
         c.embedding <=> pgm.embed('battery died, want a repair under warranty') AS best
  FROM kb_chunks c
  JOIN kb_articles a ON a.id = c.article_id
  WHERE a.audience = 'customer'
  ORDER BY a.id, best
) t
ORDER BY best
LIMIT 3;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Full text search
• pgvector filtering and iterative scans