RAG + Postgres (pgvector) in E-commerce: Next.js + .NET

Artificial intelligence is transforming e-commerce, and RAG (Retrieval-Augmented Generation) combined with vector databases opens new possibilities for search, recommendations, and customer service. In this article, we'll show you how to build a RAG system based on PostgreSQL with the pgvector extension, integrated with Next.js and .NET API.

Why RAG in E-commerce?

Traditional full-text search has its limitations. Users often search for products using natural language, synonyms, or feature descriptions rather than exact names. RAG solves this problem by offering:

1. Semantic Product Search

Instead of keyword matching, the system understands user intent:

"cheap running shoes" → finds products described as "economical sports footwear"
"laptop for programming" → matches parameters (RAM, processor) without literal phrases
"gift for mom" → suggests categories based on context

2. Similar Items

Vector representation of products enables finding truly similar items - not just by category, but based on semantic similarity of descriptions, parameters, and reviews.

3. Intelligent FAQ and Order Q&A

RAG enables building a chatbot that:

Answers order status questions based on database data
Generates FAQ responses using knowledge from documentation and history
Reduces support load through automatic responses

4. Personalized Recommendations

Embeddings enable advanced recommendations considering browsing history, preferences, and shopping context.

System Architecture

Our system consists of four main components:

Frontend: Next.js 15 App Router

Server Components for SEO and performance
API Routes for backend communication
Streaming UI for progressive result display

Backend: .NET Minimal API

Embedding generation (OpenAI/Azure/Local)
Result ranking and reranking
Database query orchestration

Database: PostgreSQL 16 + pgvector

Vector embeddings storage
HNSW index for fast search
Hybrid search (BM25 + vector)

Cache: Redis

Query embeddings caching
Search results storage
Session storage for chatbot

┌─────────────┐      ┌─────────────────┐      ┌──────────────────┐
│  Next.js    │─────▶│  .NET API       │─────▶│  PostgreSQL      │
│  (UI/SSR)   │      │  (Embeddings)   │      │  + pgvector      │
└─────────────┘      └─────────────────┘      └──────────────────┘
       │                      │                         │
       │                      ▼                         │
       │              ┌─────────────┐                   │
       └─────────────▶│   Redis     │◀──────────────────┘
                      │   (Cache)   │
                      └─────────────┘

Step-by-Step Implementation

Step 1: PostgreSQL + pgvector Setup

Install the pgvector extension in PostgreSQL 16:

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

-- Products table with embeddings
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    price DECIMAL(10, 2),
    metadata JSONB,
    -- Embedding vector (OpenAI ada-002 = 1536 dimensions)
    embedding vector(1536),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- HNSW index for fast vector search
-- m = number of connections (16-64, higher = better quality, slower build)
-- ef_construction = dynamic list size (64-200, higher = better quality)
CREATE INDEX ON products 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Index for hybrid search (text + vector)
CREATE INDEX ON products USING GIN(to_tsvector('english', name || ' ' || description));

-- Function to update timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_products_updated_at 
    BEFORE UPDATE ON products 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

Step 2: Embedding Generation Pipeline

In .NET API, create a service for generating and managing embeddings:

// EmbeddingService.cs
using Azure.AI.OpenAI;
using Npgsql;
using System.Text.Json;

public class EmbeddingService
{
    private readonly OpenAIClient _openAIClient;
    private readonly IConfiguration _config;
    private readonly ILogger<EmbeddingService> _logger;
    private readonly string _connectionString;
    
    public EmbeddingService(OpenAIClient openAIClient, IConfiguration config, ILogger<EmbeddingService> logger)
    {
        _openAIClient = openAIClient;
        _config = config;
        _logger = logger;
        _connectionString = config.GetConnectionString("PostgreSQL");
    }
    
    // Generate embeddings for single text
    public async Task<float[]> GenerateEmbeddingAsync(string text)
    {
        var options = new EmbeddingsOptions("text-embedding-ada-002", new[] { text });
        var response = await _openAIClient.GetEmbeddingsAsync(options);
        
        return response.Value.Data[0].Embedding.ToArray();
    }
    
    // Batch processing for multiple products
    public async Task GenerateProductEmbeddingsAsync(int batchSize = 100)
    {
        await using var conn = new NpgsqlConnection(_connectionString);
        await conn.OpenAsync();
        
        // Find products without embeddings
        var query = @"
            SELECT id, name, description, category 
            FROM products 
            WHERE embedding IS NULL 
            LIMIT @batchSize";
            
        await using var cmd = new NpgsqlCommand(query, conn);
        cmd.Parameters.AddWithValue("batchSize", batchSize);
        
        await using var reader = await cmd.ExecuteReaderAsync();
        var products = new List<(int id, string text)>();
        
        while (await reader.ReadAsync())
        {
            var id = reader.GetInt32(0);
            var name = reader.GetString(1);
            var description = reader.IsDBNull(2) ? "" : reader.GetString(2);
            var category = reader.IsDBNull(3) ? "" : reader.GetString(3);
            
            // Combine product information into single text
            var combinedText = $"{name}. {description}. Category: {category}";
            products.Add((id, combinedText));
        }
        
        await reader.CloseAsync();
        
        // Generate embeddings for each product
        foreach (var (id, text) in products)
        {
            try
            {
                var embedding = await GenerateEmbeddingAsync(text);
                
                // Save embedding to database
                await UpdateProductEmbeddingAsync(conn, id, embedding);
                
                // Rate limiting - OpenAI has 3000 RPM limits
                await Task.Delay(20); // ~50 req/sec
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error generating embedding for product {ProductId}", id);
            }
        }
    }
    
