DEV Community

Cover image for How we built hybrid search in Postgres: pgvector, full text, and reciprocal rank fusion
Infrenic Team
Infrenic Team

Posted on Originally published at infrenic.com

How we built hybrid search in Postgres: pgvector, full text, and reciprocal rank fusion

A question comes in: "What is our notice period for contractors?"

Two searches run at once. One looks for meaning, so "contractors leaving" finds "notice period". One looks for exact words, so a product code or an invoice number is not blurred. The two ranked lists are merged, the best passages go to the model, and the answer comes back with a source next to every claim.

This post is the implementation. We built it inside Keel, our agentic AI platform, and it runs on a single Postgres database with the pgvector extension. No separate vector database, no third party embedding API.

The pipeline

Five steps from a document to a cited answer.

  1. Parse: text from PDF, Word, HTML, markdown, plain text. Scans go through OCR first.
  2. Chunk: recursive split into passages of about 800 characters with 120 characters of overlap.
  3. Embed: each chunk becomes a vector, computed where the system runs.
  4. Index: the vector goes into pgvector, the text goes into a full-text index, same database.
  5. Search and merge: a question runs both searches and the two ranked lists are merged.

Everything after that is the agent reading the merged passages and writing from them.

Chunking

Small enough to be specific, large enough to keep a clause together with its heading. The overlap exists because answers rarely respect a boundary: a sentence split across two chunks would be half missing from both.

Both numbers belong to the knowledge base, not the code. A contract set and a support ticket archive have different shapes, so they use different sizes without anyone editing the pipeline.

const parts = await md.chunk({
  strategy: "recursive",
  maxSize: kb?.chunkSize ?? 800,
  overlap: kb?.chunkOverlap ?? 120,
});
Enter fullscreen mode Exit fullscreen mode

We replace a document's chunks in one transaction, so a half processed file never serves answers, and a deleted document stops appearing immediately.

Embeddings that stay local

Embeddings are computed on the machine that runs the system, with a small model through FastEmbed. Documents are never sent to a third party just to be indexed. That matters for data residency, for regulated customers, and for installs with no internet at all. It also means reindexing a large archive does not create a per document bill.

const vectors = await embedder.embed(parts.map(p => p.text));
// vectors[i] is a float32 array, length 384 for BGE-small
Enter fullscreen mode Exit fullscreen mode

The schema

One table for documents, one for chunks, both in Postgres.

create table chunks (
  id uuid primary key,
  document_id uuid not null references documents(id) on delete cascade,
  content text not null,
  embedding vector(384) not null,
  search tsvector generated always as (to_tsvector('english', content)) stored,
  org_id uuid not null,
  knowledge_base_id uuid not null
);

create index chunks_embedding_idx on chunks
  using hnsw (embedding vector_cosine_ops);

create index chunks_search_idx on chunks
  using gin (search);
Enter fullscreen mode Exit fullscreen mode

The tsvector is generated, so it cannot drift from the content. The HNSW index keeps vector search fast without a separate service.

Hybrid search

Two searches run on every question.

async function search(query: string, kbId: string, orgId: string): Promise<SearchHit[]> {
  const qvec = await embedder.embed([query]);

  const vectorHits = await db.execute(sql`
    select id, document_id, title, content,
           1 - (embedding <=> ${qvec[0]}) as score
    from chunks
    where knowledge_base_id = ${kbId} and org_id = ${orgId}
    order by embedding <=> ${qvec[0]}
    limit 20
  `);

  const textHits = await db.execute(sql`
    select id, document_id, title, content,
           ts_rank_cd(search, plainto_tsquery('english', ${query})) as score
    from chunks
    where knowledge_base_id = ${kbId} and org_id = ${orgId}
      and search @@ plainto_tsquery('english', ${query})
    order by score desc
    limit 20
  `);

  return merge(vectorHits, textHits);
}
Enter fullscreen mode Exit fullscreen mode

The vector search finds a paraphrase: "contractors leaving" matches "notice period". The text search finds an exact product code, an invoice number or a surname that embeddings blur.

Merging by rank, not score

The two lists are merged with reciprocal rank fusion. You cannot compare a cosine distance and a text rank directly, they are not the same kind of number. RRF scores a passage by where it appears in each list instead.

function rrf(lists: SearchHit[][], k = 60): SearchHit[] {
  const scores = new Map<string, number>();
  const byId = new Map<string, SearchHit>();

  for (const list of lists) {
    list.forEach((hit, i) => {
      const rank = i + 1;
      scores.set(hit.id, (scores.get(hit.id) ?? 0) + 1 / (k + rank));
      byId.set(hit.id, hit);
    });
  }

  return Array.from(scores.entries())
    .sort((a, b) => b[1] - a[1])
    .slice(0, 8)
    .map(([id]) => byId.get(id)!);
}
Enter fullscreen mode Exit fullscreen mode

The practical effect: a passage that is second in both lists beats a passage that is first in one and missing from the other. That is usually the right answer.

The constant k (60) dampens the effect of the top ranks. A passage ranked first is not worth infinitely more than one ranked second.

Scoping by permission

Search is filtered by org and knowledge base at the SQL level, not in application code. A person only gets answers from documents they are allowed to read, and the filter cannot be bypassed by a clever prompt.

where knowledge_base_id = ${kbId} and org_id = ${orgId}
Enter fullscreen mode Exit fullscreen mode

The rules the agent runs under

