RAG Patterns with PostgreSQL

Lesson: RAG Patterns with Azure PostgreSQL

Introduction: The Marriage of Structured Data and Generative AI

In the modern landscape of software development, Retrieval-Augmented Generation (RAG) has emerged as the standard architectural pattern for grounding Large Language Models (LLMs) in private or domain-specific data. While LLMs are excellent at reasoning and language generation, they are inherently limited by their training data cut-off dates and their tendency to hallucinate when asked about proprietary information. RAG solves this by retrieving relevant context from an external database and injecting it into the model’s prompt, providing the necessary facts for an accurate response.

Azure Database for PostgreSQL, particularly when combined with the pgvector extension, has become a premier destination for building these RAG systems. By utilizing PostgreSQL, you are not just building a vector store; you are building a system that keeps your structured relational data (like user permissions, product metadata, or transaction history) alongside your unstructured vector embeddings. This allows for hybrid search, where you combine semantic similarity with traditional SQL filters, leading to much higher precision in your retrieval process.

This lesson explores how to architect, implement, and optimize RAG patterns using Azure PostgreSQL. We will move beyond the basics of vector storage and dive into the nuances of indexing, metadata filtering, and performance tuning, ensuring that your AI solutions are both accurate and production-ready.

The Role of pgvector in Azure PostgreSQL

The pgvector extension is the bridge between traditional relational database management and modern AI applications. It introduces a new data type called vector to PostgreSQL, which allows you to store arrays of floating-point numbers representing the semantic meaning of your data. These vectors are generated by embedding models (such as Azure OpenAI’s text-embedding-ada-002 or text-embedding-3-large).

When you store these embeddings in PostgreSQL, you enable the database to perform mathematical operations such as Cosine Similarity, L2 Distance, and Inner Product calculations directly on your data. This means you do not need to move your data to a separate vector-only database. Instead, you keep your data where it lives, ensuring that your security, backup, and recovery policies apply to your AI context just as they do to your core business data.

Callout: The Power of Hybrid Search A common mistake when building RAG systems is relying solely on vector similarity. While vectors capture semantic meaning, they often struggle with exact matches (like product IDs, specific dates, or category tags). Hybrid search—the combination of vector similarity and standard SQL WHERE clauses—allows you to narrow down your search space using metadata before performing the vector search. This results in faster queries and more relevant retrieval results.

Architecting the RAG Workflow

A typical RAG workflow involves several distinct steps. Understanding these steps is critical for identifying where bottlenecks or inaccuracies might occur in your implementation.

1. Document Ingestion and Chunking: Raw data is broken into smaller, manageable chunks. If you feed a 50-page PDF into an embedding model at once, you will lose granularity. Proper chunking strategy (e.g., recursive character splitting) is vital for retrieval quality.
2. Embedding Generation: Each chunk is passed to an embedding model to convert the text into a vector representation.
3. Storage: The vector, along with metadata (source URL, timestamps, access control lists), is stored in your Azure PostgreSQL instance.
4. Retrieval: When a user asks a question, the question is also embedded. The system searches for the top k documents that are mathematically closest to the question vector.
5. Generation: The retrieved text chunks are sent to an LLM (like GPT-4) as context, along with the user's original query, to produce a final answer.

Setting Up the Environment

Before you can run RAG queries, you must enable the pgvector extension in your Azure PostgreSQL instance. You can do this by executing the following command in your SQL editor:

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

Once enabled, you define a table that can store your embeddings. It is best practice to include a column for the vector, a column for the text content, and columns for any metadata you might need for filtering.

CREATE TABLE document_chunks (
    id SERIAL PRIMARY KEY,
    content TEXT,
    metadata JSONB,
    embedding VECTOR(1536) -- 1536 is the dimension for OpenAI's ada-002
);

Note: The dimension size (1536 in the example above) must match the output dimension of the specific embedding model you are using. If you switch models later, you will need to re-generate your embeddings and update your table schema.

Implementing Advanced Search Patterns

Basic Semantic Search

To perform a basic semantic search, you calculate the distance between the query vector and the stored vectors. The <=> operator in pgvector calculates the Cosine Distance.

-- Searching for the top 5 most similar documents
SELECT content 
FROM document_chunks 
ORDER BY embedding <=> '[0.12, -0.05, ...]' 
LIMIT 5;

Hybrid Search with Metadata Filtering

In production, you rarely want a "blind" search. You likely want to filter by user permissions or document categories. Because PostgreSQL handles this natively, you can combine a SQL filter with a vector similarity search.

-- Searching for documents within a specific department that are semantically relevant
SELECT content 
FROM document_chunks 
WHERE metadata->>'department' = 'HR'
ORDER BY embedding <=> '[0.12, -0.05, ...]' 
LIMIT 5;

