Enterprise RAG Architecture in .NET 9: Hybrid Search with Semantic Kernel and PostgreSQL pgvector

While prototype Retrieval-Augmented Generation (RAG) demos take minutes to assemble in Python, engineering an enterprise-grade, mission-critical RAG pipeline in .NET capable of sub-200ms latency, zero hallucinations, and high concurrency across millions of technical documents requires deep architectural precision.

In this comprehensive guide, we will design and deploy a production-ready RAG pipeline using .NET 9, Microsoft Semantic Kernel, Azure OpenAI (text-embedding-3-large & GPT-4o), and PostgreSQL with pgvector. We will cover technical chunking strategies, hybrid search (combining dense vectors with sparse BM25 text ranking via Reciprocal Rank Fusion), and enterprise guardrails to ensure grounded, verifiable citations.

[Client Query] ──> [ASP.NET Core 9 Web API] 
                         │
                         ├─► 1. Generate Query Vector (Azure OpenAI text-embedding-3-large)
                         │
                         ├─► 2. Hybrid Query on PostgreSQL (pgvector HNSW + tsvector BM25)
                         │       └─► Reciprocal Rank Fusion (RRF) Ranking
                         │
                         ├─► 3. Context Window Assembler (Token budget & deduplication)
                         │
                         ├─► 4. Grounded Prompt Formulation (System Guardrails)
                         │
                         └─► 5. Streaming Completion (GPT-4o via Semantic Kernel)
        

1. Why "Naive RAG" Fails in Enterprise Environments

Most developer tutorials implement what is known as Naive RAG: reading a PDF, slicing it every 500 characters, generating embeddings, calculating top-K cosine similarity, and sending everything directly into an LLM prompt. In real-world enterprise environments, this fails for three main reasons:

1. Broken Semantic Boundaries: Slicing text blindly by character or word count cuts off C# class definitions, SQL procedures, and tables right in the middle, degrading vector retrieval accuracy by over 40%.
2. Keyword Blindness (Vector-Only Retrieval): Vector search is great for conceptual synonym matching, but fails at exact identifiers (e.g., error codes like ERR_0x80070005 or exact SKU numbers like XPS-9520-4K).
3. Hallucinations & Context Stuffing: Feeding 15 irrelevant chunks to an LLM dilutes the attention mechanism, causing the model to invent answers when the ground truth isn't immediately obvious.

2. Document Ingestion: Structural & Semantic Chunking

Rather than fixed-character chunking, technical enterprise documentation (PDFs, Markdown, C# docs) should be segmented using Header-Aware Semantic Chunking with token overlaps.

public record DocumentChunk(
    Guid Id,
    string DocumentName,
    int ChunkIndex,
    string HeadingHierarchy,
    string Content,
    ReadOnlyMemory<float> Embedding
);

public class MarkdownSemanticChunker
{
    private const int TargetChunkTokens = 450;
    private const int OverlapTokens = 60;

    public List<string> ChunkMarkdown(string rawText)
    {
        var chunks = new List<string>();
        var sections = rawText.Split(new[] { "\n#", "\n##", "\n###" }, StringSplitOptions.RemoveEmptyEntries);

        foreach (var section in sections)
        {
            if (EstimateTokenCount(section) <= TargetChunkTokens)
            {
                chunks.Add(section.Trim());
            }
            else
            {
                // Sub-split long sections by paragraphs while maintaining overlap
                chunks.AddRange(SlidingWindowSplit(section, TargetChunkTokens, OverlapTokens));
            }
        }
        return chunks;
    }

    private static int EstimateTokenCount(string text) => (int)Math.Ceiling(text.Length / 3.8);

    private static List<string> SlidingWindowSplit(string text, int maxTokens, int overlap)
    {
        var paragraphs = text.Split(new[] { "\n\n" }, StringSplitOptions.RemoveEmptyEntries);
        var subChunks = new List<string>();
        var current = new StringBuilder();

        foreach (var para in paragraphs)
        {
            if (EstimateTokenCount(current.ToString() + para) > maxTokens && current.Length > 0)
            {
                subChunks.Add(current.ToString().Trim());
                current.Clear();
            }
            current.AppendLine(para);
        }

        if (current.Length > 0)
            subChunks.Add(current.ToString().Trim());

        return subChunks;
    }
}

3. Setting Up PostgreSQL with pgvector for Hybrid Search

We use PostgreSQL with the pgvector extension. Unlike pure vector engines (Pinecone, Qdrant), PostgreSQL allows storing business tables, relational ACL security permissions, full-text search indices, and vector embeddings in the exact same ACID database engine.

-- Enable pgvector and full-text search extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Enterprise Document Chunks Table
CREATE TABLE enterprise_knowledge_chunks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_name VARCHAR(255) NOT NULL,
    chunk_index INT NOT NULL,
    heading_hierarchy VARCHAR(500),
    content TEXT NOT NULL,
    metadata JSONB,
    -- Full-Text search vector column
    search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
    -- Azure OpenAI text-embedding-3-large outputs 3072 dimensions (or truncated to 1536)
    embedding vector(1536) NOT NULL
);

