Dedicated vector DB (Qdrant, Weaviate, Pinecone):
  ✓ Optimised for vector search at massive scale
  ✓ Rich metadata filtering
  ✗ Extra infrastructure to manage
  ✗ Data lives in two systems (your DB + vector DB)
  ✗ Sync complexity — keep them consistent

pgvector:
  ✓ Your data and vectors in one PostgreSQL database
  ✓ Joins between vectors and regular tables (native SQL)
  ✓ Same backup/replication as your existing data
  ✓ HNSW index — fast approximate nearest-neighbour search
  ✗ Not as fast as dedicated vector DBs at 1B+ vectors
  → Right choice for < 10M vectors with mixed query patterns

-- Run once on your PostgreSQL database
CREATE EXTENSION IF NOT EXISTS vector;

# Docker — use pgvector-enabled image
docker run -e POSTGRES_PASSWORD=dev -p 5432:5432 pgvector/pgvector:pg16

<PackageReference Include="Pgvector.EntityFrameworkCore" Version="0.*" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="9.*" />

using Pgvector;

// Product entity with a 1536-dimension embedding (text-embedding-3-small)
public class Product
{
    public int    Id          { get; set; }
    public string Name        { get; set; } = "";
    public string Description { get; set; } = "";
    public string Category    { get; set; } = "";
    public decimal Price      { get; set; }

    // The vector column — stores the embedding
    public Vector? Embedding  { get; set; }
}

// DbContext — register pgvector and configure the column
public class AppDbContext(DbContextOptions<AppDbContext> opts) : DbContext(opts)
{
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder model)
    {
        model.HasPostgresExtension("vector");   // ensure extension exists

        model.Entity<Product>(entity =>
        {
            entity.Property(p => p.Embedding)
                .HasColumnType("vector(1536)");   // dimensions must match the embedding model

            // HNSW index — fast approximate nearest-neighbour, built once
            entity.HasIndex(p => p.Embedding)
                .HasMethod("hnsw")
                .HasOperators("vector_cosine_ops");   // cosine similarity
        });
    }
}

// Register with pgvector support
builder.Services.AddDbContext<AppDbContext>(opts =>
    opts.UseNpgsql(connectionString,
        npgsql => npgsql.UseVector()));   // enables Vector type mapping

public class ProductEmbeddingService(
    AppDbContext context,
    IEmbeddingGenerator<string, Embedding<float>> embedder)
{
    public async Task GenerateAndStoreAsync(int productId, CancellationToken ct)
    {
        var product = await context.Products.FindAsync([productId], ct)
            ?? throw new NotFoundException(nameof(Product), productId);

        // Create a rich text representation for embedding
        var text = $"{product.Name}. {product.Description}. Category: {product.Category}.";

        var result    = await embedder.GenerateAsync([text], cancellationToken: ct);
        var floats    = result[0].Vector.ToArray();

        product.Embedding = new Vector(floats);
        await context.SaveChangesAsync(ct);
    }

    public async Task GenerateBatchAsync(int batchSize = 100, CancellationToken ct = default)
    {
        // Find products without embeddings
        var products = await context.Products
            .Where(p => p.Embedding == null)
            .Take(batchSize)
            .ToListAsync(ct);

        if (products.Count == 0) return;

        var texts  = products.Select(p => $"{p.Name}. {p.Description}. Category: {p.Category}.").ToList();
        var results = await embedder.GenerateAsync(texts, cancellationToken: ct);

        for (int i = 0; i < products.Count; i++)
            products[i].Embedding = new Vector(results[i].Vector.ToArray());

        await context.SaveChangesAsync(ct);
    }
}

