How to Build Hybrid Search in Postgres: Full-Text + pgvector (2026)

This guide is for engineers who already store embeddings in Postgres, or are about to, and have noticed that pure vector search misses queries a keyword search would have nailed: error codes, config keys, product names. By the end you will have a single table with a full-text index and an HNSW vector index, one SQL function that fuses both rankings with reciprocal rank fusion (RRF), an optional upgrade to real BM25 scoring, a place for a reranker, and a SQL query that measures recall@10 so you can prove any of it helps. Versions: PostgreSQL 18 and pgvector 0.8.7 (released 1 October 2026); I ran every SQL statement below on a throwaway local PostgreSQL 18.4 with pgvector 0.8.2, and the outputs shown are from that run on a four-row toy table with 3-dimensional vectors. You need Docker or a Postgres you control, and an embedding model; the background on choosing one is in the companion post, embedding models explained.

Step 1: Run PostgreSQL 18 with pgvector

The pgvector project publishes images with the extension preinstalled. Pin the exact tag rather than latest.

# pgvector 0.8.7 on PostgreSQL 18 (Debian trixie base); local use only
docker run -d --name pg-hybrid \
  -e POSTGRES_PASSWORD=localdev \
  -p 5432:5432 \
  pgvector/pgvector:0.8.7-pg18-trixie

psql "postgresql://postgres:localdev@localhost:5432/postgres" \
  -c "CREATE EXTENSION IF NOT EXISTS vector;" \
  -c "SELECT extversion FROM pg_extension WHERE extname = 'vector';"

Expected output is a single row with 0.8.7. On a managed database, check which pgvector version your provider ships before you rely on 0.8.x features such as iterative index scans, which arrived in 0.8.0.

Step 2: Create the table with a generated tsvector and a GIN index

Keep the text, the precomputed full-text vector and the embedding in the same row. A stored generated column means the tsvector can never drift from the text, and setweight lets title matches outrank body matches.

CREATE TABLE documents (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title         text NOT NULL,
  body          text NOT NULL,
  -- title words weighted A, body words weighted B; recomputed on every write
  tsv           tsvector GENERATED ALWAYS AS (
                  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
                  setweight(to_tsvector('english', coalesce(body,  '')), 'B')
                ) STORED,
  embedding     vector(1536),          -- text-embedding-3-small default size
  embed_model   text NOT NULL DEFAULT 'text-embedding-3-small'
);

-- Inverted index for the @@ operator
CREATE INDEX documents_tsv_idx ON documents USING gin (tsv);

The embed_model column costs almost nothing and saves you later, when you change models and need to know which rows were embedded with what. Pick the vector(n) size to match your model's output exactly; inserting a different length fails.

Step 3: Load embeddings, then build the HNSW index

Generate embeddings outside the database and insert them in batches. This uses the OpenAI Python SDK and pgvector's Psycopg 3 adapter; any model works if you change the dimension in Step 2. I have not run this script against the paid API for this guide, so treat it as a template.

# requirements: openai==3.48.0 psycopg[binary]==3.3.8 pgvector==0.5.1
import os
import psycopg
from openai import OpenAI
from pgvector import Vector
from pgvector.psycopg import register_vector

client = OpenAI()  # reads OPENAI_API_KEY from the environment
MODEL = "text-embedding-3-small"

docs = [
    ("Fix ERR_CONNECTION_REFUSED on port 5432",
     "Postgres is not listening; check listen_addresses and pg_hba.conf."),
    ("Database will not accept connections",
     "The server refuses clients after a restart because of a bad config."),
]

with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    register_vector(conn)
    # Embed title + body together so the vector sees the same text as the tsvector
    inputs = [f"{t}\n\n{b}" for t, b in docs]
    resp = client.embeddings.create(model=MODEL, input=inputs)
    with conn.cursor() as cur:
        cur.executemany(
            "INSERT INTO documents (title, body, embedding, embed_model) "
            "VALUES (%s, %s, %s, %s)",
            [(t, b, Vector(e.embedding), MODEL)
             for (t, b), e in zip(docs, resp.data)],
        )

Build the vector index after the bulk load, not before; pgvector's README notes builds are much faster when the graph fits in maintenance_work_mem.

SET maintenance_work_mem = '2GB';   -- size to your server; must not exhaust RAM

