The pattern: take the top 20 from a full-text query and the top 20 from a vector query, give every row 1 / (60 + rank) from each list it appears in, and sort by the sum. That's reciprocal rank fusion (RRF) — no score normalisation, two indexes, one SQL statement.
Setup: one table, two indexes#
We loaded the first 100,000 DBpedia abstracts and embedded them locally with the open BAAI/bge-small-en-v1.5 model (384 dimensions, via fastembed) so that any text query can be embedded on the spot. The full-text column is generated by Postgres itself:
CREATE TABLE articles (
id bigint PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
embedding vector(384) NOT NULL,
tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED
);
CREATE INDEX ON articles USING gin (tsv);
CREATE INDEX ON articles USING hnsw (embedding vector_cosine_ops);| Relation | Size | Build |
|---|---|---|
| Table (100K rows, incl. text and TOAST) | 471 MB | — |
GIN index on tsv | 29 MB | 1.7 s |
HNSW index on embedding (384 dims) | 195 MB | 22 s |
Query vectors are stored in a small queries table (embedded with the same model) so the SQL below is runnable as-is.
Where each method wins: three real queries#
Same table, same query text, two retrieval methods. These are unedited results from our server.
“moons of Saturn” — vector search understands the topic#
Full-text search needs both stemmed words, moon and saturn, in the abstract. It ranked Titan first and then a run of craters and regions on Titan. Vector search returned moons — including Bebhionn and Narvi, whose abstracts are mostly catalogue designations.
hybrid=# SELECT title, round(ts_rank_cd(tsv, query)::numeric, 3) AS rank
hybrid-# FROM articles, websearch_to_tsquery('english', 'moons of Saturn') query
hybrid-# WHERE tsv @@ query
hybrid-# ORDER BY ts_rank_cd(tsv, query) DESC
hybrid-# LIMIT 8;
title | rank
-------------------+-------
Titan (moon) | 0.169
Leto Regio | 0.050
Salih (crater) | 0.050
Dalilah (crater) | 0.050
Adiri (Titan) | 0.050
Ithaca Chasma | 0.050
Epimetheus (moon) | 0.014
Narvi (moon) | 0.014
(8 rows)hybrid=# SELECT title, round((embedding <=> (SELECT embedding FROM queries WHERE q = 'moons of Saturn'))::numeric, 3) AS distance
hybrid-# FROM articles
hybrid-# ORDER BY embedding <=> (SELECT embedding FROM queries WHERE q = 'moons of Saturn')
hybrid-# LIMIT 8;
title | distance
-------------------+----------
Titan (moon) | 0.218
Bebhionn (moon) | 0.261
Epimetheus (moon) | 0.273
Moon | 0.291
Narvi (moon) | 0.296
Leto Regio | 0.300
Iapetus (moon) | 0.300
Xenophon (crater) | 0.301
(8 rows)“SS-243” — keyword search nails identifiers#
Hull numbers, SKUs, error codes and names are where embeddings struggle: to the model, “SS-243” is just a ship-shaped token. Full-text search found the one article that contains it; vector search put a tanker and a cruiser first.
hybrid=# SELECT title, round(ts_rank_cd(tsv, query)::numeric, 3) AS rank
hybrid-# FROM articles, websearch_to_tsquery('english', 'SS-243') query
hybrid-# WHERE tsv @@ query
hybrid-# ORDER BY ts_rank_cd(tsv, query) DESC
hybrid-# LIMIT 5;
title | rank
--------------------+-------
USS Bream (SS-243) | 0.100
(1 row)hybrid=# SELECT title, round((embedding <=> (SELECT embedding FROM queries WHERE q = 'SS-243'))::numeric, 3) AS distance
hybrid-# FROM articles
hybrid-# ORDER BY embedding <=> (SELECT embedding FROM queries WHERE q = 'SS-243')
hybrid-# LIMIT 5;
title | distance
-----------------------------------------+----------
SS Petersburg (T-AOT-9101) | 0.326
USS Cowpens (CG-63) | 0.353
USS Mariano G. Vallejo (SSBN-658) | 0.358
USS George Washington Carver (SSBN-656) | 0.359
9K720 Iskander | 0.360
(5 rows)“jazz trumpet player from New Orleans” — keyword search is precise but narrow#
Every word must match, so only two abstracts qualified. Articles that say “trumpeter” instead of “trumpet player” (Tommy Ladnier) or never mention New Orleans in those words are invisible to it. Vector search returned a broader list of jazz musicians — five of its eight are trumpeters, including Louis Armstrong, though John Handy plays saxophone — but it missed Bunk Johnson, the keyword search's top hit.
hybrid=# SELECT title, round(ts_rank_cd(tsv, query)::numeric, 3) AS rank
hybrid-# FROM articles, websearch_to_tsquery('english', 'jazz trumpet player from New Orleans') query
hybrid-# WHERE tsv @@ query
hybrid-# ORDER BY ts_rank_cd(tsv, query) DESC
hybrid-# LIMIT 8;
title | rank
----------------+-------
Bunk Johnson | 0.100
Percy Humphrey | 0.036
(2 rows)hybrid=# SELECT title, round((embedding <=> (SELECT embedding FROM queries WHERE q = 'jazz trumpet player from New Orleans'))::numeric, 3) AS distance
hybrid-# FROM articles
hybrid-# ORDER BY embedding <=> (SELECT embedding FROM queries WHERE q = 'jazz trumpet player from New Orleans')
hybrid-# LIMIT 8;
title | distance
--------------------------+----------
John Handy | 0.288
Walter Payton (musician) | 0.289
Percy Humphrey | 0.300
Chase Sanborn | 0.305
Sonny Landreth | 0.310
Tommy Ladnier | 0.310
Woody Shaw | 0.315
Louis Armstrong | 0.317
(8 rows)Across all 30 test queries, the keyword and vector top-10 lists shared only 2.2 results on average, and the keyword side returned just 5.9 rows per LIMIT 10 query because all words must match. The two methods find different things, which is exactly why combining them works.
Reciprocal rank fusion in one SQL statement#
RRF ignores the raw scores — cosine distances and ts_rank_cd values aren't comparable — and uses only each row's position in each list. A row's score is the sum of 1 / (k + rank) over the lists it appears in, with k = 60 by convention. Appearing in both lists beats being near the top of one.
hybrid=# WITH semantic AS (
hybrid-# SELECT id, row_number() OVER (ORDER BY embedding <=> (SELECT embedding FROM queries WHERE q = 'moons of Saturn')) AS rank
hybrid-# FROM articles
hybrid-# ORDER BY embedding <=> (SELECT embedding FROM queries WHERE q = 'moons of Saturn')
hybrid-# LIMIT 20
hybrid-# ),
hybrid-# keyword AS (
hybrid-# SELECT id, row_number() OVER (ORDER BY ts_rank_cd(tsv, query) DESC) AS rank
hybrid-# FROM articles, websearch_to_tsquery('english', 'moons of Saturn') query
hybrid-# WHERE tsv @@ query
hybrid-# ORDER BY ts_rank_cd(tsv, query) DESC
hybrid-# LIMIT 20
hybrid-# )
hybrid-# SELECT a.title,
hybrid-# round(COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + k.rank), 0), 4) AS rrf_score,
hybrid-# s.rank AS semantic_rank, k.rank AS keyword_rank
hybrid-# FROM semantic s
hybrid-# FULL OUTER JOIN keyword k USING (id)
hybrid-# JOIN articles a USING (id)
hybrid-# ORDER BY rrf_score DESC
hybrid-# LIMIT 10;
title | rrf_score | semantic_rank | keyword_rank
-------------------+-----------+---------------+--------------
Titan (moon) | 0.0328 | 1 | 1
Leto Regio | 0.0313 | 6 | 2
Epimetheus (moon) | 0.0308 | 3 | 7
Bebhionn (moon) | 0.0306 | 2 | 9
Narvi (moon) | 0.0301 | 5 | 8
Salih (crater) | 0.0298 | 12 | 3
Iapetus (moon) | 0.0290 | 7 | 11
Ithaca Chasma | 0.0285 | 15 | 6
Moon | 0.0156 | 4 |
Dalilah (crater) | 0.0156 | | 4
(10 rows)The identifier query shows the effect best. USS Bream was 8th by vector distance and 1st by keyword, so it scored twice and jumped to the top; rows that only one method found follow:
hybrid=# WITH semantic AS (
hybrid-# SELECT id, row_number() OVER (ORDER BY embedding <=> (SELECT embedding FROM queries WHERE q = 'SS-243')) AS rank
hybrid-# FROM articles
hybrid-# ORDER BY embedding <=> (SELECT embedding FROM queries WHERE q = 'SS-243')
hybrid-# LIMIT 20
hybrid-# ),
hybrid-# keyword AS (
hybrid-# SELECT id, row_number() OVER (ORDER BY ts_rank_cd(tsv, query) DESC) AS rank
hybrid-# FROM articles, websearch_to_tsquery('english', 'SS-243') query
hybrid-# WHERE tsv @@ query
hybrid-# ORDER BY ts_rank_cd(tsv, query) DESC
hybrid-# LIMIT 20
hybrid-# )
hybrid-# SELECT a.title,
hybrid-# round(COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + k.rank), 0), 4) AS rrf_score,
hybrid-# s.rank AS semantic_rank, k.rank AS keyword_rank
hybrid-# FROM semantic s
hybrid-# FULL OUTER JOIN keyword k USING (id)
hybrid-# JOIN articles a USING (id)
hybrid-# ORDER BY rrf_score DESC
hybrid-# LIMIT 5;
title | rrf_score | semantic_rank | keyword_rank
-----------------------------------------+-----------+---------------+--------------
USS Bream (SS-243) | 0.0311 | 8 | 1
SS Petersburg (T-AOT-9101) | 0.0164 | 1 |
USS Cowpens (CG-63) | 0.0161 | 2 |
USS Mariano G. Vallejo (SSBN-658) | 0.0159 | 3 |
USS George Washington Carver (SSBN-656) | 0.0156 | 4 |
(5 rows)Both halves use their own index#
The plan runs an HNSW index scan for the vector half and a bitmap scan of the GIN index for the keyword half, then joins the two short lists by primary key. Nothing touches all 100,000 rows:
hybrid=# EXPLAIN (COSTS OFF)
hybrid-# WITH semantic AS (
hybrid-# SELECT id, row_number() OVER (ORDER BY embedding <=> (SELECT embedding FROM queries WHERE q = 'moons of Saturn')) AS rank
hybrid-# FROM articles
hybrid-# ORDER BY embedding <=> (SELECT embedding FROM queries WHERE q = 'moons of Saturn')
hybrid-# LIMIT 20
hybrid-# ),
hybrid-# keyword AS (
hybrid-# SELECT id, row_number() OVER (ORDER BY ts_rank_cd(tsv, query) DESC) AS rank
hybrid-# FROM articles, websearch_to_tsquery('english', 'moons of Saturn') query
hybrid-# WHERE tsv @@ query
hybrid-# ORDER BY ts_rank_cd(tsv, query) DESC
hybrid-# LIMIT 20
hybrid-# )
hybrid-# SELECT a.title
hybrid-# FROM semantic s
hybrid-# FULL OUTER JOIN keyword k USING (id)
hybrid-# JOIN articles a USING (id)
hybrid-# LIMIT 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Limit
-> Nested Loop
-> Hash Full Join
Hash Cond: (articles.id = k.id)
-> Limit
InitPlan 1
-> Seq Scan on queries
Filter: (q = 'moons of Saturn'::text)
-> Index Scan using articles_embedding_idx on articles
Order By: (embedding <=> (InitPlan 1).col1)
-> Hash
-> Subquery Scan on k
-> Limit
-> Sort
Sort Key: (ts_rank_cd(articles_1.tsv, '''moon'' & ''saturn'''::tsquery)) DESC
-> Bitmap Heap Scan on articles articles_1
Recheck Cond: (tsv @@ '''moon'' & ''saturn'''::tsquery)
-> Bitmap Index Scan on articles_tsv_idx
Index Cond: (tsv @@ '''moon'' & ''saturn'''::tsquery)
-> Index Scan using articles_pkey on articles a
Index Cond: (id = COALESCE(articles.id, k.id))
(21 rows)How fast is it?#
Hybrid search latency, 100K Wikipedia abstracts
30 queries × 5 runs, one client. Query embedding is read from a table, so model time is excluded.
- p50
- p95
| Method | p50 | p95 | Rows returned (LIMIT 10) |
|---|---|---|---|
| Keyword only (GIN) | 0.23 ms | 0.83 ms | 5.9 |
| Vector only (HNSW) | 1.5 ms | 2.7 ms | 10.0 |
| Hybrid RRF (both) | 2.2 ms | 3.2 ms | 10.0 |
The hybrid query took 2.2 ms at the median — roughly the vector search (1.5 ms) plus the keyword search (0.23 ms) plus a little for the join. In a real application, embedding the query text usually costs more than the whole SQL query, so fusion is close to free.
Package it as a function#
Wrap the query in a SQL function so your application sends just the text and its embedding. This is the exact function we ran:
hybrid=# CREATE OR REPLACE FUNCTION hybrid_search(query_text text, query_embedding vector(384),
hybrid-# match_count int DEFAULT 10, rrf_k int DEFAULT 60)
hybrid-# RETURNS TABLE (id bigint, title text, score numeric)
hybrid-# LANGUAGE sql STABLE AS $$
hybrid-# WITH semantic AS (
hybrid-# SELECT a.id, row_number() OVER (ORDER BY a.embedding <=> query_embedding) AS rank
hybrid-# FROM articles a
hybrid-# ORDER BY a.embedding <=> query_embedding
hybrid-# LIMIT match_count * 2
hybrid-# ),
hybrid-# keyword AS (
hybrid-# SELECT a.id, row_number() OVER (ORDER BY ts_rank_cd(a.tsv, q) DESC) AS rank
hybrid-# FROM articles a, websearch_to_tsquery('english', query_text) q
hybrid-# WHERE a.tsv @@ q
hybrid-# ORDER BY ts_rank_cd(a.tsv, q) DESC
hybrid-# LIMIT match_count * 2
hybrid-# )
hybrid-# SELECT a.id, a.title,
hybrid-# round(COALESCE(1.0 / (rrf_k + s.rank), 0) + COALESCE(1.0 / (rrf_k + kw.rank), 0), 4)
hybrid-# FROM semantic s
hybrid-# FULL OUTER JOIN keyword kw USING (id)
hybrid-# JOIN articles a USING (id)
hybrid-# ORDER BY 3 DESC
hybrid-# LIMIT match_count
hybrid-# $$;
CREATE FUNCTION
hybrid=# SELECT * FROM hybrid_search('jazz trumpet player from New Orleans',
hybrid-# (SELECT embedding FROM queries WHERE q = 'jazz trumpet player from New Orleans'), 6);
id | title | score
-------+--------------------------+--------
93266 | Percy Humphrey | 0.0320
93208 | Bunk Johnson | 0.0164
57413 | John Handy | 0.0164
14123 | Walter Payton (musician) | 0.0161
99668 | Chase Sanborn | 0.0156
72746 | Sonny Landreth | 0.0154
(6 rows)Percy Humphrey, found by both methods, scores about twice as high as anything found by one. Bunk Johnson (keyword only) and John Handy (vector only) tie at 1 / 61.
Tuning that matters#
- Candidates per side. We take
match_count × 2from each method. More candidates let a row ranked lower on one side still be rescued by the other, at the cost of a bigger HNSW scan. Keep the vector candidates at or belowhnsw.ef_search(40 by default) or raiseef_searchto match — an HNSW scan returns at mostef_searchrows unless iterative scans are on. - Weights. To favour one side, multiply its term:
0.7 / (60 + s.rank) + 0.3 / (60 + kw.rank). Tune on real queries with known good answers rather than by eye. - Query parsing.
websearch_to_tsqueryrequires all words (AND) and understands quotes,ORand-word. For natural-language questions an AND query often returns nothing; build an OR query instead (for example by replacing&with|in the output ofplainto_tsquery) and let RRF sort it out. - Language. The
'english'configuration stems words and drops stop words. Use the right configuration per language, or'simple'for codes and identifiers. - Filters. Add the same
WHEREclause to both CTEs. The vector half then has the usual filtered-HNSW pitfalls.
What about BM25?#
PostgreSQL's built-in ts_rank and ts_rank_cd are not BM25: they don't weight terms by how rare they are across the table. For RRF this matters less than you might expect, because only the order within the keyword list counts. If you want true BM25 ranking, extensions provide it: ParadeDB's pg_search and Tiger Data's pg_textsearch add BM25 indexes that sit alongside pgvector. Check that your managed provider offers them before you depend on one.
Reproduce it: the loader and the latency benchmark are h1_hybrid.py and h2_queries.py; raw numbers are in hybrid.json.
Frequently asked questions
What is hybrid search in pgvector?
Running a PostgreSQL full-text search (tsvector + GIN index) and a pgvector nearest-neighbour search for the same query, then merging the two ranked lists — most simply with reciprocal rank fusion (RRF). Each half uses its own index, so the combined query stays fast: 2.2 ms median in our test.
Does Postgres have BM25?
Not built in. Postgres ranks full-text matches with ts_rank or ts_rank_cd, which work well for hybrid search because RRF only uses rank positions, not scores. Extensions such as ParadeDB's pg_search and Tiger Data's pg_textsearch add BM25 if you need it.
What k should I use in reciprocal rank fusion?
60 is the conventional constant from the original RRF paper and works well as a default. Smaller k gives more weight to top-ranked results. Tune it — and the number of candidates from each side — on queries from your own application.