    private async Task UpdateProductEmbeddingAsync(
        NpgsqlConnection conn, 
        int productId, 
        float[] embedding)
    {
        var updateQuery = "UPDATE products SET embedding = @embedding WHERE id = @id";
        await using var cmd = new NpgsqlCommand(updateQuery, conn);
        cmd.Parameters.AddWithValue("id", productId);
        cmd.Parameters.AddWithValue("embedding", embedding);
        
        await cmd.ExecuteNonQueryAsync();
    }
}

Step 3: Semantic Search Endpoint

Build a Minimal API endpoint for search:

// Program.cs - Minimal API
using Microsoft.AspNetCore.Mvc;

var builder = WebApplication.CreateBuilder(args);

// Add services
builder.Services.AddSingleton<OpenAIClient>(sp => 
    new OpenAIClient(builder.Configuration["OpenAI:ApiKey"]));
builder.Services.AddScoped<EmbeddingService>();
builder.Services.AddStackExchangeRedisCache(options =>
{
    options.Configuration = builder.Configuration["Redis:Connection"];
});

var app = builder.Build();

// Semantic search endpoint
app.MapPost("/api/search", async (
    [FromBody] SearchRequest request,
    [FromServices] EmbeddingService embeddingService,
    [FromServices] IDistributedCache cache,
    [FromServices] IConfiguration config) =>
{
    try
    {
        // 1. Check cache
        var cacheKey = $"search:{request.Query}:{request.Limit}";
        var cachedResult = await cache.GetStringAsync(cacheKey);
        
        if (!string.IsNullOrEmpty(cachedResult))
        {
            return Results.Ok(JsonSerializer.Deserialize<SearchResponse>(cachedResult));
        }
        
        // 2. Generate embedding for query
        var queryEmbedding = await embeddingService.GenerateEmbeddingAsync(request.Query);
        
        // 3. Search similar products in PostgreSQL
        var connectionString = config.GetConnectionString("PostgreSQL");
        await using var conn = new NpgsqlConnection(connectionString);
        await conn.OpenAsync();
        
        // Vector search with <-> operator (cosine distance)
        var query = @"
            SELECT 
                id,
                name,
                description,
                price,
                category,
                metadata,
                1 - (embedding <-> @queryEmbedding) as similarity
            FROM products
            WHERE embedding IS NOT NULL
            ORDER BY embedding <-> @queryEmbedding
            LIMIT @limit";
        
        await using var cmd = new NpgsqlCommand(query, conn);
        cmd.Parameters.AddWithValue("queryEmbedding", queryEmbedding);
        cmd.Parameters.AddWithValue("limit", request.Limit);
        
        var results = new List<ProductResult>();
        await using var reader = await cmd.ExecuteReaderAsync();
        
        while (await reader.ReadAsync())
        {
            results.Add(new ProductResult
            {
                Id = reader.GetInt32(0),
                Name = reader.GetString(1),
                Description = reader.IsDBNull(2) ? null : reader.GetString(2),
                Price = reader.GetDecimal(3),
                Category = reader.IsDBNull(4) ? null : reader.GetString(4),
                Metadata = reader.IsDBNull(5) ? null : 
                    JsonSerializer.Deserialize<Dictionary<string, object>>(reader.GetString(5)),
                Similarity = reader.GetFloat(6)
            });
        }
        
        var response = new SearchResponse { Results = results };
        
        // 4. Save in cache (5 minutes)
        await cache.SetStringAsync(
            cacheKey, 
            JsonSerializer.Serialize(response),
            new DistributedCacheEntryOptions { AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5) }
        );
        
        return Results.Ok(response);
    }
    catch (Exception ex)
    {
        return Results.Problem($"Search failed: {ex.Message}");
    }
});

app.Run();

// DTOs
public record SearchRequest(string Query, int Limit = 20);
public record SearchResponse { public List<ProductResult> Results { get; set; } }
public record ProductResult
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string? Description { get; set; }
    public decimal Price { get; set; }
    public string? Category { get; set; }
    public Dictionary<string, object>? Metadata { get; set; }
    public float Similarity { get; set; }
}

Step 4: Next.js Integration

In Next.js, create API route and search component:

// app/api/products/search/route.ts
import { NextRequest, NextResponse } from 'next/server';

const DOTNET_API_URL = process.env.DOTNET_API_URL || 'http://localhost:5000';