-- Cosine distance (<=>) suits normalised embeddings such as OpenAI's
CREATE INDEX documents_embedding_idx
  ON documents USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);   -- pgvector defaults, written out

ANALYZE documents;

If your model outputs more than 2,000 dimensions, this statement fails; see the troubleshooting table for the halfvec workaround.

Step 4: Run keyword and vector search separately

Before fusing anything, look at each retriever alone. websearch_to_tsquery accepts the syntax people type into search boxes, quoted phrases, or and -exclusions, and never throws a syntax error.

-- Keyword side: note that words are ANDed by default
SELECT id, title, ts_rank_cd(tsv, q) AS rank
FROM documents, websearch_to_tsquery('english', 'connection refused postgres') q
WHERE tsv @@ q
ORDER BY rank DESC
LIMIT 40;

 id |                  title                  |    rank
----+-----------------------------------------+------------
  1 | Fix ERR_CONNECTION_REFUSED on port 5432 | 0.16666667
(1 row)

Only one row: the second document means the same thing but uses different words ("will not accept connections"), so the AND query skips it. That is exactly the gap the vector side fills.

-- Vector side: $1 is the query embedding from the same model
SELECT id, title, embedding <=> $1 AS cosine_distance
FROM documents
ORDER BY embedding <=> $1
LIMIT 40;

Confirm the plan with EXPLAIN. On my local run with enable_seqscan = off (needed on a four-row table) the vector query showed Index Scan using documents_embedding_idx with Order By: (embedding <=> ...), and the keyword query showed a Bitmap Index Scan on documents_tsv_idx.

Step 5: Fuse the rankings with reciprocal rank fusion in SQL

Keyword scores and cosine distances are on different scales, so adding them directly is meaningless. RRF ignores the raw scores and uses only positions: each document gets 1 / (k + rank) from every list it appears in, summed. The original SIGIR 2009 paper by Cormack, Clarke and Buettcher fixed k at 60 after a pilot and found it near-optimal, and that is still the usual default. pgvector's own README points to RRF for combining it with Postgres full-text search.

CREATE OR REPLACE FUNCTION hybrid_search(
  query_text      text,
  query_embedding vector,
  match_count     int DEFAULT 10,
  rrf_k           int DEFAULT 60,
  candidates      int DEFAULT 40     -- keep <= hnsw.ef_search
)
RETURNS TABLE (id bigint, title text, rrf_score numeric, fts_rank bigint, vec_rank bigint)
LANGUAGE sql STABLE AS $$
WITH full_text AS (
  SELECT s.id, row_number() OVER (ORDER BY s.score DESC, s.id) AS rnk
  FROM (
    SELECT d.id, ts_rank_cd(d.tsv, q) AS score
    FROM documents d, websearch_to_tsquery('english', query_text) q
    WHERE d.tsv @@ q
    ORDER BY score DESC
    LIMIT candidates
  ) s
),
semantic AS (
  SELECT s.id, row_number() OVER (ORDER BY s.dist, s.id) AS rnk
  FROM (
    SELECT d.id, d.embedding <=> query_embedding AS dist
    FROM documents d
    ORDER BY dist                 -- inner ORDER BY + LIMIT lets HNSW drive the scan
    LIMIT candidates
  ) s
)
SELECT d.id, d.title,
       round(coalesce(1.0 / (rrf_k + ft.rnk), 0)
           + coalesce(1.0 / (rrf_k + se.rnk), 0), 5) AS rrf_score,
       ft.rnk, se.rnk
FROM full_text ft
FULL OUTER JOIN semantic se ON se.id = ft.id
JOIN documents d ON d.id = coalesce(ft.id, se.id)
ORDER BY rrf_score DESC, d.id
LIMIT match_count;
$$;

SELECT * FROM hybrid_search('connection refused postgres', '[0.88,0.15,0.02]', 3);

Output from the toy table:

 id |                  title                  | rrf_score | fts_rank | vec_rank
----+-----------------------------------------+-----------+----------+----------
  1 | Fix ERR_CONNECTION_REFUSED on port 5432 |   0.03279 |        1 |        1
  2 | Database will not accept connections    |   0.01613 |          |        2
  3 | Tuning HNSW ef_search                   |   0.01587 |          |        3
(3 rows)

