Enterprise RAG with pgvector Hybrid Search

Executive Summary: For B2B SaaS platforms up to tens of millions of document chunks, combining PostgreSQL 16+ pgvector HNSW dense search with native PostgreSQL full-text BM25 lexical search via Reciprocal Rank Fusion (RRF), cross-encoder reranking, and transactional Row-Level Security (RLS) outperforms standalone vector stores on accuracy, security isolation, and operational simplicity.

Three Reasons Naive Vector-Only RAG Disappoints Users

Teams frequently spin up an external vector database, split documents every 500 characters, run top-5 cosine similarity, and wonder why enterprise users lose trust within a week:

1. Dense Embeddings Miss Exact Identifiers: Ask a pure vector index for error code ERR_OAUTH_4019, invoice INV-88412, or SKU AX-990-B, and semantic embeddings return vaguely related authentication docs while missing the exact string match.
2. Blind Character Splits Destroy Structure: Slicing a financial table or legal sub-clause mid-row separates numbers from their column headers and negations (“shall not indemnify”) from their governing section.
3. Dual-Database ACL Drift Leaks Data: When permissions live in PostgreSQL but vectors live in an external store, revoking a user's folder access creates a synchronization window where confidential chunks still surface in chat answers.

The PostgreSQL Hybrid RRF Reference Architecture

Our RAG & Vector Search engineers keep relational metadata, tenant ACLs, lexical tsvectors, and pgvector HNSW embeddings inside a single ACID transaction boundary.

-- Hybrid Dense (pgvector HNSW) + Lexical (tsvector) Reciprocal Rank Fusion with Tenant RLS
WITH semantic_candidates AS (
  SELECT id, doc_id, section_path, content,
         ROW_NUMBER() OVER (ORDER BY embedding <=> $1::vector) AS dense_rank
  FROM knowledge_chunks
  WHERE tenant_id = current_setting('app.tenant_id')::uuid
  ORDER BY embedding <=> $1::vector
  LIMIT 40
),
lexical_candidates AS (
  SELECT id, doc_id, section_path, content,
         ROW_NUMBER() OVER (ORDER BY ts_rank_cd(search_tsv, websearch_to_tsquery('english', $2)) DESC) AS lex_rank
  FROM knowledge_chunks
  WHERE tenant_id = current_setting('app.tenant_id')::uuid
    AND search_tsv @@ websearch_to_tsquery('english', $2)
  LIMIT 40
)
SELECT COALESCE(s.id, l.id) AS chunk_id,
       COALESCE(s.section_path, l.section_path) AS section_path,
       COALESCE(s.content, l.content) AS content,
       COALESCE(1.0 / (60 + s.dense_rank), 0.0) + COALESCE(1.0 / (60 + l.lex_rank), 0.0) AS rrf_score
FROM semantic_candidates s
FULL OUTER JOIN lexical_candidates l ON s.id = l.id
ORDER BY rrf_score DESC
LIMIT 20;

Heading-Aware Chunking + Cross-Encoder Reranking

Before embedding any document, our ingestion workers parse Markdown/HTML/PDF layout trees to keep tables whole and prepend the hierarchical breadcrumb (e.g., MSA > Section 8: Limitation of Liability) onto every chunk before computing embeddings—a pattern you can inspect visually in our RAG Chunking & Overlap Visualizer. Those top twenty RRF candidates are then passed through a fast cross-encoder reranker down to the top five to eight spans injected into the generation prompt.

Mandatory Span Citations & Calibrated Abstention

Enterprise users forgive an assistant that says “No authorized document in Workspace X specifies the 2026 EU SLA penalty”; they never forgive a fabricated legal clause. Enforce structured output schemas requiring citations: [{ chunkId, exactQuote }] for every sentence, verify programmatically that exactQuote is a substring of the retrieved chunk, and abstain cleanly when reranker relevance scores drop below your calibrated floor.

Deploy Citation-Grounded Search Across Your SaaS Platform

Whether you are adding an AI copilot to a multi-tenant SaaS product, a HIPAA clinical workspace, or financial underwriting archive, book a 30-minute call with Eternitech's operators to review your retrieval architecture.