Hybrid Search with PostgreSQL and pgvector: Full Text, Vectors, and Rank Fusion

Hybrid search retrieves candidates with both lexical and semantic methods, then fuses their ranks. It is useful because the two retrievers fail differently: lexical search is strong on exact identifiers and rare terms, while embeddings can recover semantically related passages that share few words. The safe default is to keep both raw score systems separate and fuse ranks, not add incomparable scores.

This tutorial uses PostgreSQL 18 full-text search and pgvector 0.8.2. SQL is illustrative and must be tuned against your own corpus and relevance judgments.

Build one document representation with two indexes

Store the text, a generated search vector, and its embedding beside the authorization fields used at query time:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE passages (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id uuid NOT NULL,
  document_id uuid NOT NULL,
  title text NOT NULL,
  body text NOT NULL,
  search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED,
  embedding vector(1536) NOT NULL
);

CREATE INDEX passages_search_idx ON passages USING gin (search_vector);
CREATE INDEX passages_embedding_idx
  ON passages USING hnsw (embedding vector_cosine_ops);
CREATE INDEX passages_tenant_idx ON passages (tenant_id);

The dimension 1536 is an example, not a recommended universal size. It must exactly match the selected embedding model. Choose the pgvector operator class that matches the distance used during evaluation; cosine distance uses vector_cosine_ops and the <=> operator. The project documents the supported types, distances, and index syntax in its official README.

PostgreSQL assigns A through D weights to lexemes, allowing title matches to influence ts_rank differently from body matches. Its documentation also distinguishes ts_rank from the cover-density function ts_rank_cd; neither definition guarantees that the numeric score is calibrated to a vector distance. See PostgreSQL text-search ranking.

Produce lexical and semantic candidate lists

Use websearch_to_tsquery for human-entered text when its web-style quote, or, and dash behavior matches the product. PostgreSQL documents that it will not raise syntax errors for raw user input, but product limits on input length are still necessary. See the text-search function reference.

WITH lexical AS (
  SELECT
    id,
    row_number() OVER (
      ORDER BY ts_rank_cd(search_vector, query) DESC, id
    ) AS lexical_rank
  FROM passages, websearch_to_tsquery('english', $1) AS query
  WHERE tenant_id = $2
    AND search_vector @@ query
  ORDER BY ts_rank_cd(search_vector, query) DESC, id
  LIMIT 50
),
semantic AS (
  SELECT
    id,
    row_number() OVER (
      ORDER BY embedding <=> $3::vector, id
    ) AS semantic_rank
  FROM passages
  WHERE tenant_id = $2
  ORDER BY embedding <=> $3::vector, id
  LIMIT 50
)
SELECT * FROM lexical;

The tenant condition appears inside both candidate queries. Fetching global nearest neighbors and filtering unauthorized rows afterward can leak timing or identifiers and can leave too few permitted candidates.

Fuse ranks instead of raw scores

Reciprocal rank fusion gives each result a contribution based on its position:

RRF score(d) = Σ 1 / (k + rank_i(d))

k dampens the influence of very high ranks. Treat it as a tuning parameter, not a constant ordained by the method. A complete SQL version is:

WITH lexical AS (
  SELECT id, row_number() OVER (
    ORDER BY ts_rank_cd(search_vector, query) DESC, id
  ) AS rank
  FROM passages, websearch_to_tsquery('english', $1) AS query
  WHERE tenant_id = $2 AND search_vector @@ query
  ORDER BY ts_rank_cd(search_vector, query) DESC, id
  LIMIT $4
),
semantic AS (
  SELECT id, row_number() OVER (
    ORDER BY embedding <=> $3::vector, id
  ) AS rank
  FROM passages
  WHERE tenant_id = $2
  ORDER BY embedding <=> $3::vector, id
  LIMIT $4
),
fused AS (
  SELECT
    coalesce(lexical.id, semantic.id) AS id,
    coalesce(1.0 / ($5 + lexical.rank), 0.0) +
    coalesce(1.0 / ($5 + semantic.rank), 0.0) AS score
  FROM lexical
  FULL OUTER JOIN semantic USING (id)
)
SELECT passages.*, fused.score
FROM fused
JOIN passages USING (id)
ORDER BY fused.score DESC, passages.id
LIMIT $6;

The final ID tie-breaker makes pagination and regression comparisons stable. It does not make approximate retrieval deterministic across index rebuilds or configuration changes.

Treat filtered approximate search carefully

pgvector applies filtering after an approximate index scan. Its documentation gives a concrete consequence: with a selective filter and a limited HNSW candidate list, fewer matching rows can survive. Version 0.8 introduced iterative scans that continue scanning until enough rows are found or a configured limit is reached. The project documents strict and relaxed ordering plus hnsw.max_scan_tuples and hnsw.scan_mem_multiplier in Iterative Index Scans.

Possible strategies are:

Do not force an ANN index because it exists. pgvector performs exact nearest-neighbor search without an approximate index, which is often a sound baseline for a small filtered candidate set.

Evaluate retrieval before evaluating answers

Create query judgments with at least one of these labels: directly answers, supporting context, related but insufficient, or irrelevant. Include exact identifiers, paraphrases, acronyms, ambiguous terms, and queries with no answer in the corpus.

Compare lexical-only, semantic-only, and fused retrieval using:

• recall at the context budget;
• mean reciprocal rank when one early answer matters;
• nDCG when graded relevance and ordering matter;
• percentage of queries with no relevant passage;
• p50, p95, and p99 database latency; and
• retrieved tokens per query.

Tune candidate counts and fusion parameters on a development set. Report final performance on a held-out set; otherwise repeated tuning leaks test information into the design.

Know when hybrid search is the wrong choice

Use lexical search alone for exact catalog lookup when synonyms are controlled and semantic drift is harmful. Use semantic search alone only after evidence shows lexical retrieval adds no material recall. A dedicated search engine may be preferable when the product requires sophisticated language analyzers, typo tolerance, faceting, operationally independent scaling, or ranking features PostgreSQL should not own.

Hybrid retrieval also cannot repair missing or incorrectly permissioned content. Fix ingestion and authorization before tuning rank fusion.

Production checklist

• Text configuration matches the language and domain.
• Embedding dimensions and distance operator are fixed and documented.
• Both candidate queries enforce identical authorization filters.
• Raw lexical scores and vector distances are not added directly.
• Candidate counts, RRF parameter, and context budget were tuned on labeled queries.
• Exact vector search remains in the benchmark as a recall reference.
• Filtered HNSW behavior is tested with realistic tenant sizes.
• Results have stable tie-breakers and citations to source versions.
• No-answer queries can abstain instead of forcing unrelated context.