This query is highly efficient because the database optimizer can use standard indexes on the metadata column to narrow down the search space before calculating distances.

Optimizing for Performance: Indexing Vectors

As your dataset grows into the millions of rows, performing a linear scan (calculating the distance for every single row) becomes too slow for real-time applications. pgvector supports two primary types of indexes to speed up these queries: IVFFlat and HNSW.

IVFFlat (Inverted File Flat)

IVFFlat partitions the vector space into "lists." When you query, the database only searches the lists that are closest to your query vector. This is faster than a linear scan but requires a trade-off between speed and accuracy.

-- Creating an IVFFlat index
CREATE INDEX ON document_chunks USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

HNSW (Hierarchical Navigable Small World)

HNSW is generally the preferred choice for production environments. It builds a graph structure where nodes are vectors, allowing the search algorithm to traverse the graph and find the nearest neighbors quickly. It provides a better balance of speed and recall (accuracy) compared to IVFFlat.

-- Creating an HNSW index
CREATE INDEX ON document_chunks USING hnsw (embedding vector_cosine_ops);

Warning: Building indexes on large tables can be resource-intensive. Always monitor your CPU and memory usage during index creation. For very large datasets, consider performing index maintenance during off-peak hours.

Best Practices for Production RAG

Building a RAG system is an iterative process. You will find that your initial results are rarely perfect. Here are the industry standards for ensuring your system remains reliable as it scales.

1. Chunking Strategy Matters

The way you split your text is the single biggest factor in retrieval quality. If chunks are too small, they lack context. If they are too large, they include too much "noise" that confuses the LLM.

• Overlap: Always include an overlap between chunks (e.g., 10-20%) to ensure that information split across the boundary of two chunks is captured in both.
• Semantic Chunking: Instead of splitting by character count, consider using libraries that split by sentences or paragraphs to keep the logical structure of the text intact.

2. Monitor Retrieval Accuracy

You cannot improve what you do not measure. Implement an evaluation pipeline using frameworks like RAGAS or TruLens. These tools help you calculate metrics like:

• Faithfulness: Is the generated answer derived from the retrieved context?
• Answer Relevance: Does the answer actually address the user's question?
• Context Precision: Did we retrieve the right information?

3. Handle Updates and Deletions

Data is not static. When a source document is updated, you must ensure your vector database is updated as well.

• Upsert Logic: Use INSERT ... ON CONFLICT patterns in PostgreSQL to update existing embeddings rather than creating duplicates.
• Soft Deletes: Maintain a is_active flag in your metadata to filter out deleted content without having to rebuild the index immediately.

4. Security and Access Control

Because you are storing private data in the database, row-level security (RLS) is your friend. PostgreSQL supports RLS policies that allow you to restrict which rows a user can see based on their session credentials.

ALTER TABLE document_chunks ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_policy ON document_chunks
USING (metadata->>'tenant_id' = current_setting('app.current_tenant'));

Callout: The "Context Window" Trap Do not simply dump every retrieved chunk into the LLM prompt. The context window is a finite resource. If you retrieve 20 documents, the LLM might struggle to focus on the most important information. Implement a "reranking" step after the initial vector search to identify which of the top-k results are truly the most relevant before sending them to the LLM.

Comparison: Vector Database vs. PostgreSQL Extension

Common Pitfalls and How to Avoid Them

Pitfall 1: Ignoring the "Curse of Dimensionality"

Many developers assume that more dimensions are always better. However, using extremely high-dimensional embeddings (like 3072+) increases storage requirements and slows down index traversal without necessarily improving search quality. Start with standard models and move to higher dimensions only if you see a measurable improvement in your evaluation metrics.

Pitfall 2: Neglecting Data Normalization

Embeddings are sensitive to formatting. If your training data contains messy HTML tags or inconsistent casing, your embeddings will reflect that noise. Always clean your text data before sending it to the embedding model. Remove excessive whitespace, strip unnecessary tags, and normalize text to a consistent language.

Pitfall 3: The "Cold Start" Index Issue

When you first populate a table, you might forget to create an index, leading to slow performance. Alternatively, you might create an index too early, causing the database to rebuild the index for every single row insertion.

• The Fix: Bulk-insert your data first, then create the index as the final step of your ingestion pipeline.

Pitfall 4: Ignoring Token Limits

When retrieving context for an LLM, you are constrained by the token limit of the model. If you retrieve too many chunks, your application will crash or throw an error. Always implement a "token counter" in your application code to limit the amount of text sent to the LLM based on the model's specific context window constraints.

Step-by-Step Implementation: A Practical Example

Let’s walk through the implementation of a simple document retrieval service using Python and PostgreSQL.

