Tutorial: hybrid search with pgvector and reciprocal rank fusion

Before you start — about 45 minutes · pgvector · Postgres full-text · bge-m3 embeddings

Short version: one code path, explicit limits, a command that proves it works. Here is hybrid retrieval for RAG, end to end.

Step 1 — Decide what "working" means

Before touching code, write down three numbers: worst acceptable latency, cost per request, and what should happen when the upstream is unavailable. Everything after this is implementation.

Step 2 — Wire pgvector

The whole flow is one path. No special branches for "large" inputs — express those as different limits, not different code.

-- reciprocal rank fusion over dense + lexical candidates
with dense as (
  select id, row_number() over (order by embedding <=> $1) as r
  from chunk order by embedding <=> $1 limit 50
), lex as (
  select id, row_number() over (order by ts_rank_cd(tsv, q) desc) as r
  from chunk, websearch_to_tsquery('simple', $2) q
  where tsv @@ q limit 50
)
select id, sum(1.0 / (60 + r)) as score
from (select * from dense union all select * from lex) s
group by id order by score desc limit 10;

Why this works

The limit is enforced before the expensive call, not after it. Reserving budget up front means a burst degrades the caller that caused it instead of everyone downstream.

Step 3 — Guard the failure path

dense retrieval silently ignores exact identifiers like account numbers

Write that guard before you write the feature. It costs an hour and saves a postmortem.

Step 4 — Verify it

psql "$DATABASE_URL" -f check_recall.sql   # prints per-query recall@k

• Lower the limit and confirm the command fails loudly
• Kill the upstream and confirm you get a typed error, not a timeout
• Check the metric you emit per outcome: ok, throttled, upstream_error

Tuning knobs

Knob	Default	What we run	Why
concurrency	8	24	Most of the wait is network, not compute
retry budget	infinite	2, with jitter	Unbounded retries turn an outage into an avalanche
timeout	30s	8s, inherited from the caller	Nobody waits 30s for a chat reply

That is it. No framework required.