Document 1 appears in both lists and gets 1/61 + 1/61. Document 2 is found only by the vector branch but still ranks second, which is the whole point. The rank columns are worth keeping in the output while you tune; they tell you which retriever is pulling its weight. The inner ORDER BY ... LIMIT matters: I checked the plan and it uses the HNSW index for the vector branch and the GIN index for the keyword branch. Ranking with a window function over the whole table instead would force a full scan and sort.

To weight one side, multiply its term, for example 0.7 / (rrf_k + se.rnk). ParadeDB's own RRF reference uses exactly that shape with weights of 1.0 and 0.7.

Step 6 (optional): Replace ts_rank_cd with real BM25

ts_rank_cd is not BM25. It rewards term frequency and proximity but has no inverse document frequency, so a common word counts as much as a rare one, and it ignores document length unless you pass a normalisation flag, where BM25 builds length normalisation in. For RRF this matters less than you would think, because only the order is used, but on large corpora a proper BM25 ranking gives the keyword branch noticeably better candidates. Two extensions do it today. I have not installed either for this guide; the syntax below is from their current documentation.

pg_textsearch from Timescale, v1.5.1, PostgreSQL licence, supports PostgreSQL 17 and 18:

-- postgresql.conf: shared_preload_libraries = 'pg_textsearch'  (then restart)
CREATE EXTENSION pg_textsearch;

CREATE INDEX documents_bm25_idx ON documents
  USING bm25 (body) WITH (text_config = 'english');

-- <@> returns a negative BM25 score, so ascending order = best first
SELECT id, title, body <@> 'connection refused postgres' AS score
FROM documents
ORDER BY body <@> 'connection refused postgres'
LIMIT 40;

ParadeDB pg_search, v0.26.6, AGPL-3.0. Since 0.25.0 the index access method is called paradedb, with bm25 kept as an alias, and the old key_field option is deprecated and ignored.

-- postgresql.conf: shared_preload_libraries = 'pg_search'  (then restart)
CREATE EXTENSION pg_search CASCADE;   -- CASCADE also enables pgvector

CREATE INDEX documents_search_idx ON documents
  USING paradedb (id, title, body);

-- ||| matches any of the terms; pdb.score() is the BM25 score
SELECT id, title, pdb.score(id) AS score
FROM documents
WHERE body ||| 'connection refused postgres'
ORDER BY pdb.score(id) DESC, id
LIMIT 40;

Either query drops straight into the full_text CTE in Step 5 in place of the tsvector query. Because both extensions register a bm25 access method name, use one per database; I have not tested them side by side.

Step 7: Add a reranker on the fused top 20

RRF decides which candidates are in the pool; a cross-encoder reranker decides their final order by reading the query and each document together. Call hybrid_search(..., match_count => 20), send those 20 texts to a reranker such as Cohere rerank-v5.0-fast, Voyage rerank-3, or a self-hosted bge-reranker-v2-m3, and keep the top five to ten. Keep the pool small: Voyage, for example, bills reranking on query tokens times document count plus document tokens. Reranking is a network call outside Postgres, so put a timeout on it and fall back to the RRF order when it fails.

Step 8: Measure recall@10 in SQL

Without an evaluation set you cannot tell whether fusion, BM25 or the reranker helped. Store 100 or more real queries with their known relevant documents and compute recall in the database.

CREATE TABLE eval_queries (
  qid             int PRIMARY KEY,
  query_text      text NOT NULL,
  query_embedding vector(1536) NOT NULL
);
CREATE TABLE eval_qrels (               -- "query relevance judgements"
  qid    int    REFERENCES eval_queries,
  doc_id bigint REFERENCES documents(id),
  PRIMARY KEY (qid, doc_id)
);

WITH results AS (
  SELECT q.qid, h.id AS doc_id
  FROM eval_queries q
  CROSS JOIN LATERAL hybrid_search(q.query_text, q.query_embedding, 10) h
),
per_query AS (
  SELECT g.qid, count(r.doc_id)::numeric / count(*) AS recall
  FROM eval_qrels g
  LEFT JOIN results r ON r.qid = g.qid AND r.doc_id = g.doc_id
  GROUP BY g.qid
)
SELECT count(*) AS queries, round(avg(recall), 3) AS recall_at_10
FROM per_query;

