Hybrid search for RAG with Postgres full-text search and pgvector

Vector search is good at meaning. Ask "how do I cancel my plan" and it finds a passage titled "Ending your subscription", even though the two share no words. It is much weaker at exact terms: an error code like E4012, a product SKU, a function name, or a person's surname. Embeddings blur those into "something that looks like an identifier".

Keyword search has the opposite profile. Running both and merging the results, called hybrid search, covers each one's blind spot. With PostgreSQL you can do it in one query, on the table from the pgvector RAG post.

Add full-text search to the table

PostgreSQL's built-in full-text search works on tsvector values. A generated column keeps it in sync with content automatically, and a GIN index makes it fast:

ALTER TABLE document_chunks
  ADD COLUMN content_tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;
 
CREATE INDEX ON document_chunks USING gin (content_tsv);

The 'english' configuration handles stemming ("cancelling" matches "cancel") and drops stop words. Use the configuration that matches your content's language, or 'simple' if you have mixed languages and mostly care about exact tokens.

Why not just add the scores?

The two searches score on incompatible scales. Cosine distance sits between 0 and 2; ts_rank_cd returns small positive numbers that depend on document length and term frequency. Adding or averaging them lets whichever scale is larger dominate.

Reciprocal rank fusion (RRF) sidesteps the problem by ignoring raw scores and using only each result's position in its list:

score(chunk) = sum over lists of 1 / (k + rank in that list)

k is a smoothing constant; 60 is the value from the original paper and a reasonable default. A chunk ranked first by both searches scores highest; a chunk that appears in only one list still gets credit, but less.

The hybrid query

WITH semantic AS (
  SELECT id,
         ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS rank
    FROM document_chunks
   ORDER BY embedding <=> $1
   LIMIT 20
),
keyword AS (
  SELECT id,
         ROW_NUMBER() OVER (ORDER BY ts_rank_cd(content_tsv, query) DESC) AS rank
    FROM document_chunks,
         websearch_to_tsquery('english', $2) AS query
   WHERE content_tsv @@ query
   ORDER BY ts_rank_cd(content_tsv, query) DESC
   LIMIT 20
)
SELECT c.id,
       c.content,
       COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + k.rank), 0) AS score
  FROM semantic s
  FULL OUTER JOIN keyword k ON s.id = k.id
  JOIN document_chunks c ON c.id = COALESCE(s.id, k.id)
 ORDER BY score DESC
 LIMIT 5;

How it fits together:

• semantic takes the 20 nearest chunks by vector distance, numbered 1 to 20.
• keyword takes the 20 best full-text matches. websearch_to_tsquery accepts user-style input, so quotes, or, and -exclusions work the way people expect, and it never throws on odd punctuation.
• FULL OUTER JOIN keeps chunks found by only one of the two searches; COALESCE(..., 0) gives them zero credit from the list they are missing from.
• The final ORDER BY score returns the five best fused results for the prompt.

Calling it from TypeScript is the same as before, with the raw question as the second parameter:

const [queryEmbedding] = await embed([question], "query")
 
const { rows } = await pool.query(HYBRID_SEARCH_SQL, [
  JSON.stringify(queryEmbedding),
  question,
])

Tuning

• Candidate depth. Each list keeps 20 candidates. Larger values give fusion more to work with at the cost of more work per query; 20 to 50 is a common range.
• Weighting. If one signal matters more for your data, multiply its term, for example 1.5 * COALESCE(1.0 / (60 + k.rank), 0) to favor exact matches in a codebase or API reference.
• Filtering. Apply tenant or permission filters inside both CTEs. Filtering only after fusion can return fewer results than requested, and filtering only one side leaks.
• Reranking. For the highest quality, take the top 20 to 30 fused results and reorder them with a dedicated reranking model before choosing the final five. It costs one extra call and often improves the passages the model actually sees.

When to reach for a dedicated search engine

Postgres hybrid search holds up well into millions of chunks with the right indexes, and keeping everything in one database removes a whole class of sync bugs. Consider a dedicated engine when you need features Postgres full-text search lacks, such as typo tolerance, faceting across many fields, or language-aware ranking at very large scale. Until then, one database and one query is the simpler system to operate.