public class ProductSearchService(
    AppDbContext context,
    IEmbeddingGenerator<string, Embedding<float>> embedder)
{
    // Cosine similarity search — returns most semantically similar products
    public async Task<List<ProductSearchResult>> SearchAsync(
        string query,
        int topK = 10,
        CancellationToken ct = default)
    {
        var queryResult   = await embedder.GenerateAsync([query], cancellationToken: ct);
        var queryVector   = new Vector(queryResult[0].Vector.ToArray());

        return await context.Products
            .Where(p => p.Embedding != null)
            .OrderBy(p => p.Embedding!.CosineDistance(queryVector))   // lowest = most similar
            .Take(topK)
            .Select(p => new ProductSearchResult(
                p.Id,
                p.Name,
                p.Category,
                p.Price,
                1 - p.Embedding!.CosineDistance(queryVector)))   // similarity score 0–1
            .ToListAsync(ct);
    }

    // Hybrid search — combine vector similarity with keyword filter
    public async Task<List<ProductSearchResult>> HybridSearchAsync(
        string query,
        string? category = null,
        decimal? maxPrice = null,
        int topK = 10,
        CancellationToken ct = default)
    {
        var queryResult = await embedder.GenerateAsync([query], ct);
        var queryVector = new Vector(queryResult[0].Vector.ToArray());

        var q = context.Products.Where(p => p.Embedding != null);

        if (category is not null)
            q = q.Where(p => p.Category == category);

        if (maxPrice is not null)
            q = q.Where(p => p.Price <= maxPrice);

        return await q
            .OrderBy(p => p.Embedding!.CosineDistance(queryVector))
            .Take(topK)
            .Select(p => new ProductSearchResult(
                p.Id, p.Name, p.Category, p.Price,
                1 - p.Embedding!.CosineDistance(queryVector)))
            .ToListAsync(ct);
    }

    // Find similar products (given a product, find related ones)
    public async Task<List<ProductSearchResult>> FindSimilarAsync(
        int productId,
        int topK = 5,
        CancellationToken ct = default)
    {
        var product = await context.Products.FindAsync([productId], ct);
        if (product?.Embedding is null) return [];

        return await context.Products
            .Where(p => p.Id != productId && p.Embedding != null)
            .OrderBy(p => p.Embedding!.CosineDistance(product.Embedding))
            .Take(topK)
            .Select(p => new ProductSearchResult(
                p.Id, p.Name, p.Category, p.Price,
                1 - p.Embedding!.CosineDistance(product.Embedding!)))
            .ToListAsync(ct);
    }
}

public record ProductSearchResult(int Id, string Name, string Category, decimal Price, double Similarity);

// EF Core migration — pgvector column and HNSW index
public partial class AddProductEmbeddings : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("CREATE EXTENSION IF NOT EXISTS vector");

        migrationBuilder.AddColumn<Vector>(
            name:       "Embedding",
            table:      "Products",
            type:       "vector(1536)",
            nullable:   true);

        // HNSW index — build after populating embeddings for better performance
        migrationBuilder.Sql("""
            CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_embedding_hnsw
            ON "Products" USING hnsw ("Embedding" vector_cosine_ops)
            WITH (m = 16, ef_construction = 64)
            """,
            suppressTransaction: true);   // HNSW index cannot be built in a transaction
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn("Embedding", "Products");
    }
}

// Retrieval-Augmented Generation — use pgvector to fetch context for LLM
public class ProductQnA(
    ProductSearchService search,
    IChatClient chatClient)
{
    public async Task<string> AnswerAsync(string question, CancellationToken ct)
    {
        // 1. Retrieve relevant products
        var products = await search.SearchAsync(question, topK: 5, ct);

        // 2. Build context for the LLM
        var context = string.Join("\n\n", products.Select(p =>
            $"Product: {p.Name} (Category: {p.Category}, Price: {p.Price:C})\nSimilarity: {p.Similarity:P0}"));

        // 3. Ask the LLM with context
        var messages = new List<ChatMessage>
        {
            new(ChatRole.System, """
                You are a product advisor. Answer questions based ONLY on the provided product catalogue.
                If the answer is not in the catalogue, say so.
                """),
            new(ChatRole.User, $"Product catalogue:\n{context}\n\nQuestion: {question}"),
        };

        var response = await chatClient.CompleteAsync(messages, cancellationToken: ct);
        return response.Message.Text ?? "";
    }
}

pgvector supports two index types:

HNSW (Hierarchical Navigable Small World):
  - Build once — no training required
  - Fast query time — O(log n)
  - More memory than IVFFlat
  - Parameters: m (connections per node, default 16), ef_construction (build quality, default 64)
  - ef_search at query time: SET hnsw.ef_search = 100; (higher = more accurate but slower)
  → Recommended for most use cases

IVFFlat (Inverted File with Flat):
  - Requires training (needs existing data to cluster)
  - Lower memory than HNSW
  - Parameters: lists (number of clusters, sqrt(rows) is a good start)
  → Better for very large datasets (> 1M vectors)

For < 1M vectors and typical SaaS: HNSW with default parameters is the right choice.