Step 1: Install Dependencies

You will need psycopg2 for the database connection and openai for the embeddings.

pip install psycopg2-binary openai

Step 2: Define the Embedding Function

Create a function that communicates with the Azure OpenAI API to generate a vector for a given string.

import openai

def get_embedding(text):
    client = openai.AzureOpenAI(
        api_key="your_key",
        api_version="2023-05-15",
        azure_endpoint="your_endpoint"
    )
    response = client.embeddings.create(
        input=text,
        model="text-embedding-ada-002"
    )
    return response.data[0].embedding

Step 3: Query the Database

Now, write the logic that takes a user query, embeds it, and retrieves the relevant context from PostgreSQL.

import psycopg2

def retrieve_context(query_text):
    conn = psycopg2.connect("dbname=ai_db user=admin password=secret host=localhost")
    cur = conn.cursor()
    
    query_vector = get_embedding(query_text)
    
    # Using HNSW index for fast retrieval
    sql = """
    SELECT content FROM document_chunks 
    ORDER BY embedding <=> %s 
    LIMIT 3;
    """
    
    cur.execute(sql, (str(query_vector),))
    results = cur.fetchall()
    
    cur.close()
    conn.close()
    
    return [row[0] for row in results]

Step 4: Final Generation

Combine the context into a prompt for the LLM.

def generate_answer(user_query):
    context = retrieve_context(user_query)
    prompt = f"Use the following context to answer the question: {' '.join(context)}\n\nQuestion: {user_query}"
    
    # Send this prompt to your LLM completion endpoint
    # ...

Advanced Considerations: Handling Multi-Tenancy

In a SaaS application, you often have multiple customers (tenants) using the same database. You must ensure that Tenant A cannot retrieve documents belonging to Tenant B.

The most robust way to handle this in PostgreSQL is by using a combination of a tenant_id column and a filtered index.

1. Filtered Index: By creating an index that only includes rows for a specific tenant, you can significantly speed up searches for that tenant. However, this is only viable if the number of tenants is small.
2. Partitioning: For large-scale multi-tenant applications, use PostgreSQL table partitioning based on tenant_id. This keeps each tenant's data in its own physical partition, which is highly performant and makes data isolation trivial to manage.

Troubleshooting Common Errors

Error: "Vector dimension mismatch"

This occurs when the embedding model produces an array of a different size than what you defined in your SQL table.

• Resolution: Check your model documentation. If you are using text-embedding-3-small, the default dimension is 1536, but it can be configured to be smaller. Ensure your table definition matches the configuration used in your Python code.

Error: "Index not being used"

Sometimes, the PostgreSQL optimizer decides that a sequential scan is faster than using an index, especially on small datasets.

• Resolution: You can force the use of an index for testing purposes, but generally, trust the optimizer. If your dataset is large and it is still not using the index, check your EXPLAIN ANALYZE output to see if the cost of the index scan is higher than the sequential scan.

Error: "LLM Hallucination despite RAG"

If the LLM is still hallucinating, it might be because the retrieved chunks are irrelevant or the prompt instructions are too weak.

• Resolution: Add a "system prompt" that explicitly tells the model: "Answer only using the provided context. If the answer is not in the context, say you do not know."

Summary and Key Takeaways

Building RAG systems with Azure PostgreSQL is a powerful way to leverage your existing data infrastructure for AI applications. By using the pgvector extension, you combine the reliability of a mature relational database with the capabilities of modern vector search.

Key Takeaways:

• Hybrid Search is Essential: Never rely solely on vector similarity. Combine vector search with relational SQL filters (metadata) to ensure precision and security.
• Index Selection: Use HNSW for production environments where speed and recall are paramount. Use IVFFlat only if you have specific memory constraints.
• Embeddings are the Foundation: Your retrieval quality is only as good as your embedding model and your chunking strategy. Invest time in testing different chunk sizes and overlaps.
• Data Integrity: Treat your vector data with the same discipline as your relational data. Implement proper upsert logic and ensure your schema is correctly aligned with your embedding model's dimensions.
• Security First: Utilize Row-Level Security (RLS) to ensure that your AI applications respect user access controls and tenant boundaries.
• Evaluation is Continuous: Implement an evaluation loop (RAGAS, TruLens) to monitor the quality of your retrieval and generation. RAG is not a "set it and forget it" system; it requires ongoing tuning.
• Performance Tuning: Always perform index building as a post-ingestion step and monitor database metrics to ensure the system scales gracefully as your document library grows.

By following these principles, you will be able to build AI solutions that are not only capable of generating high-quality, relevant content but are also secure, performant, and maintainable in a production environment. PostgreSQL provides the stability that many specialized vector databases lack, making it an ideal choice for enterprise-grade AI development.