Hybrid Search in PostgreSQL with Full Text and pgvector

Store text-search and vector data together

create extension if not exists vector;

create table documents (
  id bigint generated always as identity primary key,
  tenant_id bigint not null,
  title text not null,
  body text not null,
  search_text tsvector generated always as (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) stored,
  embedding vector(1536)
);

create index documents_search_text_idx
  on documents using gin (search_text);

Match the vector dimension to the embedding model you actually use. Changing dimensions later requires a migration or a new column.

Build the keyword candidate set

websearch_to_tsquery accepts familiar quoted phrases, minus terms, and plain words. ts_rank_cd produces a score you can sort before assigning a simple row number for fusion.

Rank full-text candidates

select
  id,
  row_number() over (
    order by ts_rank_cd(search_text, websearch_to_tsquery('english', $1)) desc
  ) as keyword_rank
from documents
where tenant_id = $2
  and search_text @@ websearch_to_tsquery('english', $1)
limit 50;

Build the semantic candidate set

Use the same tenant and authorization filter on the vector side. Ordering by cosine distance with the matching operator class lets pgvector use an HNSW or IVFFlat index when the planner considers it worthwhile.

Rank vector candidates

select
  id,
  row_number() over (order by embedding <=> $1::vector) as semantic_rank
from documents
where tenant_id = $2
order by embedding <=> $1::vector
limit 50;

Fuse ranks instead of raw scores

Text rank and vector distance are not on the same scale. Reciprocal-rank fusion avoids pretending they are. It rewards documents that rank near the top of either list and gives an extra lift to documents present in both.

Reciprocal-rank fusion

with keyword as (
  -- keyword query, returning id and keyword_rank
), semantic as (
  -- vector query, returning id and semantic_rank
)
select
  coalesce(keyword.id, semantic.id) as id,
  coalesce(1.0 / (60 + keyword.keyword_rank), 0) +
  coalesce(1.0 / (60 + semantic.semantic_rank), 0) as score
from keyword
full join semantic using (id)
order by score desc
limit 20;

The constant 60 is a common starting point, not a universal optimum. Tune candidate counts and fusion against labeled queries.

Filter before results leave PostgreSQL

Tenant, status, language, and visibility rules should be inside both candidate queries. Filtering only after fusion can leak identifiers, waste candidate slots, and produce poor recall for users with narrow access.

Filtered approximate vector search can return too few rows because the index retrieves neighbors before the filter removes them. pgvector iterative scans and higher search parameters can help, but partitioning or exact search may be better for very selective filters.

Evaluate search with real questions

Build a small set of queries with expected relevant documents. Compare keyword-only, vector-only, and hybrid results. Record recall, latency, and failure cases such as identifiers, misspellings, and ambiguous natural-language questions.

Use the HNSW versus IVFFlat comparison before choosing an approximate index. The index is only one part of search quality; embedding choice and ranking evaluation matter just as much.