Hybrid Search in PostgreSQL: Combining pgvector and Full-Text Search

1. The Blind Spots of Pure Vector Search

Dense vector search excels at matching intent and concept: a query for how do I cancel my subscription will retrieve a chunk that says terminate your plan even though the words barely overlap, because both phrases live close together in embedding space. That same strength is exactly why vector search struggles with anything where exact tokens matter: product SKUs, error codes, part numbers, person names, or acronyms. An embedding model has no reliable way to represent that INV-20487 must match INV-20487 and nothing else, because it was trained to generalize meaning, not to preserve exact strings.

This blind spot is not a corner case in most real applications; it is a large fraction of real queries. Support tickets reference order numbers, engineering docs reference function names and config keys, and legal or compliance search needs exact clause and section references. Teams that ship vector-only search routinely discover this in production when a user searches for an exact error code and gets back semantically related but wrong results, because the nearest neighbors in embedding space are conceptually similar, not lexically identical.

The fix is not to replace vector search but to run it alongside classical lexical search (BM25-style ranking via Postgres's built-in tsvector and GIN indexes) and merge the two ranked lists. This is what hybrid search means in practice, and the reason it belongs directly in Postgres rather than as a separate search service is that pgvector and tsvector can share the same row, the same transaction, and the same consistency guarantees, which eliminates an entire class of sync bugs between a primary database and an external search index.

2. Reciprocal Rank Fusion (RRF) in Pure SQL

Combining a vector similarity score and a BM25-style text rank is harder than it looks, because the two scores live on completely different, non-comparable scales: cosine distance is bounded between 0 and 2, while ts_rank scores are unbounded and depend on document length and term frequency. Naively averaging or weighting the two raw scores produces results dominated by whichever score happens to have a wider numeric range that day, which is fragile and hard to reason about.

Reciprocal Rank Fusion sidesteps this entirely by discarding the raw scores and working only with rank position: each document gets a fusion score of 1 divided by (k plus its rank) in each result list, summed across lists, where k is a smoothing constant (60 is the standard default from the original RRF paper) that dampens the influence of any single very-high rank. Because it only needs the ordering, not the magnitude, of each underlying search, RRF composes cleanly across any number of ranked lists without any score normalization step.

The elegant part for a Postgres-based stack is that RRF is expressible as a single SQL query using two CTEs, one per search method, each producing a rank via ROW_NUMBER() OVER (ORDER BY ...), joined with a FULL OUTER JOIN so that documents matched by only one method are not dropped. This means hybrid search runs entirely inside the database, in one round trip, with no application-side score-merging logic to maintain.