You Don’t Need a Vector DB: Ranking hybrid search in Postgres

This is the second part of the series on using Postgres for hybrid search. Here I just want to focus on ranking, in the simplest shape I can show. I needed one result list, but I had two different signals: meaning and text.

I spent some time thinking embeddings would cover more than they really do. They're good when the query and the document talk about the same idea. They're less good with error codes, partial IDs, and slightly wrong spellings.

What the vector query gave me

For meaning-based search, pgvector was already enough. I mostly used cosine distance with <=> and kept the query plain.

SELECT
  id,
  embedding <=> :query_embedding AS cosine_distance
FROM knowledge_base
ORDER BY embedding <=> :query_embedding
LIMIT 10;

In production I usually filtered before ranking, for example by product area or tenant. I'm leaving that out here because the point is the ranking shape, not the full query.

I also stopped treating vector search as the full answer. It was one useful signal, not the whole search system.

Why I kept trigram search

pg_trgm looks simple next to embeddings, but it solved a real problem for me. It works on overlapping three-character chunks, which makes it good at typos, partial words, and messy IDs people still search for.

For a word like postgres, the extension breaks it into trigrams like po, pos, ost, stg, tgr, gre, res, and es . That's enough to make fuzzy text matching useful without making the query too complex.

The shape I used was roughly this:

SELECT
  id,
  similarity(content, :query_text) AS trigram_score
FROM knowledge_base
WHERE content % :query_text
ORDER BY trigram_score DESC
LIMIT 10;

The % operator acts as a filter, and similarity() gives you the score for sorting. That was enough to catch things the vector side often missed. If someone typed ERR_CONN_RSET, I didn't want to rely on embeddings to guess what they meant. The trigram query was much better at that job.

In some cases I searched more than one field. In others I built one search_text column. I didn't treat that as a fixed rule. It depended on how messy the text was.

The score problem I ran into

At first I tried to think in raw scores, and that went nowhere. Cosine distance is lower-is-better. Trigram similarity is higher-is-better. Even if you normalize both, the numbers still don't mean the same thing. A cosine distance of 0.12 is not really comparable to a trigram score of 0.85.

So I stopped trying to merge scores and switched to rank fusion instead. Reciprocal Rank Fusion was the most practical option because it only cared about position in each result list.

$$
RRF(d) = \sum_{r \in R} \frac{1}{k + r(d)}
$$

Where $d$ is the document, $R$ is the set of ranked lists, $r(d)$ is the position of the document in one list, and $k$ is a smoothing constant. I usually used $k = 60$. I wouldn't claim that number is special. It just worked well for the data I had.

The SQL shape I settled on

What finally felt stable was a query with two CTEs and a merge step. One list came from the vector query. One came from the text query. Then I joined them and computed the RRF score from their ranks.

WITH semantic AS (
  SELECT
    id,
    ROW_NUMBER() OVER (ORDER BY embedding <=> :query_embedding) AS rank
  FROM knowledge_base
  ORDER BY embedding <=> :query_embedding
  LIMIT 50
),
lexical AS (
  SELECT
    id,
    ROW_NUMBER() OVER (
      ORDER BY similarity(content, :query_text) DESC
    ) AS rank
  FROM knowledge_base
  WHERE content % :query_text
  ORDER BY similarity(content, :query_text) DESC
  LIMIT 50
)
SELECT
  COALESCE(s.id, l.id) AS id,
  s.rank AS semantic_rank,
  l.rank AS lexical_rank,
  COALESCE(1.0 / (60 + s.rank), 0.0) +
  COALESCE(1.0 / (60 + l.rank), 0.0) AS rrf_score
FROM semantic s
FULL OUTER JOIN lexical l ON s.id = l.id
ORDER BY rrf_score DESC
LIMIT 10;

I capped each list at 50 rows mostly to keep the merge cheap. And I used a FULL OUTER JOIN because some documents were only strong in one query. That was fine. I still wanted those results to survive.

What this changed in practice

The result list felt more balanced. A query like connection reset in pooler still benefited from the vector side because the meaning lined up. But ERR_CONN_RSET was where the combination helped most. The trigram query caught the typo. The vector query still added context. Documents that were decent in both lists moved upward naturally.

A typical result shape looked like this:

That middle result was usually the one I cared about. It was not first in either list, but it looked relevant in two different ways.

What still needed judgment

This didn't remove the usual search problems. Bad chunking still caused bad recall. Loose trigram thresholds still produced junk. Weak embeddings still drifted. And once I had query logs, I ended up tuning the queries differently for different datasets.

Still, this was the first query shape that felt honest about the problem. I was no longer asking semantic search to do keyword work, and I was no longer pretending a fuzzy string score and a vector distance belonged on the same scale.