Retrieval is a tool the agent calls. The rules are instructions plus scoping in the query.

  • When the question is about the organisation, search the knowledge base first.
  • Answer only from the passages that came back, not from what the model remembers.
  • Cite each claim inline with the passage number, like [1] or [2], and list the sources at the end.
  • If the passages do not contain the answer, say you could not find it. Do not fill the gap.
  • Search only the knowledge bases the person is allowed to use.

Every search is a tool call, recorded like any other action: who asked, what was searched, which passages came back, what the agent did with them. When an answer is questioned a month later, the evidence is still there.

Getting documents in, and keeping them current

Documents arrive by upload, from a connected drive or wiki, or by pointing the system at a website. Website import reads the sitemap or a list of pages, so a company's public help pages can become a knowledge base in minutes rather than a migration project.

Every document carries a status: ready with a chunk count, or failed with the reason. Reindexing replaces a document's chunks in one transaction. A deleted document stops appearing immediately.

When RAG is the wrong tool

RAG answers questions from text. It is the wrong choice in three common cases.

  • The answer is a number that changes today, like stock levels, balances or open tickets. That is a database query, not a document search.
  • The job is an action, like issuing a refund, booking a slot or opening a ticket. That is a workflow with permissions, not a chat answer.
  • You need arithmetic over a table. Retrieval gives you the rows, but something still has to add them up.

A useful assistant usually does all three. It searches documents, queries the database and calls tools. The agent decides which one a question needs.

Testing

Write down ten questions you already know the answer to, and the document each answer lives in. Ask them. Count how many answers are right and how many cite the right document. Ask three questions your documents cannot answer. Every one should get a refusal, not a guess. Change one document and ask again. If the answer does not move, indexing is broken.

You will learn more from those twenty minutes than from a month of demos.


We build AI agents on your data and tools, ship them to production and run them, all on Keel. Send us a problem at infrenic.com/free-ai-poc and see it working in 48 hours.

Top comments (5)

Collapse
 
sunnatulla_dev profile image
Sunnatulla •

One thing in the vector half that bites multi-tenant setups: with an HNSW index, the where knowledge_base_id = ... and org_id = ... filter is applied after the index scan, not inside it. The index hands back about hnsw.ef_search candidates (40 by default) from the whole table, then Postgres drops the ones from other orgs. So limit 20 can quietly return 2 rows.

I tried it on pgvector 0.8.7 with 10k random 384-dim vectors spread over 20 orgs. An org holding 5% of the rows (521 chunks) got 1 and then 2 rows back out of 20 in two runs, and EXPLAIN showed the HNSW index scan with the org filter on top. A tiny org with 1% didn't hit it, because the planner picked a seq scan for it. So it's the mid-sized tenants that lose recall, and nothing errors. The RRF step then mostly ranks the text hits, so answers can still look fine.

What fixed it in the same test:

set hnsw.iterative_scan = relaxed_order;  -- pgvector 0.8+
Enter fullscreen mode Exit fullscreen mode

With that the same query returned all 20. Raising hnsw.ef_search to 1000 worked too, but costs more on every query. relaxed_order can return rows slightly out of distance order, so the pgvector README suggests wrapping it in a materialized CTE and ordering by distance again outside. For a few very large orgs a partial index or partitioning by org is the other route.

(I use AI tools to help me write and check code. The test above I ran myself.)

Collapse
 
infrenic_team_2025 profile image
Infrenic Team •

Spot on point. That exact post-filter truncation behavior is one of the biggest gotchas with pgvector HNSW in multi-tenant schemas.

In Keel, we handle this in two ways depending on tenant size:

  1. Partitioning by org_id (Postgres declarative list partitioning). Each tenant gets its own isolated chunk table and dedicated HNSW index, so the vector scan is 100% scoped to that tenant's vectors before rank retrieval.
  2. For smaller shared knowledge bases where partitioning adds table overhead, we bump hnsw.ef_search dynamically per-query (set local hnsw.ef_search = 100;) to widen the initial candidate pool before applying the scalar filter.

If you have hundreds of small tenants, the declarative partition pattern keeps query latency sub-15ms without candidate starvation.

Collapse
 
sunnatulla_dev profile image
Sunnatulla •

One partition per tenant is the clean version, agreed. I'd be careful with the ef_search bump though. I benchmarked this recently (200k rows, ~2k tenants, default HNSW) and even ef_search = 1000 only got 1-row tenants to 0.30 recall@10 and 10-row ones to 0.33. At 100 it mostly turns "empty" into "slightly less empty" for small tenants.

Two more things bit me. SET LOCAL does nothing outside a transaction block, Postgres only prints a warning, so if the driver is in autocommit the bump never applies. And having org_id as a real column with stats isn't enough by itself: once the driver prepares the statement and Postgres switches to the generic plan, every tenant goes through HNSW again. What held up under the generic plan was an index on the tenant key, plus hnsw.iterative_scan = relaxed_order for the bigger tenants the planner still sends to HNSW.

Collapse
 
kolmopdf profile image
KolmoPDF •

Recursive character splitting at 800 characters works well for standard prose, but document tables often break mid-row. In our ingestion pipeline, splitting table markdown into row groups and prepending the original column header block to each slice prevented embedding drift without relying on arbitrary character boundaries.

Collapse
 
infrenic_team_2025 profile image
Infrenic Team •

Prepending table headers to each slice is a fantastic rule of thumb.

When a table chunk breaks across rows, the model loses the semantic context of what each cell represents (especially with numbers or dates), which completely ruins cosine similarity on queries looking for specific metrics.

In Keel, we extract tables as semantic units during the parsing phase. If a large table exceeds the target chunk size, we slice it along row boundaries and replicate the table header block on subsequent chunks rather than cutting across arbitrary character boundaries. Appreciate you sharing that practical nuance!