On this page

🔗Why We're Talking About This

The internet is overflowing with "Build a RAG app in 5 minutes" tutorials. They're great for a proof-of-concept, but production is where demos go to die. A real-world RAG system isn't just a vector store plugged into an LLM; it's a reliable, observable, and continuously evaluated data pipeline.

The good news? You don't need a proprietary, black-box vector database to do this well. This guide lays out a battle-tested pattern for building a production-grade RAG system on Azure using the tools you already know and trust: .NET, Postgres, and the pgvector extension. This approach gives you a rock-solid, portable foundation without locking you into a niche technology.

🔗The Shape of a Real RAG System

Think of your system as two distinct machines running side-by-side: an Ingestion Factory and a Query Engine, with a Quality Control department keeping them honest.

  • The Ingestion Factory: This is an offline process that takes your raw documents, breaks them into meaningful chunks, attaches critical metadata, generates vector embeddings for each chunk, and finally, stores everything in your pgvector tables.
  • The Query Engine: This is your live, user-facing API. It takes a user's query, finds the most relevant document chunks from the database, re-ranks them for precision, assembles a clean and well-grounded prompt, and sends it to the LLM to generate an answer.
  • The Quality Control Loop: This is your offline evaluation process. It constantly runs tests against your system to measure the quality of your retrieval and generation, ensuring that changes to your pipeline don't cause silent regressions.

🔗Chunking and Metadata: Your Foundation

Garbage in, garbage out. The quality of your RAG system is determined long before an LLM is ever called.

  • Chunk Smarter, Not Harder: Don't just split your text every 500 tokens. That's a recipe for cutting sentences in half and losing context. Whenever possible, use semantic chunking. Split documents by headings, paragraphs, or even bullet points. This keeps related ideas together and dramatically improves the quality of your search results.
  • Metadata is Your Get-Out-of-Jail-Free Card: Always store rich metadata alongside each chunk. At a minimum, you need the document's original source, the section or heading it came from, its version, and when it was last updated (updated_at). This is non-negotiable for filtering, debugging, and providing citations in your final answer.
  • Keep the Original Text: Don't discard the original, un-chunked text. It's invaluable for re-ranking algorithms and provides a crucial audit trail.

🔗Embeddings and Cost: The Necessary Evil

Embeddings cost money and time. Manage them wisely.

  • Start with a Good-Enough Model: You probably don't need the biggest, most expensive embedding model. Start with a strong, cost-effective model like OpenAI's text-embedding-3-small. Measure your retrieval quality. Only upgrade to a more powerful (and expensive) model if you have data proving you need it.
  • Cache Your Embeddings: Hash the content of each chunk. Before you send text to the embedding API, check if you already have an embedding for that exact hash. This simple trick prevents you from re-embedding the same content over and over, saving you a significant amount of money and time.
  • Batch and Retry: When you do need to generate embeddings, batch your requests to the provider's API. It's far more efficient. And always wrap your API calls in a retry policy with exponential backoff and jitter to handle transient network errors.

🔗The Schema: Your System's Beating Heart

This is where everything comes together. It's just a Postgres table, which is the whole point—it's simple, powerful, and you already know how to manage it.

-- Enable the vector extension
create extension if not exists vector;

-- The core table for our document chunks
create table if not exists documents (
  id bigserial primary key,
  source text not null,          -- e.g., 'sharepoint://docs/policy.docx'
  heading text,                  -- The section heading for this chunk
  metadata jsonb not null default '{}', -- Flexible bag for other info
  content text not null,         -- The actual text of the chunk
  embedding vector(1536),        -- The embedding vector (size from your model)
  updated_at timestamptz not null default now()
);

-- The magic index for fast vector search
create index on documents using ivfflat (embedding vector_cosine_ops);
-- A standard index for fast metadata filtering
create index on documents (source);

🔗Querying: Finding the Needles in the Haystack

With the pgvector extension, a complex semantic search becomes a stunningly simple SQL query.

-- Find the top 'k' documents closest to the user's query vector
select id, source, heading, metadata, content
from documents
order by embedding <-> :query_embedding -- The magic similarity operator
limit :k;

The <-> operator does all the heavy lifting, calculating the cosine distance between your query vector and all the chunks in the table, using the ivfflat index to do it with incredible speed.

🔗Re-ranking: From Good to Great

The first retrieval pass gets you into the right ballpark. Re-ranking is how you find the exact seat. After you retrieve the top 50-100 results with your vector search, you can use a faster, more precise algorithm (like a lightweight cross-encoder or Maximal Marginal Relevance) to re-order just that small set. This improves the quality of the final context without adding massive latency.

🔗Prompt Assembly: Building Trust

How you present the retrieved context to the LLM is critical.

  • Use a Deterministic Template: Create a rock-solid prompt template with clear separators between the user's question and the retrieved document chunks. This prevents ambiguity.
  • Include Citations: For each chunk you include in the prompt, also include its source and heading from your metadata. Instruct the model to use these citations in its answer. This turns a black box into a glass box, allowing users to verify the information and building immense trust in your system.

🔗Caching and Freshness: Staying Fast

Your database is fast, but memory is faster.

  • Cache What You Can: Cache retrieval results for a given (query_embedding_hash, top_k, filters) combination. You can also cache the final LLM completion.
  • Know When to Bust the Cache: Your caching strategy must be tied to your data freshness. When an underlying document is updated (check its content hash!), you must invalidate any cache entries that rely on it.
  • Measure Your Hit Rate: You can't improve what you don't measure. Aim for a cache hit rate of over 30% on real-world traffic to make a meaningful impact on latency and cost.

🔗Offline Evaluation: Your Safety Net

This is what separates a toy from a production system.

  • Start Simple: You don't need a data science PhD to evaluate your RAG system. Start with simple, measurable questions: How often is the correct answer in the top 5 retrieved documents? Is the final answer actually supported by the context we provided? Is the answer helpful?
  • Use a "Golden Set": Curate a set of representative queries where you know what a good response looks like. Run this "golden set" through your entire pipeline every night. Track the key quality metrics in a dashboard. If your accuracy drops by 5% overnight, you'll get an alert and can investigate before your users notice.

🔗Azure Notes

  • Services: Use Azure OpenAI for your embedding and completion models. Use Managed Identity to securely connect your services without managing secrets. Host your database on Azure Database for PostgreSQL with the pgvector extension enabled.
  • Observability: Use Azure Monitor (or a combination of Prometheus/Grafana) to build your dashboards for cost, latency, and evaluation metrics.

🔗Your Production Checklist

Before you go live, can you confidently check these boxes?

  • We have a clear schema and metadata strategy.
  • We have automated jobs for both backfilling all data and ingesting incremental updates.
  • Our queries can efficiently filter by source, tenant, or time window.
  • We have a multi-layer caching strategy for retrieval and completion.
  • We have an automated evaluation harness that runs daily and alerts us to quality regressions.

🔗Next Up

This is the pattern. In the follow-up post, I’ll publish the reference repository containing the full schema, the .NET ingestion scripts, the API code, and the Terraform for the Azure dashboards. Stay tuned.