Production RAG Pipeline with pgvector

The client was paying eight hundred dollars per month for a dedicated Pinecone instance and still getting irrelevant results on domain-specific legal queries. The naive chunking strategy was splitting documents mid-paragraph, so the embeddings captured partial context that did not represent the actual meaning of the text. Retrieval accuracy sat around sixty percent. When the LLM received these fragmented chunks as context, it would hallucinate answers roughly forty percent of the time because the retrieved text was too incomplete to ground a reliable response. The client needed accurate answers to questions about specific contract clauses, regulatory requirements, and case law citations, all of which require full paragraphs of context to interpret correctly.

The first decision was replacing Pinecone with pgvector on the client existing Supabase PostgreSQL instance. Since they were already paying for Supabase, adding the pgvector extension cost nothing extra. I created a vector column with 1536 dimensions to match the OpenAI text-embedding-ada-002 output and added an IVFFlat index for approximate nearest neighbor search. The chunking pipeline was rebuilt from scratch. Instead of splitting on a fixed token count, the system parses document structure first. It identifies headings, numbered clauses, paragraphs, and list items. Each chunk preserves its structural boundary, so a numbered clause is never split across two chunks. Chunks also carry metadata: the document category, section title, source file name, and page number. At query time, the pipeline runs in two stages. First, a metadata pre-filter narrows the search space to chunks from the relevant document category. This uses a standard PostgreSQL WHERE clause before the vector similarity search even begins. Second, pgvector performs cosine similarity on the filtered set and returns the top five chunks. LangChain orchestrates the full chain. It takes the query, calls OpenAI to generate the embedding, passes it to Supabase for retrieval, assembles the retrieved chunks into a structured prompt with source citations, and sends it to GPT-4. The system prompt instructs the model to cite which source each part of the answer came from and to explicitly state when the retrieved context does not contain enough information to answer. The FastAPI backend handles request validation, query preprocessing, and response formatting. It runs in a Docker container with structured logging for debugging retrieval quality over time.

Replaced a dedicated Pinecone vector database with pgvector on the client existing Supabase PostgreSQL instance, cutting infrastructure costs by eight hundred dollars per month. Built a custom chunking pipeline that preserves document structure and added metadata pre-filtering before cosine similarity search. LangChain orchestrates the retrieval chain while OpenAI text-embedding-ada-002 generates 1536-dimensional vectors. Retrieval accuracy improved from sixty percent to ninety five percent on a test set of two hundred domain-specific legal queries.

Retrieval accuracy jumped from sixty percent to ninety five percent on the client test set of two hundred domain-specific legal questions. Monthly infrastructure costs dropped from eight hundred dollars to zero additional spend because pgvector runs on the existing Supabase instance the client was already paying for. LLM hallucination rate fell from roughly forty percent of responses to under five percent because the retrieved chunks now contain complete, coherent context instead of fragmented text. Average query-to-answer latency is 1.8 seconds including embedding generation, vector search, and LLM response.