export async function POST(request: NextRequest) {
  try {
    const { query, limit = 20 } = await request.json();
    
    if (!query || typeof query !== 'string') {
      return NextResponse.json(
        { error: 'Query is required' },
        { status: 400 }
      );
    }
    
    // Call .NET API
    const response = await fetch(`${DOTNET_API_URL}/api/search`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
      },
      body: JSON.stringify({ query, limit }),
    });
    
    if (!response.ok) {
      throw new Error(`API error: ${response.statusText}`);
    }
    
    const data = await response.json();
    
    return NextResponse.json(data);
  } catch (error) {
    console.error('Search error:', error);
    return NextResponse.json(
      { error: 'Search failed' },
      { status: 500 }
    );
  }
}

Embedding Update Strategies

Embeddings must stay current when product data changes:

1. Trigger-based Updates

-- Trigger to mark products for re-embedding
CREATE OR REPLACE FUNCTION mark_for_reembedding()
RETURNS TRIGGER AS $$
BEGIN
    -- If name, description or category changed, reset embedding
    IF (NEW.name != OLD.name OR 
        NEW.description != OLD.description OR 
        NEW.category != OLD.category) THEN
        NEW.embedding = NULL;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_reembed_trigger
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION mark_for_reembedding();

2. Batch Processing with Queue

Use Redis Queue or Background Job for asynchronous processing:

// BackgroundEmbeddingService.cs
public class BackgroundEmbeddingService : BackgroundService
{
    private readonly IServiceProvider _services;
    private readonly ILogger<BackgroundEmbeddingService> _logger;
    
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            using var scope = _services.CreateScope();
            var embeddingService = scope.ServiceProvider.GetRequiredService<EmbeddingService>();
            
            try
            {
                // Process 100 products every 5 minutes
                await embeddingService.GenerateProductEmbeddingsAsync(100);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error in background embedding service");
            }
            
            await Task.Delay(TimeSpan.FromMinutes(5), stoppingToken);
        }
    }
}

Hybrid Search: BM25 + Vector

For best results, combine full-text search with vector search:

-- Hybrid search with weights
WITH vector_results AS (
    SELECT 
        id,
        name,
        description,
        price,
        (1 - (embedding <-> @queryEmbedding)) * 0.7 as vector_score
    FROM products
    WHERE embedding IS NOT NULL
    ORDER BY embedding <-> @queryEmbedding
    LIMIT 50
),
text_results AS (
    SELECT 
        id,
        ts_rank(to_tsvector('english', name || ' ' || description), 
                plainto_tsquery('english', @queryText)) * 0.3 as text_score
    FROM products
    WHERE to_tsvector('english', name || ' ' || description) @@ 
          plainto_tsquery('english', @queryText)
)
SELECT 
    p.id,
    p.name,
    p.description,
    p.price,
    COALESCE(v.vector_score, 0) + COALESCE(t.text_score, 0) as combined_score
FROM products p
LEFT JOIN vector_results v ON p.id = v.id
LEFT JOIN text_results t ON p.id = t.id
WHERE v.id IS NOT NULL OR t.id IS NOT NULL
ORDER BY combined_score DESC
LIMIT 20;

UX and Cost Optimization

Latency (p95)

Target: < 500ms for 95% of queries
Cache hit rate: > 60% (Redis)
HNSW ef_search: 40-100 (higher = better quality, slower)

Embedding Costs

For OpenAI text-embedding-ada-002:

Price: $0.0001 / 1K tokens
Example: 10,000 products × 200 tokens average = 2M tokens = $0.20
Updates: ~$0.01 daily with 5% products changed

Data Security

Encryption: PostgreSQL SSL, encrypted backups
Access control: Role-based access (RBAC) in .NET
Rate limiting: Max 100 req/minute per user
Input sanitization: Query validation, max 500 characters

Deployment Checklist

[ ] Install PostgreSQL 16 + pgvector
[ ] Create tables and indexes (HNSW)
[ ] Configure .NET API with OpenAI Client
[ ] Implement EmbeddingService
[ ] Run batch processing for existing products
[ ] Configure Redis cache
[ ] Build Next.js API routes
[ ] Add search UI component
[ ] Set up background job for updates
[ ] Test latency and accuracy
[ ] Monitor costs and API usage
[ ] Add logging and metrics (Application Insights / Grafana)

Use Cases

1. FAQ Chatbot

Documentation embeddings + RAG = intelligent bot answering customer questions.

2. "You Might Also Like" Recommendations

-- Products similar to ID=123
SELECT id, name, price,
       1 - (embedding <-> (SELECT embedding FROM products WHERE id = 123)) as similarity
FROM products
WHERE id != 123 AND embedding IS NOT NULL
ORDER BY embedding <-> (SELECT embedding FROM products WHERE id = 123)
LIMIT 6;

3. Automatic Categorization

New products can be automatically categorized by comparing their embeddings with existing categories.

Summary

RAG with PostgreSQL pgvector offers powerful capabilities for e-commerce:

Semantic search instead of keyword matching
Scalable to millions of products (HNSW index)
Low costs compared to managed vector DBs
Easy integration with existing Next.js + .NET stack

Ready to implement? Contact us for a search audit or proof-of-concept in 1-2 weeks.

RAG + Postgres (pgvector) in E-commerce - Next.js + .NET