-- 1. HNSW Index for ultra-fast Approximate Nearest Neighbor vector search (Cosine Distance)
CREATE INDEX idx_chunks_embedding_hnsw 
ON enterprise_knowledge_chunks 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- 2. GIN Index for rapid full-text keyword retrieval (BM25 equivalent)
CREATE INDEX idx_chunks_search_vector_gin 
ON enterprise_knowledge_chunks 
USING gin(search_vector);

4. Implementing Reciprocal Rank Fusion (RRF) Hybrid Search in SQL

Vector search and full-text search output scores on completely different scales (cosine distances between 0.0 – 1.0 vs. BM25 unbounded scores). Reciprocal Rank Fusion (RRF) resolves this by ranking items based on their positional ranks rather than raw score floats:

-- Reciprocal Rank Fusion (RRF) Hybrid Search Stored Procedure
CREATE OR REPLACE FUNCTION match_documents_hybrid(
    query_text TEXT,
    query_embedding vector(1536),
    match_count INT,
    rrf_k INT DEFAULT 60
)
RETURNS TABLE (
    id UUID,
    document_name VARCHAR(255),
    heading_hierarchy VARCHAR(500),
    content TEXT,
    combined_score FLOAT
) 
LANGUAGE sql AS $$
WITH semantic_search AS (
    SELECT id, document_name, heading_hierarchy, content,
           ROW_NUMBER() OVER (ORDER BY embedding <=> query_embedding) as rank_semantic
    FROM enterprise_knowledge_chunks
    ORDER BY embedding <=> query_embedding
    LIMIT match_count * 2
),
keyword_search AS (
    SELECT id, document_name, heading_hierarchy, content,
           ROW_NUMBER() OVER (ORDER BY ts_rank_cd(search_vector, websearch_to_tsquery('english', query_text)) DESC) as rank_keyword
    FROM enterprise_knowledge_chunks
    WHERE search_vector @@ websearch_to_tsquery('english', query_text)
    LIMIT match_count * 2
)
SELECT 
    COALESCE(s.id, k.id) as id,
    COALESCE(s.document_name, k.document_name) as document_name,
    COALESCE(s.heading_hierarchy, k.heading_hierarchy) as heading_hierarchy,
    COALESCE(s.content, k.content) as content,
    (COALESCE(1.0 / (rrf_k + s.rank_semantic), 0.0) + 
     COALESCE(1.0 / (rrf_k + k.rank_keyword), 0.0))::FLOAT as combined_score
FROM semantic_search s
FULL OUTER JOIN keyword_search k ON s.id = k.id
ORDER BY combined_score DESC
LIMIT match_count;
$$;

5. Semantic Kernel & Azure OpenAI Service in .NET 9

Now let's wire the C# service layer. Install the official Microsoft Semantic Kernel and Npgsql packages:

dotnet add package Microsoft.SemanticKernel
dotnet add package Microsoft.SemanticKernel.Connectors.AzureOpenAI
dotnet add package Npgsql
dotnet add package Pgvector

Here is our production service implementing the complete lifecycle:

using System.Data;
using Microsoft.SemanticKernel;
using Microsoft.SemanticKernel.ChatCompletion;
using Microsoft.SemanticKernel.Embeddings;
using Npgsql;
using Pgvector;

public interface IRagSearchService
{
    IAsyncEnumerable<string> AskStreamingAsync(string userQuestion, CancellationToken ct = default);
}

public class ProductionRagService : IRagSearchService
{
    private readonly ITextEmbeddingGenerationService _embeddingService;
    private readonly IChatCompletionService _chatService;
    private readonly string _connectionString;

    public ProductionRagService(
        ITextEmbeddingGenerationService embeddingService,
        IChatCompletionService chatService,
        IConfiguration config)
    {
        _embeddingService = embeddingService;
        _chatService = chatService;
        _connectionString = config.GetConnectionString("PostgresRagDb")!;
    }

