Production RAG Pipelines with pgvector & PostgreSQL

While dedicated cloud vector databases exist, deploying pgvector directly within PostgreSQL has emerged as the architectural gold standard for production enterprise RAG systems. It eliminates dual-database synchronization pipelines, prevents data drift, enforces strict ACID transactions, and allows developers to join unstructured semantic vector similarity directly against relational business tables in a single sub-50ms query.

This guide details the end-to-end engineering architecture for a high-recall RAG retrieval system using PostgreSQL 16, pgvector, HNSW indexes, BM25 hybrid ranking, and Cross-Encoder reranking.

1. Architectural Blueprint: The Hybrid Retrieval Pipeline

Pure semantic vector search frequently fails on exact keyword queries (such as alphanumeric product serial numbers, error codes, and customer tax IDs). Combining dense vector embeddings with sparse lexical search via Reciprocal Rank Fusion (RRF) yields the highest recall accuracy:

[User Query]
     |
     +-----------------------+-----------------------+
     | (Dense Embedding)     | (Sparse Text Lexical) |
     v                       v                       v
[pgvector HNSW Cosine]  [PostgreSQL tsvector BM25]
     |                       |
     +----------+------------+
                |
                v
  [Reciprocal Rank Fusion (RRF)]  ---> Top 20 Candidates
                |
                v
    [Cross-Encoder Reranker]      ---> Top 5 High-Precision Chunks
                |
                v
      [LLM Generation Context]

2. PostgreSQL Schema & Optimized HNSW Vector Indexing

Hierarchical Navigable Small World (HNSW) graphs offer an order of magnitude faster query latencies and superior recall compared to older IVFFlat cluster-based indexes, with zero pre-warming training phases required.

-- 1. Enable pgvector and cryptographic extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 2. Knowledge Base Document Chunks Table
CREATE TABLE kb_document_chunks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL,
  document_id UUID NOT NULL,
  chunk_index INT NOT NULL,
  content TEXT NOT NULL,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  -- 1536-dim embeddings for OpenAI text-embedding-3-small or Gemini
  embedding vector(1536) NOT NULL,
  -- Pre-computed Full Text Search vector for instant BM25 matching
  tsv_content tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 3. High-Performance HNSW Cosine Distance Index
-- m: Max connections per layer (16-64). Higher = higher recall, more RAM.
-- ef_construction: Exploration factor at index build time (64-128).
CREATE INDEX idx_chunks_hnsw_cosine
ON kb_document_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 24, ef_construction = 100);

-- 4. GIN Index for rapid full-text keyword retrieval
CREATE INDEX idx_chunks_tsv ON kb_document_chunks USING gin (tsv_content);

-- 5. Relational B-Tree index for tenant multi-tenancy filtering
CREATE INDEX idx_chunks_org_id ON kb_document_chunks (organization_id);

3. Hybrid Search: Combining Vector Cosine with Reciprocal Rank Fusion (RRF)

Reciprocal Rank Fusion scores candidates based on their rank positions across both retrieval engines, completely avoiding the challenge of calibrating disparate score distributions:

$$\text{RRF Score}(d) = \sum_{m \in M} \frac{1}{60 + r_m(d)}$$

WITH vector_search AS (
  SELECT id, content,
         1 - (embedding <=> $1::vector) AS vector_similarity,
         ROW_NUMBER() OVER (ORDER BY embedding <=> $1::vector) AS v_rank
  FROM kb_document_chunks
  WHERE organization_id = $2
  ORDER BY embedding <=> $1::vector
  LIMIT 25
),
text_search AS (
  SELECT id, content,
         ts_rank_cd(tsv_content, plainto_tsquery('english', $3)) AS text_score,
         ROW_NUMBER() OVER (ORDER BY ts_rank_cd(tsv_content, plainto_tsquery('english', $3)) DESC) AS t_rank
  FROM kb_document_chunks
  WHERE organization_id = $2 AND tsv_content @@ plainto_tsquery('english', $3)
  ORDER BY text_score DESC
  LIMIT 25
)
SELECT 
  COALESCE(v.id, t.id) AS chunk_id,
  COALESCE(v.content, t.content) AS content,
  COALESCE(v.vector_similarity, 0.0) AS vector_score,
  (COALESCE(1.0 / (60 + v.v_rank), 0.0) + COALESCE(1.0 / (60 + t.t_rank), 0.0)) AS combined_rrf_score
