Production RAG with pgvector and Supabase: the architecture that actually scales

RAG (Retrieval-Augmented Generation) is the most underrated and worst-implemented technique in enterprise AI practice. Underrated because it isn't glamorous — it's embeddings, indexes and prompts. Poorly implemented because most teams copy an OpenAI cookbook tutorial and assume it's production-ready.

This guide describes the RAG architecture we use at WITS for clients in Mexico: PostgreSQL with pgvector as the foundation, Supabase as the auth and storage layer when it applies, Python + FastAPI for the orchestrator. No Pinecone, no Weaviate, no costly lock-in — at least until volume justifies it.

Why pgvector + Postgres instead of a dedicated vector DB

For volumes up to 10-50 million vectors and retrieval latencies <200ms, pgvector covers the need. The operational advantages are huge:

• A single database: vectors, metadata, ACLs and audit logs in the same Postgres
• Joins between vectors and relational tables — key for filtering by permissions, tenant, language
• Your team already knows how to operate Postgres: backups, replication, monitoring
• No egress fees or contract renewals: pgvector is open source and runs on any Postgres ≥13
• pgvector 0.7+ supports HNSW + quantization; performance comparable to dedicated vector DBs in most B2B cases

When you should move to a dedicated vector DB: >100M vectors, required latencies <50ms, or highly complex geometric queries. For 90% of B2B projects in Mexico, pgvector is the right choice.

The 7 stages of a production RAG pipeline

1. Ingestion

Source-specific connectors: PDFs (PyMuPDF for text + tables), Markdown (native parser that respects structure), Office (mammoth for .docx), Confluence/Notion (native APIs), code (tree-sitter for the AST). Each connector emits documents with metadata: source_id, version, lang, permissions, parent_path.

2. Chunking

Chunking is the most underrated decision. Four strategies and when to use each:

3. Embeddings

Models we recommend in 2026: OpenAI's text-embedding-3-large (1536 dim, quality/cost balance), Voyage-3 when justified (better for code and multilingual), self-hosted all-mpnet-base-v2 if the data can't leave your environment. Typical OpenAI cost: $0.13 per million tokens — irrelevant in most B2B projects.

4. Indexing in pgvector

HNSW (Hierarchical Navigable Small World) has been the default index since pgvector 0.5. Parameters: m=16, ef_construction=64 as a starting point. For >10M vectors, consider quantization (vector_l2_ops_quantized) to cut storage 4× with marginal accuracy loss.

5. Hybrid search: semantic + keyword

Semantic-only search fails on queries with technical jargon, numbers or proper names. Solution: combine vector search with BM25 (Postgres native full-text search with tsvector) and merge with Reciprocal Rank Fusion (RRF). With clients we've seen recall@10 rise from 78% to 91% just by adding BM25.

6. Reranking

A lightweight cross-encoder (Cohere Rerank, BGE-reranker-large) takes the top-50 from hybrid search and reorders the top-10. It improves precision@5 by 15-30% at a cost of ~20-50ms extra per query. Almost always worth it.

7. Generation with mandatory citations

The LLM receives the top-k chunks as context + a prompt that forces it to cite a source for every claim. Each citation is traceable to the chunk → source document → original metadata. Without this it isn't production RAG, it's a chatbot on steroids.

Traceability: the difference between RAG and an opaque oracle

Traceability means every answer can be audited: which chunks were retrieved, which prompt was sent, what the model generated, which documents the final answer cites. Without it, the system fails its first two security or legal audits and ends up in the POC graveyard.

• Every answer stores: query, embedding, top-k chunks retrieved, scores, prompt sent, output, latency
• The UI shows sources with a link to the original document — not "according to my sources"
• Structured logs that let you answer "why did it say X?" in a post-mortem
• Versioned embeddings + chunks: if you reindex, you know which version generated each historical answer

Evaluation: how to know it works

The metrics that matter, in order of priority:

Useful frameworks: Ragas, TruLens, DeepEval. An evaluation set of 50-200 curated questions with ideal answers — small but representative beats large and sloppy.

When NOT to use RAG

• The knowledge fits in the prompt (<8k stable tokens): put it in the system prompt and forget RAG
• You need reasoning over the whole base, not passage retrieval (structured data + SQL is better)
• The domain is ultra-narrow and stable: fine-tuning will probably be cheaper in the long run
• Latency <100ms required at high volume: cache + direct LLM beats the RAG pipeline

The minimum viable stack we recommend

• PostgreSQL 16 + pgvector 0.7+ (or Supabase, which already includes it)
• Python 3.12 + FastAPI + SQLModel for the orchestrator
• OpenAI or Anthropic for the main LLM; Voyage or OpenAI for embeddings
• Cohere Rerank for reranking (the free tier is enough for most)
• Sentry or Datadog for latency and error observability
• Ragas for continuous evaluation (CI/CD for the RAG)

Total operating cost for a production knowledge base of 100k documents / 1k daily queries: $300-$800 USD/month. Implementation cost with WITS: typically $400k-$700k MXN, depending on connector complexity and the evaluation volume required.

Mistakes we see over and over

1. 1Skipping evaluation: if you don't measure, you don't improve
2. 2Thoughtless chunking: accepting the library default without testing alternatives
3. 3Semantic search only: you lose 10-20% of recall on keyword queries
4. 4No reranking: the retriever's raw top-k is rarely optimal
5. 5No citations: the system loses credibility the first time it hallucinates
6. 6No permission filtering: the classic cross-tenant information leak
7. 7Early over-engineering: a dedicated vector DB when pgvector is enough