Most Retrieval-Augmented Generation (RAG) tutorials stop at a naive vector lookup: embed text with OpenAI, store it in Pinecone or Chroma, and perform cosine similarity search.
In production environments, this naive approach quickly fails. Vector embeddings excel at semantic similarity, but they consistently stumble on exact keywords, acronyms, product SKUs, UUIDs, and domain-specific error codes.
To build an enterprise-grade RAG agent, you need Hybrid Search with Reciprocal Rank Fusion (RRF) combined with a deterministic multi-agent harness.
In this guide, we will construct a production RAG system in under 30 minutes using PostgreSQL (pgvector), Reciprocal Rank Fusion (RRF), and LangGraph.
1. Why Pure Vector Search Fails in Production
Consider a developer searching an enterprise knowledge base for:
"Fix CVE-2024-38077 Windows Netlogon RPC buffer overflow"
- Dense Vector Search: Understands the general concept of "Windows vulnerabilities" and returns general security advisories, often missing the exact patch document.
-
Sparse Keyword Search (BM25): Finds the exact token
"CVE-2024-38077", but misses related contextual documentation that uses synonyms like"Netlogon remote elevation vulnerability". ### The Solution: Hybrid Search + Reciprocal Rank Fusion (RRF) Hybrid search executes both retrieval pipelines concurrently and merges the candidate rankings using the mathematical RRF formula: $$\text{RRF Score}(d) = \sum_{m \in M} \frac{1}{60 + r_m(d)}$$ Where: - $r_m(d)$ is the rank of document $d$ in retrieval method $m$ (Dense vs Sparse).
- $60$ is a smoothing constant that prevents top-heavy outliers from dominating the result set.
2. PostgreSQL Schema Setup (pgvector + tsvector)
You don't need a separate vector database. PostgreSQL handles dense vector embeddings and sparse full-text search within a single atomic ACID transaction.
sql
-- Enable vector extension
CREATE EXTENSION IF NOT EXISTS vector;
-- Create enterprise knowledge documents table
CREATE TABLE enterprise_documents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id VARCHAR(64) NOT NULL,
title TEXT NOT NULL,
content TEXT NOT NULL,
search_vector TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED,
embedding VECTOR(1536), -- Compatible with text-embedding-3-small
metadata JSONB DEFAULT '{}'::jsonb,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Fast approximate nearest neighbor index (HNSW)
CREATE INDEX ON enterprise_documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- High-performance GIN index for sparse full-text search
CREATE INDEX ON enterprise_documents USING gin(search_vector);
3. The SQL Reciprocal Rank Fusion (RRF) Query
Here is the single SQL query that executes both dense and sparse retrieval in parallel and merges them via RRF:
sql
WITH dense_candidates AS (
SELECT id, content, metadata,
rank() OVER (ORDER BY embedding <=> $1) as d_rank
FROM enterprise_documents
WHERE tenant_id = $3
LIMIT 50
),
sparse_candidates AS (
SELECT id, content, metadata,
rank() OVER (ORDER BY ts_rank_cd(search_vector, plainto_tsquery($2)) DESC) as s_rank
FROM enterprise_documents
WHERE tenant_id = $3 AND search_vector @@ plainto_tsquery($2)
LIMIT 50
)
SELECT
COALESCE(d.id, s.id) as id,
COALESCE(d.content, s.content) as content,
COALESCE(d.metadata, s.metadata) as metadata,
(COALESCE(1.0 / (60 + d.d_rank), 0.0) + COALESCE(1.0 / (60 + s.s_rank), 0.0)) as fusion_score
FROM dense_candidates d
FULL OUTER JOIN sparse_candidates s ON d.id = s.id
ORDER BY fusion_score DESC
LIMIT 10;
4. Connecting the Retrieval Engine to LangGraph
Now we wrap the hybrid search function inside an autonomous LangGraph Supervisor Agent that can analyze the retrieved context and verify facts before generating an answer.
typescript
import { StateGraph, END, START } from '@langchain/langgraph';
import { HybridSearchEngine } from './tools/hybridSearch';
export async function createRAGAgent() {
const searchEngine = new HybridSearchEngine(process.env.DATABASE_URL!);
const workflow = new StateGraph({
// Define state channels
channels: {
query: { value: (x, y) => y ?? x, default: () => '' },
retrievedContext: { value: (x, y) => y ?? x, default: () => [] },
finalAnswer: { value: (x, y) => y ?? x, default: () => '' },
}
});
// Step 1: Hybrid Retrieval Node
workflow.addNode('retrieve', async (state) => {
const embedding = await generateEmbedding(state.query);
const results = await searchEngine.search(state.query, embedding, 5);
return { retrievedContext: results };
});
// Step 2: Answer Generation Node (Claude 3.5 Sonnet)
workflow.addNode('synthesize', async (state) => {
const answer = await generateGroundedAnswer(state.query, state.retrievedContext);
return { finalAnswer: answer };
});
workflow.addEdge(START, 'retrieve');
workflow.addEdge('retrieve', 'synthesize');
workflow.addEdge('synthesize', END);
return workflow.compile();
}
5. Architectural Checklist for Production RAG
Before rolling this out to production users, enforce these 4 guardrails:
Strict Multi-Tenant Row-Level Security (RLS): Ensure tenant IDs are parameterized at the DB connection level.
Context Window Truncation: Dynamically budget token usage with tiktoken to prevent context overflow.
Cross-Encoder Re-ranking: For legal/medical data, add a secondary cross-encoder re-ranking pass (cohere.rerank or bge-reranker-large).
Hallucination Tripwires: Measure output faithfulness against retrieved <context> chunks using an automated LLM-as-a-Judge pass.
🚀 Complete Production Starter Kits & Resources
If you are building autonomous AI agents or production RAG systems, explore our battle-tested templates:
⭐️ Open-Source Claude Skills Starter Kit — Free on GitHub
📦 LangGraph Multi-Agent Production Starter Kit ($29) — Full TypeScript + Python code with Redis & pgvector memory
📋 Claude Code & Agent Prompt Templates Pack ($19) — 50 battle-tested production prompts
🛠️ Claude AI Engineering Skills Pack — 84 Skills ($49) — (Use coupon LAUNCH20 for 20% off)
How are you currently handling hybrid search and agent orchestration in your stack? Let's discuss in the comments!
Top comments (0)