Recommended Free Tools
Combine PostgreSQL full-text search with pgvector by retrieving a bounded candidate list from each, ranking candidates within each branch, then summing a reciprocal-rank contribution per document. This approach—often called Reciprocal Rank Fusion (RRF)—joins lexical and semantic results without comparing their raw scores, which have different meanings and scales.
How hybrid retrieval works in PostgreSQL
The lexical branch uses PostgreSQL full-text search: a tsvector document representation is matched against a tsquery, commonly with the @@ operator, and can be ordered with ts_rank_cd. The semantic branch searches vector embeddings with pgvector. Each branch produces its own ranked list of document IDs.
RRF combines those lists by rank rather than by raw score. For a document appearing in a branch at rank r, that branch contributes 1 / (k + r) to its fused score; contributions are added across branches. A document found by only one branch can still rank in the final list. pgvector’s hybrid-search guidance identifies RRF and cross-encoder reranking as ways to combine full-text and vector search results: pgvector hybrid search.
A single-statement RRF query
This illustrative SQL shape retrieves candidates from each branch, assigns ranks, and fuses them by document ID:
#1 Best Overall
WITH
lexical AS (
SELECT id,
row_number() OVER (
ORDER BY ts_rank_cd(textsearch, query) DESC, id
) AS rank
FROM documents,
websearch_to_tsquery('english', $1) AS query
WHERE textsearch @@ query
ORDER BY ts_rank_cd(textsearch, query) DESC, id
LIMIT $2
),
semantic AS (
SELECT id,
row_number() OVER (
ORDER BY embedding <=> $3::vector, id
) AS rank
FROM documents
ORDER BY embedding <=> $3::vector, id
LIMIT $4
),
ranked AS (
SELECT id, rank, 'lexical' AS branch FROM lexical
UNION ALL
SELECT id, rank, 'semantic' AS branch FROM semantic
)
SELECT id,
sum(1.0 / (60 + rank)) AS rrf_score
FROM ranked
GROUP BY id
ORDER BY rrf_score DESC, id
LIMIT $5;The parameters here represent the text query ($1), lexical candidate limit ($2), query embedding ($3), semantic candidate limit ($4), and final result limit ($5). The 60 in the score expression is an example constant, not a universally optimal setting. The query is a teaching outline, not a tested or universal prescription. PostgreSQL documents text-search operators and ranking functions in its text-search functions and operators reference; pgvector documents vector operators, indexing, and hybrid search in its project README.
Choices to make before using the pattern
Text-search configuration
The example uses websearch_to_tsquery('english', $1). Select a text-search configuration and query-construction function appropriate to your language and input. The stored tsvector should be built with the intended configuration; PostgreSQL describes tsvector as an optimized document representation and tsquery as a query representation in its text-search types documentation.
Rank #2
Vector operator and index
The example orders by the pgvector <=> distance operator. Choose the distance operator and any index operator class to match the embedding and workload. pgvector documents available vector search and index methods, but the best selection depends on the application and version.
Candidate depth and filters
The per-branch limits determine which documents are even eligible for fusion. Small candidate pools can exclude useful results before RRF sees them; larger pools can increase work. There is no universally correct limit in the cited documentation. Decide candidate depth and where filters apply by testing representative searches against your corpus.
Rank constant, weighting, and ties
RRF’s constant and any branch weights influence how ranks contribute. The example uses a shared constant and equal branch weight. The secondary id ordering makes ties deterministic, but neither that constant nor equal weighting is established as optimal for every dataset. Tune only against judged relevance and application requirements.
Validate relevance and query behavior
A single SQL statement expresses the retrieval and fusion work together; it does not guarantee a particular query plan, index use, latency, or relevance level. Validate the actual PostgreSQL and pgvector versions, schema, data volume, filters, and hardware in your deployment.
- Compare lexical-only, vector-only, and fused results on representative queries with judged relevance.
- Include exact identifiers, names, and phrases as well as conceptually related queries whose wording differs from the stored documents.
- Vary the candidate limits and assess whether additional candidates improve results enough to justify their cost.
- Inspect the real plan and runtime with
EXPLAIN (ANALYZE, BUFFERS); confirm index behavior rather than inferring it from the SQL form. - If rank fusion is insufficient, consider tuned weights or a later reranking stage such as a cross-encoder.
PostgreSQL’s guidance on document/query preparation and ranking is available in Controlling Text Search. Neither PostgreSQL nor pgvector documentation establishes a workload-independent performance figure or best RRF configuration for this query shape.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
