Hybrid Search in PostgreSQL: pgvector Plus Full-Text, Fused and Measured

1. Why vector-only retrieval misses identifiers

A user pastes E10042 into your RAG assistant. The chunk that explains that exact error code exists, but pgvector returns five chunks about the same subsystem and none of them mention the code. Paraphrased questions work; questions built around error codes, version numbers and function names do not.

The Retrieval and RAG for Developers hub names the reason and the fix at concept level: embeddings match meaning rather than exact strings, so test hybrid search with reciprocal rank fusion against a vector-only baseline. This guide builds that inside the PostgreSQL database that already holds your vectors: a full-text column, a keyword query, one SQL function that fuses both lists, and a recall@10 comparison you can repeat on your own queries.

Full-text behaviour below comes from the documentation for PostgreSQL 18, the current stable major version (18.6 according to the project's versioning page, checked 2026-10-19; PostgreSQL 19 is still a release candidate). pgvector behaviour comes from the pgvector README for version 0.8.7, released 2026-10-01 according to its changelog.

2. Add a generated tsvector column and a GIN index

The PostgreSQL text search chapter describes two ways to index text: an expression index on to_tsvector(...), or a separate tsvector column kept current by a stored generated column. The separate column means queries do not have to repeat the configuration name to hit the index, and matches do not have to recompute to_tsvector. The cost is disk space for the stored vector.

ALTER TABLE chunks
  ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS
    (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

CREATE INDEX chunks_tsv_gin ON chunks USING GIN (tsv);

Three details carry weight. Write STORED explicitly: in PostgreSQL 18 a generated column is virtual by default. Keep the coalesce calls, because to_tsvector(NULL) returns NULL and one empty field would otherwise blank the whole row. Use the two-argument form with a fixed configuration such as 'english', so the indexed vectors do not depend on default_text_search_config. The documentation calls GIN the preferred text search index type: an inverted index with one entry per lexeme.

3. Check how your identifiers are tokenized

Keyword search can only rescue identifiers that survive parsing. The built-in parser recognizes 23 token types, among them numword (letters and digits, such as beta1), version (such as 8.3.0), hyphenated words with their parts, file paths and hosts. Characters it does not recognize as part of a token are treated as separators. Before you rely on the column, look at your own strings:

SELECT alias, token, lexemes
FROM ts_debug('english', 'ERR_SSL_PROTOCOL_ERROR after upgrade to 2.3.1 in pg_stat_statements');

Read the lexemes column. If an identifier is split into common pieces, a query for it will also match every chunk that contains those pieces, and the keyword list stops being precise. Collect 20 real identifiers from your logs and run them through ts_debug before going further; the fixture in section 6 prints this exact check first.

4. Write the keyword query and know what its rank means

For raw user input, use websearch_to_tsquery. The documentation states that it never raises syntax errors, which makes it usable on text you did not write. It supports quoted phrases, or and a leading minus for exclusion. Unquoted words are joined with &, which is the trap in RAG: a full question like “what does E10042 mean” requires every remaining word to appear in the same chunk. Send the identifier, or a shortened query, to the keyword side and the full question to the embedding side.

SELECT id, ts_rank_cd(tsv, q) AS rank
FROM chunks, websearch_to_tsquery('english', 'E10042 error') q
WHERE tsv @@ q
ORDER BY rank DESC, id
LIMIT 40;

ts_rank_cd computes cover density ranking, which also rewards matching lexemes that sit close together, and it needs positional information in the vector. It is not BM25. The documentation says plainly that the ranking functions do not use any global information, so there is no corpus-wide term weighting of the kind BM25 relies on. Normalization option 32 rescales ranks into zero to one, and the documentation calls that a cosmetic change that does not alter the ordering. That is exactly why fusing by score is awkward and fusing by rank is not.

5. Fuse both lists with reciprocal rank fusion

Reciprocal rank fusion was introduced by Cormack, Clarke and Büttcher at SIGIR 2009. Each document gets 1 / (k + rank) from every list it appears in, and the sums are sorted. The paper fixed k at 60 during a pilot run and kept it unchanged in later validation; its pilot table shows k = 60 as near-optimal, with the authors noting that the choice was not critical. Because only ranks enter the formula, a cosine distance and a cover density score never have to be put on the same scale.

CREATE FUNCTION hybrid_search(
  query_text text, query_embedding vector(32),
  match_count integer DEFAULT 10, rrf_k integer DEFAULT 60, candidates integer DEFAULT 40)
RETURNS TABLE (doc_id integer, rrf_score numeric, keyword_rank bigint, vector_rank bigint)
LANGUAGE sql STABLE AS $fn$
  WITH keyword AS (
    SELECT d.id, row_number() OVER (ORDER BY ts_rank_cd(d.tsv, q) DESC, d.id) AS r
    FROM docs d, websearch_to_tsquery('english', query_text) q
    WHERE d.tsv @@ q
    ORDER BY r
    LIMIT candidates
  ), semantic AS (
    SELECT s.id, row_number() OVER (ORDER BY s.dist, s.id) AS r
    FROM (
      SELECT d.id, d.embedding <=> query_embedding AS dist
      FROM docs d
      ORDER BY dist
      LIMIT candidates
    ) s
  )
  SELECT coalesce(k.id, s.id),
         coalesce(1.0 / (rrf_k + k.r), 0) + coalesce(1.0 / (rrf_k + s.r), 0),
         k.r, s.r
  FROM keyword k FULL OUTER JOIN semantic s ON s.id = k.id
  ORDER BY 2 DESC, 1
  LIMIT match_count
$fn$;

The FULL OUTER JOIN keeps documents found by only one side, and returning both ranks lets you see why a chunk made the cut. The vector side orders by the distance operator ascending with a LIMIT, the query shape the README requires for an HNSW index to be used. Change docs, the column names and vector(32) to match your table and embedding size. With an HNSW index in production, mind the candidate depth: the README says results are limited by hnsw.ef_search, 40 by default, so asking for more than 40 vector candidates needs a higher setting. Tuning that is the job of pgvector HNSW Index: Tune Recall and Query Speed.

6. Run the fixture

The fixture needs PostgreSQL with pgvector. It works inside a transaction and ends with ROLLBACK, so nothing persists; run it in a disposable database anyway. Save it as hybrid-fixture.sql and run:

psql -X --dbname=postgres --set=ON_ERROR_STOP=1 --file=hybrid-fixture.sql

It creates 2,000 short documents in eight topics, each with a unique code such as E10042, and 100 evaluation queries with one target document each: 50 identifier queries (the code plus “error”) and 50 paraphrase queries written in words that occur in no document. The vectors are synthetic and deliberately encode an assumption: an identifier query's embedding knows the topic but not the code, while a paraphrase query's embedding lies close to its target. Two sanity checks stop the run if the identifier text does not match exactly its target or a paraphrase matches anything. The script then fills a recall table, prints one fused result list and checks that the keyword query uses the GIN index.

\set ON_ERROR_STOP on
\pset pager off
SELECT version();
BEGIN;
CREATE EXTENSION IF NOT EXISTS vector;
SELECT extversion AS pgvector_version FROM pg_extension WHERE extname = 'vector';
CREATE SCHEMA hybrid_fixture;
SET LOCAL search_path = hybrid_fixture, public;
SELECT setseed(0.19);

-- 1. How the parser splits identifiers. Read this output for your own strings.
SELECT alias, token, lexemes
FROM ts_debug('english', 'ERR_SSL_PROTOCOL_ERROR after upgrade to 2.3.1 in pg_stat_statements');

-- 2. Eight topics. Documents use "phrase"; paraphrase queries use words that occur in no document.
CREATE TABLE topics (topic integer PRIMARY KEY, phrase text, paraphrase text, centroid float8[]);
INSERT INTO topics (topic, phrase, paraphrase) VALUES
  (1, 'connection pool exhausted', 'too many clients are queued'),
  (2, 'replication lag on standby', 'replica falls behind the primary'),
  (3, 'autovacuum worker cancelled', 'background cleanup stopped early'),
  (4, 'deadlock detected between transactions', 'two sessions block each other'),
  (5, 'disk quota reached', 'storage volume is full'),
  (6, 'certificate verification failed', 'secure handshake was rejected'),
  (7, 'out of memory during sort', 'ran short of RAM while ordering rows'),
  (8, 'lock timeout expired', 'statement gave up waiting for a row latch');
UPDATE topics SET centroid =
  (SELECT array_agg(random() - 0.5 ORDER BY d) FROM generate_series(1, 32) d WHERE topic > 0);

-- 3. 2,000 documents with a generated tsvector column and a GIN index.
CREATE TABLE doc_vectors AS
SELECT g AS id, ((g - 1) % 8) + 1 AS topic,
       (SELECT array_agg(t.centroid[d] + 0.3 * (random() - 0.5) ORDER BY d)
        FROM generate_series(1, 32) d) AS v
FROM generate_series(1, 2000) g
JOIN topics t ON t.topic = ((g - 1) % 8) + 1;

CREATE TABLE docs (
  id integer PRIMARY KEY,
  title text NOT NULL,
  body text NOT NULL,
  embedding vector(32) NOT NULL,
  tsv tsvector GENERATED ALWAYS AS
    (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED
);
INSERT INTO docs (id, title, body, embedding)
SELECT dv.id, 'E' || (10000 + dv.id), format('Error E%s: %s.', 10000 + dv.id, t.phrase), dv.v::vector(32)
FROM doc_vectors dv JOIN topics t USING (topic);
CREATE INDEX docs_tsv_gin ON docs USING GIN (tsv);
ANALYZE docs;

-- 4. Evaluation queries: 50 identifier queries, 50 paraphrase queries, one target each.
--    The synthetic vectors encode an assumption: an identifier query's embedding
--    knows the topic but not the code; a paraphrase query's embedding is close to its target.
CREATE TABLE eval_queries (qid integer PRIMARY KEY, kind text, target integer, query_text text, embedding vector(32));
INSERT INTO eval_queries
SELECT g, 'identifier', x.target, format('E%s error', 10000 + x.target),
       (SELECT array_agg(t.centroid[d] + 0.3 * (random() - 0.5) ORDER BY d)
        FROM generate_series(1, 32) d)::vector(32)
FROM generate_series(1, 50) g
CROSS JOIN LATERAL (SELECT ((g * 41) % 2000) + 1 AS target) x
JOIN doc_vectors dv ON dv.id = x.target
JOIN topics t ON t.topic = dv.topic;
INSERT INTO eval_queries
SELECT 100 + g, 'paraphrase', x.target, t.paraphrase,
       (SELECT array_agg(dv.v[d] + 0.05 * (random() - 0.5) ORDER BY d)
        FROM generate_series(1, 32) d)::vector(32)
FROM generate_series(1, 50) g
CROSS JOIN LATERAL (SELECT ((g * 43 + 7) % 2000) + 1 AS target) x
JOIN doc_vectors dv ON dv.id = x.target
JOIN topics t ON t.topic = dv.topic;

-- Fixture sanity: identifier text matches exactly its target; paraphrase text matches nothing.
DO $$ BEGIN
  IF EXISTS (
    SELECT 1 FROM eval_queries e
    WHERE e.kind = 'identifier'
      AND (SELECT array_agg(d.id) FROM docs d WHERE d.tsv @@ websearch_to_tsquery('english', e.query_text))
          IS DISTINCT FROM ARRAY[e.target])
  THEN RAISE EXCEPTION 'identifier keyword match is not unique'; END IF;
  IF EXISTS (
    SELECT 1 FROM eval_queries e JOIN docs d ON d.tsv @@ websearch_to_tsquery('english', e.query_text)
    WHERE e.kind = 'paraphrase')
  THEN RAISE EXCEPTION 'paraphrase text unexpectedly matches a document'; END IF;
END $$;

-- 5. Hybrid search: keyword and vector candidates, merged with reciprocal rank fusion.
CREATE FUNCTION hybrid_search(
  query_text text, query_embedding vector(32),
  match_count integer DEFAULT 10, rrf_k integer DEFAULT 60, candidates integer DEFAULT 40)
RETURNS TABLE (doc_id integer, rrf_score numeric, keyword_rank bigint, vector_rank bigint)
LANGUAGE sql STABLE AS $fn$
  WITH keyword AS (
    SELECT d.id, row_number() OVER (ORDER BY ts_rank_cd(d.tsv, q) DESC, d.id) AS r
    FROM docs d, websearch_to_tsquery('english', query_text) q
    WHERE d.tsv @@ q
    ORDER BY r
    LIMIT candidates
  ), semantic AS (
    SELECT s.id, row_number() OVER (ORDER BY s.dist, s.id) AS r
    FROM (
      SELECT d.id, d.embedding <=> query_embedding AS dist
      FROM docs d
      ORDER BY dist
      LIMIT candidates
    ) s
  )
  SELECT coalesce(k.id, s.id),
         coalesce(1.0 / (rrf_k + k.r), 0) + coalesce(1.0 / (rrf_k + s.r), 0),
         k.r, s.r
  FROM keyword k FULL OUTER JOIN semantic s ON s.id = k.id
  ORDER BY 2 DESC, 1
  LIMIT match_count
$fn$;

-- 6. Recall@10 per query kind and method (one target per query, so recall@10 = hit rate).
CREATE TABLE results AS
SELECT kind, method, count(*) AS queries, round(avg(hit::int), 3) AS recall_at_10
FROM (
  SELECT e.kind, 'keyword only' AS method,
         e.target IN (SELECT d.id FROM docs d, websearch_to_tsquery('english', e.query_text) q
                      WHERE d.tsv @@ q ORDER BY ts_rank_cd(d.tsv, q) DESC, d.id LIMIT 10) AS hit
  FROM eval_queries e
  UNION ALL
  SELECT e.kind, 'vector only',
         e.target IN (SELECT d.id FROM docs d ORDER BY d.embedding <=> e.embedding LIMIT 10)
  FROM eval_queries e
  UNION ALL
  SELECT e.kind, 'hybrid rrf k=60',
         e.target IN (SELECT h.doc_id FROM hybrid_search(e.query_text, e.embedding) h)
  FROM eval_queries e
) runs
GROUP BY kind, method;

-- 7. One fused result list, to see both ranks side by side.
SELECT h.* FROM eval_queries e, hybrid_search(e.query_text, e.embedding, 5) h WHERE e.qid = 1;
SELECT target FROM eval_queries WHERE qid = 1;

-- 8. Plan check: the keyword query must use the GIN index.
SET LOCAL enable_seqscan = off;
DO $$
DECLARE line text; uses_gin boolean := false;
BEGIN
  FOR line IN EXECUTE $q$EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM docs WHERE tsv @@ websearch_to_tsquery('english', 'E10042 error')$q$ LOOP
    RAISE NOTICE '%', line;
    IF line LIKE '%Bitmap Index Scan on docs_tsv_gin%' THEN uses_gin := true; END IF;
  END LOOP;
  IF NOT uses_gin THEN RAISE EXCEPTION 'keyword query does not use docs_tsv_gin'; END IF;
END $$;

SELECT * FROM results ORDER BY kind, method;
ROLLBACK;
\echo DONE: tokenizer check, sanity checks, recall table, fused sample, GIN plan check

7. Read the recall table against an answer key

The final SELECT prints six rows: three methods for each of the two query kinds. One target per query means recall@10 here is the share of queries whose target appears in the top 10. Write the answer key before you run, because the fixture's construction already decides most of it:

The row that tests your implementation is hybrid. If hybrid falls below the better single method on either kind, look for a bug before you blame the method: a join that drops one side, a rank that starts at 0, or ties sorted without the id tiebreak. The fused sample from step 7 of the script shows keyword_rank and vector_rank next to each score, so you can trace one result by hand: 1 / (60 + 1) for a first place is about 0.0164.

Keep the limit of this rehearsal in view. It proves that the SQL fuses correctly, the GIN index is used and an identifier found by only one side survives fusion. It cannot prove that your embedding model is weak on identifiers, because the fixture put that weakness into its vectors. Only section 8 can answer that.

8. Measure on your own identifier and paraphrase queries

Build two labelled sets from real traffic: 50 or more questions built around identifiers (error codes, version numbers, function and table names, ticket keys) and 50 or more paraphrased questions without them. For each, record the chunk or chunks that answer it. Run vector-only and hybrid retrieval with your production embedding model, HNSW settings and filters, and report recall@10 per set, not one blended number. A blended average can hide a paraphrase loss behind an identifier gain.

The decision rule: adopt hybrid if identifier recall rises and paraphrase recall does not fall beyond a margin you set in advance. Then vary one parameter at a time: candidate depth (20, 40, 100), and k only if you have a reason, since the paper found the choice not critical in its experiments. Record PostgreSQL and pgvector versions, the text search configuration, both candidate depths, k and hnsw.ef_search with every result, and keep the query sets so the comparison can be re-run after a model or chunking change.

9. When ts_rank is not enough

If hybrid recall meets your target, you are done with retrieval and the next lever is ordering. If the keyword side itself ranks badly, for example because common terms swamp rare ones, that is the absence of global term statistics showing. BM25 needs an extension. One option is ParadeDB's pg_search, whose README lists BM25 scoring; its install documentation adds it to shared_preload_libraries and enables it with CREATE EXTENSION pg_search, so check whether your hosting allows that before you plan around it. Measure it with the same two query sets instead of assuming it wins.

Once the right chunks reliably reach the candidate set, reordering them is a separate step with its own cost, covered in RAG Reranking: When Better Ordering Improves Answers. Fusion decides what gets in; reranking decides what comes first.

Execution record

On 2026-10-19 the unchanged fixture above was run once on Windows 11 against PostgreSQL 18.6 on x86_64-windows, compiled by msvc-19.44.35229, 64-bit, with pgvector 0.8.7. psql exited with code 0, both sanity checks passed, and the run ended with ROLLBACK and the final DONE line.

The fused sample for query 1, whose target is document 42, put document 42 first with keyword rank 1 and no vector rank, at a score of 0.01639344… (1/61). Document 1418, the vector side's first place, had exactly the same score and was ordered second only by the id tiebreak; ranks 3 to 5 were vector-only documents at 1/62, 1/63 and 1/64. The plan check printed Bitmap Index Scan on docs_tsv_gin under a Bitmap Heap Scan on docs, returning one row. The tokenizer check split ERR_SSL_PROTOCOL_ERROR into four lexemes (err, ssl, protocol, error) and pg_stat_statements into pg, stat and statement, treating each underscore as a blank, while 2.3.1 stayed one version token.

How to read this run: every row matches the answer key in section 7, which is what this fixture can show. The fused SQL kept the identifier hits from the keyword side and the paraphrase hits from the vector side. The numbers do not show that hybrid search improves retrieval in general. The fixture's synthetic vectors were built so that an identifier query only points at its topic and a paraphrase query lies next to its target, and the paraphrase texts were chosen to match no document, so the 0.060 and 0.000 rows follow from that construction. The tie for first place shows how close a single-list first place and a keyword-only first place are at k = 60. Your own query sets from section 8 decide whether hybrid search earns its place.