Hybrid Search in Postgres: tsvector + pgvector + RRF (SQL Example)

Keyword search (Postgres full-text search) is great at exact terms: product codes, names, error messages. Vector search (embeddings with pgvector) is great at meaning: "how do I get my money back" matches a doc titled "Refund policy". Each misses what the other catches. (Semantic Search vs Keyword Search)

Hybrid search runs both and merges the results. You can do the whole thing in Postgres — no separate search service.

The table

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title     text NOT NULL,
  body      text NOT NULL,
  search    tsvector GENERATED ALWAYS AS (
              setweight(to_tsvector('english', title), 'A') ||
              setweight(to_tsvector('english', body), 'B')
            ) STORED,
  embedding vector(1024)   -- match your embedding model's dimensions
);

CREATE INDEX docs_search_idx    ON docs USING gin (search);
CREATE INDEX docs_embedding_idx ON docs USING hnsw (embedding vector_cosine_ops);

The search column is a generated column, so it stays in sync automatically. Your app computes embedding with an embedding model when a document is inserted or updated. (Postgres Generated Columns, pgvector Tutorial, What Are Embeddings?)

Why not just add the scores?

Full-text scores (ts_rank) and vector distances are on completely different scales. Adding or averaging them lets one dominate unpredictably. Reciprocal rank fusion (RRF) sidesteps this by using only each result's rank in each list:

score = 1 / (k + rank_in_keyword_results) + 1 / (k + rank_in_vector_results)

k (commonly 60) dampens the influence of top positions. A document that ranks well in both lists rises to the top; one that appears in only one list still gets credit.

The query

WITH keyword AS (
  SELECT id, row_number() OVER (ORDER BY ts_rank_cd(search, query) DESC) AS rank
  FROM docs, websearch_to_tsquery('english', $1) AS query
  WHERE search @@ query
  ORDER BY rank
  LIMIT 50
),
semantic AS (
  SELECT id, row_number() OVER (ORDER BY embedding <=> $2) AS rank
  FROM docs
  ORDER BY embedding <=> $2
  LIMIT 50
)
SELECT d.id, d.title,
       coalesce(1.0 / (60 + k.rank), 0) + coalesce(1.0 / (60 + s.rank), 0) AS score
FROM keyword k
FULL OUTER JOIN semantic s ON k.id = s.id
JOIN docs d ON d.id = coalesce(k.id, s.id)
ORDER BY score DESC
LIMIT 10;

$1 is the user's text; $2 is the embedding of that same text.
websearch_to_tsquery accepts Google-style input (quotes, -exclude, or) without syntax errors.
<=> is pgvector's cosine distance operator.
The FULL OUTER JOIN keeps documents found by only one method.

Tuning

Candidate count (LIMIT 50 in each CTE): larger finds more but costs more.
Weighting: multiply one term (e.g. 1.5 * 1.0/(60 + k.rank)) if keyword matches matter more in your domain — say, part numbers.
Filters (tenant, language, published) go in both CTEs' WHERE clauses. (Multi-Tenant Postgres Patterns)
Reranking: for the best quality, send the top 20–30 fused results to a reranker model and keep its top few.
Evaluate with real queries from your users rather than guessing. (Evaluate LLM Outputs)

Using it for RAG

For retrieval-augmented generation, hybrid search noticeably improves recall: questions that quote exact terms hit via keywords, paraphrased ones hit via vectors. Chunk documents sensibly first — search quality depends on what a "document" is. (What Is RAG?, Chunking for RAG)

Performance notes

Both indexes matter: GIN for @@, HNSW for <=>. Check with EXPLAIN ANALYZE. (Postgres EXPLAIN ANALYZE)
With strict filters, an HNSW index can return too few rows; recent pgvector versions offer iterative index scans to help.
A few hundred thousand chunks is comfortable on a modest server.

The summary

Store a tsvector and a vector side by side; index both.
Run keyword and vector queries separately, then merge with reciprocal rank fusion.
Put filters in both halves, and evaluate on real queries.

EasySpawn runs PostgreSQL next to your app on each server, so full-text search and pgvector live in the same database as your data — and Claude Code can build and tune the query. See how it works or join the waitlist.

Hybrid Search in Postgres: Full-Text + pgvector With Reciprocal Rank Fusion