    public async IAsyncEnumerable<string> AskStreamingAsync(string userQuestion, [EnumeratorCancellation] CancellationToken ct = default)
    {
        // 1. Generate 1536-dim Embedding for Query
        var queryEmbedding = await _embeddingService.GenerateEmbeddingAsync(userQuestion, cancellationToken: ct);
        var pgVector = new Vector(queryEmbedding.ToArray());

        // 2. Perform Hybrid Retrieval via Postgres pgvector
        var retrievedContexts = new List<string>();

        await using var conn = new NpgsqlConnection(_connectionString);
        await conn.OpenAsync(ct);

        await using var cmd = new NpgsqlCommand("SELECT document_name, heading_hierarchy, content, combined_score FROM match_documents_hybrid(@query, @embedding, @limit);", conn);
        cmd.Parameters.AddWithValue("query", userQuestion);
        cmd.Parameters.AddWithValue("embedding", pgVector);
        cmd.Parameters.AddWithValue("limit", 4);

        await using var reader = await cmd.ExecuteReaderAsync(ct);
        int citationIndex = 1;
        while (await reader.ReadAsync(ct))
        {
            string doc = reader.GetString(0);
            string heading = reader.IsDBNull(1) ? "" : reader.GetString(1);
            string content = reader.GetString(2);

            retrievedContexts.Add($"[Citation {citationIndex}] Document: {doc} ({heading})\nContent: {content}");
            citationIndex++;
        }

        // 3. Assemble Grounded Enterprise System Prompt
        var chatHistory = new ChatHistory();
        chatHistory.AddSystemMessage("""
            You are the official enterprise technical assistant.
            Answer the user's question STRICTLY based on the provided context below.
            
            RULES:
            1. Every claim must reference its source using the exact format: [Citation X].
            2. If the context does not contain enough information to answer definitively, state:
               'I do not have sufficient documentation to answer this question accurately.'
            3. Never fabricate or hallucinate features, URLs, or parameters.
            
            CONTEXT:
            """ + "\n\n" + string.Join("\n\n---\n\n", retrievedContexts));

        chatHistory.AddUserMessage(userQuestion);

        // 4. Stream response back to client with low latency
        var stream = _chatService.GetStreamingChatMessageContentsAsync(
            chatHistory, 
            executionSettings: new PromptExecutionSettings { ExtensionData = new() { ["temperature"] = 0.1 } },
            cancellationToken: ct);

        await foreach (var message in stream)
        {
            if (!string.IsNullOrEmpty(message.Content))
            {
                yield return message.Content;
            }
        }
    }
}

6. Registering in ASP.NET Core 9 Program.cs

Configure dependency injection and establish native connection pooling using the latest .NET 9 service extensions:

var builder = WebApplication.CreateBuilder(args);

// Register Azure OpenAI Embedding & Chat Completion
builder.Services.AddAzureOpenAITextEmbeddingGeneration(
    deploymentName: "text-embedding-3-large",
    endpoint: builder.Configuration["AzureOpenAI:Endpoint"]!,
    apiKey: builder.Configuration["AzureOpenAI:ApiKey"]!);

builder.Services.AddAzureOpenAIChatCompletion(
    deploymentName: "gpt-4o",
    endpoint: builder.Configuration["AzureOpenAI:Endpoint"]!,
    apiKey: builder.Configuration["AzureOpenAI:ApiKey"]!);

// Register RAG Engine
builder.Services.AddScoped<IRagSearchService, ProductionRagService>();

var app = builder.Build();

// Streaming Server-Sent Events (SSE) Endpoint
app.MapGet("/api/rag/ask", async (string q, IRagSearchService ragService, HttpContext context, CancellationToken ct) =>
{
    context.Response.ContentType = "text/event-stream";
    await foreach (var token in ragService.AskStreamingAsync(q, ct))
    {
        await context.Response.WriteAsync($"data: {token}\n\n", ct);
        await context.Response.Body.FlushAsync(ct);
    }
});

app.Run();

7. Production Checklist & Cost Optimization (FinOps)

Before taking your .NET RAG pipeline live, apply this enterprise checklist:

Conclusion

Building production RAG in .NET 9 is not just about connecting an API; it is an engineering discipline requiring hybrid search, rigorous semantic chunking, and low-temperature grounded guardrails. By hosting vectors alongside relational business data in PostgreSQL with pgvector, your infrastructure remains simple, performant, and cost-efficient.