Hybrid Search in Postgres: pgvector, Full-Text, and RRF

A developer searches your knowledge base for "Fix CVE-2024-38077 Windows Netlogon RPC buffer overflow" and gets back three general security advisories. The actual patch document ranks fourth.

That is not a tuning problem. It is the predictable failure mode of a retrieval stack built on cosine similarity alone.

Where pure vector search breaks

Most RAG tutorials stop at a naive vector lookup: embed the text, store it in Pinecone or Chroma, run cosine similarity. Dense embeddings are strong on semantic similarity and consistently weak on exact keywords, acronyms, product SKUs, UUIDs, and domain-specific error codes.

The CVE query shows both halves of the problem. Dense search understands the general concept of Windows vulnerabilities and returns advisories while often missing the exact patch document. Sparse keyword search (BM25) locks onto the literal token CVE-2024-38077 but misses related documentation that says "Netlogon remote elevation vulnerability" instead.

The same split shows up in ordinary support queries. On a search for "JWT token expired after password reset", vector search surfaces documents about authentication sessions and credential expiry even when those exact words are absent, while full-text search strongly matches the documents containing the typed terms.

Reciprocal Rank Fusion, in one number

Hybrid search runs both pipelines concurrently and merges the two candidate rankings with Reciprocal Rank Fusion. The score for a document is the sum, across retrieval methods, of 1 / (60 + rank).

The constant 60 is the part worth understanding: it is a smoothing term that stops top-heavy outliers from dominating the merged result set. A document ranked first by one method does not automatically bury a document ranked third by both.

Because RRF operates on ranks rather than scores, you never have to normalize a cosine distance against a ts_rank_cd value — which is where most hand-rolled blending attempts go wrong.

You probably do not need a second database

PostgreSQL handles dense vector embeddings and sparse full-text search inside a single atomic ACID transaction. Three schema decisions carry most of the weight, per the guide:

• A TSVECTOR column generated and stored from to_tsvector('english', title || ' ' || content), so the sparse index stays in sync with writes automatically
• An HNSW index on the embedding column, created with m = 16 and ef_construction = 64
• A GIN index on the generated search vector for the full-text side

The retrieval itself is one query: two CTEs that each rank 50 candidates — one ordered by embedding distance, one by ts_rank_cd over a plainto_tsquery — joined with a FULL OUTER JOIN, summed into a fusion score, and cut to the top 10.

(COALESCE(1.0 / (60 + d.d_rank), 0.0)
 + COALESCE(1.0 / (60 + s.s_rank), 0.0)) AS fusion_score

The COALESCE calls are what make the full outer join safe: a document found by only one of the two methods still scores, instead of dropping out.

Four things to add before production

The guide lists four guardrails to enforce before this reaches real users: parameterize tenant IDs at the database connection level for strict row-level security; budget token usage dynamically with tiktoken so retrieved context cannot overflow the window; add a cross-encoder re-ranking pass for legal or medical data, using something like cohere.rerank or bge-reranker-large; and measure output faithfulness against the retrieved chunks with an automated LLM-as-a-judge pass.

Where to start

Pull the twenty worst queries from your search logs — the ones with error codes, SKUs, or version strings in them. Run them through your current vector-only retrieval and record where the correct document ranks.

If it consistently lands outside the top three, hybrid search will move it, and you can test that with an index and a single SQL query before adding any new infrastructure.