Run it three times, with only the full-text CTE, only the semantic CTE, and both, and keep the numbers. On my two-query toy set the hybrid run returned recall_at_10 = 1.000, which proves the query works and nothing else. Your real numbers are the ones that matter.

Troubleshooting

Symptom	Likely cause	Fix
ERROR: column cannot have more than 2000 dimensions for hnsw index	Model outputs over 2,000 dims (3,072 for text-embedding-3-large, Gemini)	Request fewer dims from the API, or index (embedding::halfvec(3072)) halfvec_cosine_ops and query with the same cast (limit 4,000)
Vector branch returns fewer rows than candidates	HNSW returns at most hnsw.ef_search rows (default 40), and filters apply after the scan	SET hnsw.ef_search = 100 for larger pools; for filtered queries SET hnsw.iterative_scan = relaxed_order (0.8.0+)
Keyword branch finds almost nothing	websearch_to_tsquery ANDs every word	Join the words with or first: websearch_to_tsquery('english', 'connection or refused or postgres') turns the ANDs into ORs
pg_hba.conf or ERR_CONNECTION_REFUSED not matched as expected	The default parser splits ERR_CONNECTION_REFUSED on underscores and stems the parts, but keeps pg_hba.conf as one host token, so a query for pg_hba misses it	Add a second tsvector built with the simple config for identifiers, or embed code tokens in a separate column
EXPLAIN shows Seq Scan, not the HNSW index	Missing ORDER BY embedding <=> ... LIMIT, operator does not match opclass, or table is tiny	Use <=> with vector_cosine_ops; keep ORDER BY and LIMIT in the same subquery; test with SET enable_seqscan = off
ERROR: expected 1536 dimensions, not 1024	Embedding model or dimensions setting changed	Fix the client, or migrate to a new column and re-embed
HNSW build very slow or NOTICE: hnsw graph no longer fits into maintenance_work_mem	Graph exceeds memory during build	Raise maintenance_work_mem for the session; in Docker raise --shm-size to match for parallel builds
Hybrid results worse than vector alone	Keyword branch floods the pool with weak matches	Weight the branches (for example 0.5 keyword, 1.0 vector), lower keyword candidates, or move to BM25
Query and document embeddings disagree badly	Model needs a query or document prefix or task type	Apply the model's documented instruction, for example a query prefix for Qwen3-Embedding

FAQ

Is Postgres full-text search the same as BM25?

No. Built-in ts_rank and ts_rank_cd score by term frequency and proximity without corpus-wide inverse document frequency. For true BM25 inside Postgres, use the pg_textsearch or ParadeDB pg_search extensions. For RRF, built-in ranking is often good enough because only the order of results is used.

What k should I use for reciprocal rank fusion?

Start with 60, the value from the original 2009 paper and the one ParadeDB's RRF example uses. Smaller k gives the top few positions more weight. Tune it only against an evaluation set, and change one thing at a time.

Do I need a separate vector database for hybrid search?

Not at the scale most teams run. Postgres with pgvector and a GIN index handles both branches in one transactionally consistent query, and filters and joins are plain SQL. A dedicated engine starts to earn its keep at very large vector counts or very high query rates; the trade-offs are covered in vector databases in 2026.

Should I use cosine distance or inner product?

For unit-length embeddings such as OpenAI's, cosine and inner product give the same order, and pgvector's README recommends inner product (<#> with vector_ip_ops) for best performance in that case. Note that <#> returns the negative inner product, so ascending order is still best first. Cosine (<=>) is the safer default when you are not sure the vectors are normalised.

What I check first in production is the boring part: that the HNSW index is actually used (EXPLAIN on the real query, not a hand-written one), that candidates is not larger than hnsw.ef_search, and that every row carries the model name its embedding came from. Then I look at the per-branch rank columns for the ten worst queries in the eval set, because they show which retriever is failing far faster than any aggregate score. The official references are the pgvector README, the PostgreSQL text search documentation, the pg_textsearch README and the ParadeDB docs. For the next layer up, see how to build a RAG pipeline with Postgres and pgvector and tuning pgvector HNSW.

Related: How to Build a RAG Pipeline With Postgres and pgvector (2026), How to Tune pgvector Performance: HNSW, ef_search and Quantization (2026) and Vector Databases in 2026: Do You Need One, or Is pgvector Enough?.