Hybrid search in PostgreSQL: combine pgvector and full-text search with reciprocal rank fusion in one SQL query

#The problem

A RAG assistant over IT runbooks gets the question "what does ORA-01555 mean?". Pure vector search returns chunks about "Oracle errors" in general, because the embedding doesn't weigh the exact code much. Keyword search finds the right chunk immediately, but fails on "why does my long report fail with snapshot errors?".

Hybrid search runs both and merges the results. If your vectors are already in PostgreSQL, you don't need a second search engine for this.

#Step 1: add a full-text column and index

ALTER TABLE doc_chunks
  ADD COLUMN content_tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || content)) STORED;

CREATE INDEX CONCURRENTLY doc_chunks_tsv_gin ON doc_chunks USING gin (content_tsv);

Notes:

• Adding a stored generated column rewrites the table. On a large table, add a normal column, backfill in batches and maintain it in the ingestion code instead.
• The english configuration stems words ("failing" → "fail"). Error codes and identifiers survive as tokens, but check how your codes tokenize: SELECT to_tsvector('english', 'ORA-01555 KB5034441');.
• For mixed-language content, use the simple configuration or one column per language.

#Step 2: run both searches and fuse

Reciprocal rank fusion (RRF) scores each document by 1 / (k + rank) in each result list and sums them. It ignores the raw scores, which is the point: cosine distances and ts_rank values aren't comparable, but ranks are.

WITH semantic AS (
  SELECT id, row_number() OVER (ORDER BY embedding <=> $1) AS rank
  FROM (
    SELECT id, embedding FROM doc_chunks
    ORDER BY embedding <=> $1
    LIMIT 40
  ) s
),
keyword AS (
  SELECT id, row_number() OVER (ORDER BY ts_rank_cd(content_tsv, q) DESC) AS rank
  FROM doc_chunks, websearch_to_tsquery('english', $2) AS q
  WHERE content_tsv @@ q
  ORDER BY ts_rank_cd(content_tsv, q) DESC
  LIMIT 40
)
SELECT c.id, c.title, c.content,
       coalesce(1.0 / (60 + s.rank), 0) + coalesce(1.0 / (60 + k.rank), 0) AS rrf_score
FROM semantic s
FULL OUTER JOIN keyword k ON k.id = s.id
JOIN doc_chunks c ON c.id = coalesce(s.id, k.id)
ORDER BY rrf_score DESC
LIMIT 10;

• $1 is the query embedding (vector), $2 the raw user question.
• The inner ORDER BY embedding <=> $1 LIMIT 40 keeps the HNSW index in use; the window function runs on only 40 rows.
• websearch_to_tsquery accepts user-style input (quotes, -exclude, or) without syntax errors.
• 60 is the usual RRF constant; it dampens the advantage of the very top ranks.

#Step 3: check both halves use indexes

EXPLAIN (ANALYZE) <the query above with literal values>;

You should see an Index Scan using doc_chunks_emb_hnsw and a Bitmap Index Scan on doc_chunks_tsv_gin. If the keyword side does a sequential scan, the GIN index is missing or the tsvector expression in the query doesn't match the column.

#Step 4: weight the two sides

Some question types are keyword-heavy (codes, product names), others semantic. Add weights:

SELECT ...,
       0.7 * coalesce(1.0 / (60 + s.rank), 0) + 0.3 * coalesce(1.0 / (60 + k.rank), 0) AS rrf_score

Pick weights with an evaluation set, not by feel:

Try vector only, keyword only and a few weightings. Keep the one that wins on both slices, not just the average.

#Step 5: filters and tenants

Apply the same filter on both CTEs, or a tenant can leak into the other half:

... FROM doc_chunks WHERE tenant_id = $3 ORDER BY embedding <=> $1 LIMIT 40 ...
... WHERE tenant_id = $3 AND content_tsv @@ q ...

A composite GIN index isn't possible on int plus tsvector without the btree_gin extension; with it you can index both together:

CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE INDEX CONCURRENTLY ON doc_chunks USING gin (tenant_id, content_tsv);

#Step 6: add a reranker (optional)

RRF gets the right chunks into the top 20 more often; a cross-encoder reranker then puts the best one at position 1. Fetch 20–30 fused results from SQL and rerank them in the application. This adds latency (tens to hundreds of milliseconds depending on model and hardware), so measure it.

#When not to do this in PostgreSQL

ts_rank_cd is not BM25. It doesn't use document frequency across the corpus, so very common terms aren't down-weighted as much. For many internal corpora it works well enough; measure before adding another system.

#Prevent problems next time

• Keep an evaluation set and re-run it whenever you change chunking, the embedding model or weights.
• Test tokenisation of your domain identifiers before relying on keyword search for them.
• Apply security filters in both halves of the query and test with a second tenant.
• Log which half produced each returned chunk; it shows you when one side stops contributing.