Architecting Production-Ready RAG AI Agents with OpenAI & pgvector

Retrieval-Augmented Generation (RAG) enables Large Language Models (LLMs) to answer domain-specific queries accurately by grounding responses in private knowledge bases without fine-tuning model weights.

In projects like Try Rehearse AI and Nutrition AI App, I implemented low-latency vector similarity pipelines using PostgreSQL and the pgvector extension.

High-Level RAG Architecture

[ User Question ] ──▶ [ text-embedding-3-small ] ──▶ ( 1536-dim Vector )
                                                             │
                                                             ▼
                                                [ PostgreSQL / pgvector ]
                                                Query: <=> Cosine Distance
                                                             │
                                                             ▼
[ LLM Generation (GPT-4o) ] ◀── [ Top K Chunks + System Prompt ]

1. Schema & Vector Indexing

-- Enable vector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Documents table with vector embedding column
CREATE TABLE knowledge_chunks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    metadata JSONB DEFAULT '{}',
    embedding vector(1536) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- HNSW Index for sub-millisecond similarity search
CREATE INDEX idx_knowledge_embedding_hnsw 
ON knowledge_chunks 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

2. Similarity Search Query

import { sql } from "drizzle-orm";
import { db } from "@/lib/db";

export async function findRelevantChunks(queryEmbedding: number[], limit = 4, threshold = 0.78) {
  const embeddingString = `[${queryEmbedding.join(",")}]`;
  
  const results = await db.execute(sql`
    SELECT 
      id,
      content,
      metadata,
      1 - (embedding <=> ${embeddingString}::vector) AS similarity
    FROM knowledge_chunks
    WHERE 1 - (embedding <=> ${embeddingString}::vector) > ${threshold}
    ORDER BY similarity DESC
    LIMIT ${limit};
  `);

  return results.rows;
}

Key Best Practices

1. Chunk Overlap: Use a recursive text splitter with 500-token chunks and 100-token overlap to maintain semantic continuity.
2. Metadata Filtering: Filter by user ID or organization ID before performing vector calculations for multi-tenant isolation.
3. Prompt Hardening: Instruct the model to strictly cite sources and state "I don't have enough information" when similarity scores fall below the threshold.