FROM vector_search v
FULL OUTER JOIN text_search t ON v.id = t.id
ORDER BY combined_rrf_score DESC
LIMIT 10;

4. Node.js RAG Retrieval Service with Cross-Encoder Reranking

Retrieving candidates with SQL is step one; scoring them with a cross-encoder model eliminates semantic false positives:

import { pool } from "../config/db";
import { CohereClient } from "cohere-ai";

const cohere = new CohereClient({ token: process.env.COHERE_API_KEY });

interface RetrievalParams {
  query: string;
  queryEmbedding: number[];
  orgId: string;
  topK?: number;
}

export async function executeProductionRagRetrieval({
  query,
  queryEmbedding,
  orgId,
  topK = 5
}: RetrievalParams) {
  // Step 1: Execute Hybrid RRF Query in PostgreSQL
  const embeddingString = `[${queryEmbedding.join(",")}]`;
  
  const queryResult = await pool.query(
    `
    WITH vector_search AS (
      SELECT id, content, metadata,
             ROW_NUMBER() OVER (ORDER BY embedding <=> $1::vector) AS v_rank
      FROM kb_document_chunks
      WHERE organization_id = $2
      ORDER BY embedding <=> $1::vector
      LIMIT 30
    ),
    text_search AS (
      SELECT id, content, metadata,
             ROW_NUMBER() OVER (ORDER BY ts_rank_cd(tsv_content, plainto_tsquery('english', $3)) DESC) AS t_rank
      FROM kb_document_chunks
      WHERE organization_id = $2 AND tsv_content @@ plainto_tsquery('english', $3)
      LIMIT 30
    )
    SELECT COALESCE(v.id, t.id) AS id,
           COALESCE(v.content, t.content) AS content,
           COALESCE(v.metadata, t.metadata) AS metadata,
           (COALESCE(1.0 / (60 + v.v_rank), 0.0) + COALESCE(1.0 / (60 + t.t_rank), 0.0)) AS rrf_score
    FROM vector_search v
    FULL OUTER JOIN text_search t ON v.id = t.id
    ORDER BY rrf_score DESC
    LIMIT 20;
    `,
    [embeddingString, orgId, query]
  );

  const candidates = queryResult.rows;
  if (candidates.length === 0) return [];

  // Step 2: Cross-Encoder Reranking
  const rerankResponse = await cohere.rerank({
    model: "rerank-english-v3.0",
    query: query,
    documents: candidates.map((c) => c.content),
    topN: topK,
    returnDocuments: false
  });

  // Step 3: Re-map ranked indices to original chunks
  return rerankResponse.results.map((result) => ({
    chunk: candidates[result.index],
    relevanceScore: result.relevanceScore
  }));
}

5. Performance Tuning & Benchmark Metrics

Critical PostgreSQL Production Flags

-- Increase HNSW search exploration at query time (defaults to 40)
-- Higher = higher recall at the expense of slight latency
SET hnsw.ef_search = 64;

-- Give vector builds ample memory to construct clean graphs
SET maintenance_work_mem = '2GB';

Key Architectural Takeaways

• Eliminate Dual Databases: Running pgvector inside PostgreSQL removes complex synchronization microservices while maintaining ACID guarantees.
• Always Pair with BM25: Pure vector search misses domain codes, acronyms, and SKU numbers. Hybrid search with Reciprocal Rank Fusion solves lexical gaps.
• Apply Cross-Encoders: A final reranking step across the top 20 candidates boosts context quality by 20–35% before injecting into LLM context prompts.