Vector Search with EF Core (2): PostgreSQL pgvector, HNSW Index and Dapper

Version note: The code in this post was built and run with .NET 10, Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3, Pgvector.EntityFrameworkCore 0.3.0, Pgvector.Dapper 0.3.1 and Dapper 2.1.89 against PostgreSQL 17.11 + pgvector 0.8.7 and Ollama 0.34.4 (nomic-embed-text).

Guess First

You built the HNSW (Hierarchical Navigable Small World) index on the doc_chunks table with vector_l2_ops. Your queries use the cosine distance the embedding model recommends, the <=> operator. Even if you tell PostgreSQL "do not use a sequential scan", will the query use the index? If not, do you get an error, or does it silently run slow?

In this post we set up the series' vector store: PostgreSQL with pgvector in Docker, mapping and an HNSW index with EF (Entity Framework) Core, search with a permission filter, checking the index with EXPLAIN, halving storage with halfvec, and the same query written with Dapper. The PostgreSQL counterpart of the model we built but could not run for SQL (Structured Query Language) Server in the sixth post runs for real this time.

Docker, Connection and Model

pgvector's official image ships PostgreSQL with the extension installed. In the fifth post we saw that a parallel HNSW build does not fit into Docker's default 64 MB /dev/shm, so I pass --shm-size:

$ docker run -d --name ragwork1-pg --shm-size=1g \
    -e POSTGRES_PASSWORD="$PGPW" -e POSTGRES_DB=bookstore_rag \
    -p 19701:5432 pgvector/pgvector:pg17

On the connection side there is a detail the pgvector-dotnet README stresses: if you build an NpgsqlDataSource yourself, you have to call UseVector() both on the data source builder and in UseNpgsql's options. One teaches Npgsql the vector type, the other teaches EF Core:

// Register the vector type on the Npgsql data source and give EF Core the same data source
NpgsqlDataSourceBuilder dataSourceBuilder = new(connectionString);
dataSourceBuilder.UseVector();
await using NpgsqlDataSource dataSource = dataSourceBuilder.Build();

DbContextOptions<RagDbContext> options = new DbContextOptionsBuilder<RagDbContext>()
    .UseNpgsql(dataSource, o => o.UseVector())
    .Options;

The model is the PostgreSQL counterpart of the SQL Server model from the sixth post; the vector type is Pgvector.Vector rather than SqlVector<float>. The HNSW index is defined directly in the model:

using Microsoft.EntityFrameworkCore;
using Pgvector;

namespace Bookstore.Rag.Postgres;

public class DocChunk
{
    public required string Id { get; set; }
    public required string DocId { get; set; }
    public required string Title { get; set; }
    public required string Section { get; set; }
    public required string Content { get; set; }
    public required string SourcePath { get; set; }
    public required string Tenant { get; set; }
    public required string Acl { get; set; }
    public DateOnly UpdatedAt { get; set; }
    public required string ContentHash { get; set; }
    public required string EmbeddingModel { get; set; }
    public required Vector Embedding { get; set; }
}

public class RagDbContext(DbContextOptions<RagDbContext> options) : DbContext(options)
{
    public DbSet<DocChunk> DocChunks => Set<DocChunk>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasPostgresExtension("vector");

        modelBuilder.Entity<DocChunk>(e =>
        {
            e.ToTable("doc_chunks");
            // Column names in snake_case: SQL and Dapper use the same names
            foreach (var property in e.Metadata.GetProperties())
            {
                property.SetColumnName(string.Concat(property.Name.Select((ch, i) =>
                    i > 0 && char.IsUpper(ch) ? "_" + char.ToLowerInvariant(ch) : char.ToLowerInvariant(ch).ToString())));
            }

            e.Property(c => c.Embedding).HasColumnType("vector(768)");

            e.HasIndex(c => c.Embedding)
                .HasDatabaseName("ix_doc_chunks_embedding")
                .HasMethod("hnsw")
                .HasOperators("vector_cosine_ops")
                .HasStorageParameter("m", 16)
                .HasStorageParameter("ef_construction", 64);

            e.HasIndex(c => new { c.Tenant, c.Acl });
        });
    }
}

HasPostgresExtension("vector") makes migrations or EnsureCreated add CREATE EXTENSION. HasMethod("hnsw") sets the index type, HasOperators("vector_cosine_ops") the operator class, and HasStorageParameter sets m and ef_construction. The DDL (Data Definition Language) I got without opening a connection:

$ dotnet run -- schema
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE doc_chunks (
    id text NOT NULL,
    doc_id text NOT NULL,
    title text NOT NULL,
    section text NOT NULL,
    content text NOT NULL,
    source_path text NOT NULL,
    tenant text NOT NULL,
    acl text NOT NULL,
    updated_at date NOT NULL,
    content_hash text NOT NULL,
    embedding_model text NOT NULL,
    embedding vector(768) NOT NULL,
    CONSTRAINT "PK_doc_chunks" PRIMARY KEY (id)
);
CREATE INDEX ix_doc_chunks_embedding ON doc_chunks USING hnsw (embedding vector_cosine_ops) WITH (ef_construction=64, m=16);
CREATE INDEX "IX_doc_chunks_tenant_acl" ON doc_chunks (tenant, acl);

The table matches the doc_chunks table we created with SQL for incremental sync in the third post, and the index is USING hnsw (embedding vector_cosine_ops) as expected.

Loading the Chunks

For every document, the chunks produced by the standard chunker are embedded in a single request and written with the document's hash and the model's name (incremental updates from the third post rely on these two columns):

static async Task<int> LoadAsync(RagDbContext db, IEmbeddingGenerator<string, Embedding<float>> embedder, List<SourceDocument> docs)
{
    foreach (SourceDocument doc in docs)
    {
        List<Chunk> chunks = Chunkers.Section([doc]);
        GeneratedEmbeddings<Embedding<float>> embeddings =
            await embedder.GenerateAsync(chunks.Select(c => "search_document: " + c.Content));
        string hash = IncrementalIndexer.DocumentHash(doc);
        db.DocChunks.AddRange(chunks.Select((c, i) => new DocChunk
        {
            Id = c.Id,
            DocId = c.DocId,
            Title = c.Title,
            Section = c.Section,
            Content = c.Content,
            SourcePath = c.SourcePath,
            Tenant = c.Tenant,
            Acl = c.Acl,
            UpdatedAt = c.UpdatedAt,
            ContentHash = hash,
            EmbeddingModel = embeddings[i].ModelId ?? "unknown",
            Embedding = new Vector(embeddings[i].Vector)
        }));
    }
    return await db.SaveChangesAsync();
}

$ dotnet run -- load
inserted 53 chunks in 368 ms

$ dotnet run -- recall
pgvector recall@1=16/30 recall@5=25/30

53 chunks were written. Do not read the time as embedding speed: in these experiments embeddings produced earlier come from a disk cache in the work folder, so the 368 ms is mostly inserts. The more important line is the second one: search on pgvector gave exactly the result we measured with the in-memory list in the second post (recall@1 16/30, recall@5 25/30). Storage changed, retrieval quality did not; that is how it should be.

Searching with CosineDistance and a Permission Filter

Pgvector.EntityFrameworkCore offers extension methods on Vector such as L2Distance, MaxInnerProduct, CosineDistance and L1Distance; EF Core translates them to pgvector operators. The search query keeps the tenant and permission filter, the ordering and the projection together:

static IQueryable<SearchHit> SearchQuery(RagDbContext db, Vector queryVector, string tenant, string[] acl, int k) =>
    db.DocChunks
        .Where(c => c.Tenant == tenant && acl.Contains(c.Acl))
        .OrderBy(c => c.Embedding.CosineDistance(queryVector))
        .Select(c => new SearchHit(c.Id, c.DocId, c.Section, c.Embedding.CosineDistance(queryVector)))
        .Take(k);

public sealed record SearchHit(string Id, string DocId, string Section, double Distance);

$ dotnet run -- sql
-- @queryVector='[0,0,0,...]' (DbType = Object)
-- @tenant='bookstore'
-- @acl={ 'public' } (DbType = Object)
-- @p='5'
SELECT d.id, d.doc_id, d.section, d.embedding <=> @queryVector
FROM doc_chunks AS d
WHERE d.tenant = @tenant AND d.acl = ANY (@acl)
ORDER BY d.embedding <=> @queryVector
LIMIT @p

The generated SQL is exactly what we want: the filter in WHERE, ordering by embedding <=> @queryVector, and no embedding column in the SELECT (I shortened the zero vector in the parameter line to keep it readable). With a real question ("how much is express delivery?"):

$ dotnet run -- ask "Ekspres teslimat ücreti ne kadar?"
0.203  kargo-ve-teslimat#1          Ekspres teslimat
0.282  kargo-ve-teslimat#0          Standart kargo
0.314  kargo-ve-teslimat#5          Teslim edilemeyen gönderiler

Distance is cosine distance (1 minus cosine similarity); smaller is closer. The express delivery section is on top with 0.203. Let's retry the permission leak from the first post with the same query ("do you give your employees a discount?"):

$ dotnet run -- ask "Çalışanlarınıza indirim yapıyor musunuz?"
0.306  sadakat-programi#1           Puan kullanmak
0.338  kurumsal-satis-sozlesmesi#0  1. Toplu alım indirimi
0.339  sadakat-programi#2           Puanların süresi

$ dotnet run -- ask "Çalışanlarınıza indirim yapıyor musunuz?" public,staff
0.300  personel-indirimi#0          Personel indirimi
0.306  sadakat-programi#1           Puan kullanmak
0.330  personel-indirimi#2          Müşteriye söylenmemesi gerekenler

In the customer's search the personel-indirimi chunks never even become candidates; with the staff role the same question brings that chunk to the top with 0.300. Because the filter is inside the SQL, unauthorized rows never travel to the application.

Is the Index Used? EXPLAIN and Operator Classes

On a 53-row table PostgreSQL would not use the index anyway; scanning the whole table is cheaper. To see whether the index is usable by this query, I turned off enable_seqscan inside a transaction and asked for the plan, first with the index built with vector_l2_ops, then with vector_cosine_ops:

SqlMapper.AddTypeHandler(new VectorTypeHandler());
await using NpgsqlConnection conn = await dataSource.OpenConnectionAsync();
string ops = args[1] == "l2" ? "vector_l2_ops" : "vector_cosine_ops";
await conn.ExecuteAsync($"""
    DROP INDEX IF EXISTS ix_doc_chunks_embedding;
    CREATE INDEX ix_doc_chunks_embedding ON doc_chunks USING hnsw (embedding {ops}) WITH (m = 16, ef_construction = 64);
    """);
Vector queryVector = new(await embedder.GenerateVectorAsync("search_query: Ekspres teslimat ücreti ne kadar?"));
await using NpgsqlTransaction tx = await conn.BeginTransactionAsync();
await conn.ExecuteAsync("SET LOCAL enable_seqscan = off", transaction: tx);
IEnumerable<string> plan = await conn.QueryAsync<string>("""
    EXPLAIN (COSTS OFF)
    SELECT id FROM doc_chunks ORDER BY embedding <=> @queryVector LIMIT 5
    """, new { queryVector }, tx);
Console.WriteLine($"index: {ops}");
foreach (string line in plan) Console.WriteLine(line);

$ dotnet run -- explain l2
index: vector_l2_ops
Limit
  ->  Sort
        Sort Key: ((embedding <=> '[-0.02134457,0.02082554,...]'::vector))
        ->  Seq Scan on doc_chunks

$ dotnet run -- explain cosine
index: vector_cosine_ops
Limit
  ->  Index Scan using ix_doc_chunks_embedding on doc_chunks
        Order By: (embedding <=> '[-0.02134457,0.02082554,...]'::vector)

The answer to the opening question: no error, no warning. An index built with vector_l2_ops cannot serve an ordering that uses the cosine operator <=>, so the planner has only one option left: a sequential scan and a sort. Even turning off seq scan does not change that. With vector_cosine_ops as the operator class the plan becomes Index Scan using ix_doc_chunks_embedding. At 53 rows this mistake is invisible; at a million rows every query takes seconds and the cause shows up only in EXPLAIN.

The query-time hnsw.ef_search is set per session or per transaction. In the fifth post we saw the default of 40 give 0.830 recall on 100,000 synthetic vectors and 100 raise it to 0.898. In the Dapper example below I set it with SET LOCAL for that transaction only, so the setting does not leak when the connection returns to the pool.

Halving Storage with halfvec

pgvector's halfvec type stores each dimension as a 2-byte half-precision float. I converted the existing column and compared it with a separate HNSW index:

await conn.ExecuteAsync("""
    ALTER TABLE doc_chunks ADD COLUMN IF NOT EXISTS embedding_half halfvec(768);
    UPDATE doc_chunks SET embedding_half = embedding::halfvec(768);
    CREATE INDEX IF NOT EXISTS ix_doc_chunks_embedding_half ON doc_chunks USING hnsw (embedding_half halfvec_cosine_ops);
    """);

$ dotnet run -- halfvec
vector=3076 B/row, halfvec=1542 B/row, index: 224 kB vs 120 kB
identical top-5 with halfvec: 30/30

Per row it went from 3,076 bytes to 1,542, the index from 224 kB to 120 kB, and for 30 of 30 questions the top 5 results were identical. With nomic-embed-text's normalized, small-valued vectors, half precision did not change the ranking. Verify this with your own model and question set, but 1.5 GB saved at a million chunks is a gain worth measuring. According to the pgvector README, HNSW indexes support up to 2,000 dimensions for vector and 4,000 for halfvec; if you use a 3,072-dimension model, halfvec is the simplest way to build an HNSW index.

The Same Query with Dapper

When you want to go beyond EF Core's translation (session settings, CTEs (Common Table Expressions), hybrid search later on), writing SQL by hand is more comfortable. The mechanics are the same as in the Dapper series; the only addition is registering a type handler for Vector:

SqlMapper.AddTypeHandler(new VectorTypeHandler());
await using NpgsqlConnection conn = await dataSource.OpenConnectionAsync();
Vector queryVector = new(await embedder.GenerateVectorAsync("search_query: " + args[1]));
await using NpgsqlTransaction tx = await conn.BeginTransactionAsync();
// ef_search for this transaction only
await conn.ExecuteAsync("SET LOCAL hnsw.ef_search = 100", transaction: tx);
IEnumerable<ChunkHit> hits = await conn.QueryAsync<ChunkHit>("""
    SELECT id, section, embedding <=> @queryVector AS distance
    FROM doc_chunks
    WHERE tenant = @tenant AND acl = ANY(@acl) AND updated_at >= @since
    ORDER BY embedding <=> @queryVector
    LIMIT 3
    """,
    new { queryVector, tenant = "bookstore", acl = new[] { "public" }, since = new DateOnly(2026, 1, 1) }, tx);
foreach (ChunkHit h in hits) Console.WriteLine($"{h.Distance:F3}  {h.Id,-28} {h.Section}");

$ dotnet run -- dapper "Ekspres teslimat ücreti ne kadar?"
0.203  kargo-ve-teslimat#1          Ekspres teslimat
0.282  kargo-ve-teslimat#0          Standart kargo
0.314  kargo-ve-teslimat#5          Teslim edilemeyen gönderiler

The result matches the EF Core query; this time updated_at is in the filter as well. If you forget to register the type handler, Dapper rejects the query before running it; that is exactly what happened to the explain command on the first try for this post:

Unhandled exception. System.NotSupportedException: The member queryVector of type Pgvector.Vector cannot be used as a parameter value

Type Registration: UseVector and ReloadTypes

Npgsql loads and caches a database's types on the data source's first connection. If you create the vector extension while the application is running, on a connection from the same data source, that cache does not contain the vector type. I tried it on a new database:

// On a new database: create the extension on this connection and skip ReloadTypes
await using NpgsqlConnection conn = await dataSource.OpenConnectionAsync();
await conn.ExecuteAsync("DROP EXTENSION IF EXISTS vector CASCADE; CREATE EXTENSION vector;");
if (args.Contains("--reload")) await conn.ReloadTypesAsync();
try
{
    await using NpgsqlCommand cmd = new("SELECT @v::vector <=> @v::vector", conn);
    cmd.Parameters.AddWithValue("v", new Vector(new float[] { 1, 2, 3 }));
    Console.WriteLine($"distance = {await cmd.ExecuteScalarAsync()}");
}
catch (Exception ex)
{
    Console.WriteLine($"{ex.GetType().Name}: {ex.Message}");
}

$ PGDB=reloadtest dotnet run -- reload
InvalidCastException: Writing values of 'Pgvector.Vector' is not supported for parameters having no NpgsqlDbType or DataTypeName. Try setting one of these values to the expected database type..

$ PGDB=reloadtest dotnet run -- reload --reload
distance = 0

Without ReloadTypesAsync() Npgsql could not write the Vector parameter; with it the query ran. That is why the sync command in the third post calls ReloadTypesAsync() right after CREATE EXTENSION. In production, installing the extension through a migration before the application starts removes the problem entirely.

Production Notes

Think of the index together with the data. HNSW can be built on an empty table and is updated as rows are added, but for a bulk load building the index after loading is much faster. In a large re-embedding job (the model switch from the second post) fill the new column and build the index last.

Add B-tree indexes on filter columns. The (tenant, acl) index in the model is the prerequisite for the pre-filter plan we saw in the fifth post: with a selective tenant filter the planner can narrow the rows with this index first and then compute exact distances. For queries that combine a filter with HNSW, watch pgvector 0.8's iterative scan setting and the number of rows returned.

Tie settings to the transaction. Set things like hnsw.ef_search or hnsw.iterative_scan with SET LOCAL, not SET, so one request's setting does not carry over to the next in the connection pool.

Manage the schema with migrations. In this post I used EnsureCreated for the experiment; in production an EF Core migration produces CREATE EXTENSION thanks to HasPostgresExtension and the HNSW index thanks to HasMethod and HasOperators. Changing the index on a large table (raising m or fixing the operator class) can take minutes and slows writes meanwhile; run that migration in a separate maintenance window, ideally as a hand-written SQL step with CREATE INDEX CONCURRENTLY. Adding a hand-written SQL step with migrationBuilder.Sql, as shown in the EF Core series' migrations post, is exactly for this.

Do not read the embedding column for nothing. What we saw for SQL Server in the sixth post holds here too: DocChunks queries without a projection carry 3 KB per row. Use projections on listing screens and for hash checks.

Common Pitfalls

First, choosing an operator class that does not match the metric. There is no error; the index is silently not used. Check EXPLAIN for every new query shape and look for an Index Scan using ...embedding line in the plan.

Second, calling UseVector() in only one place. If you call it on the data source builder but forget it in the UseNpgsql options, EF Core cannot map the Vector property; the other way round, Npgsql cannot write the parameter.

Third, forgetting ReloadTypes(). If the extension was created while the application was running, connections from the same data source do not know the vector type; the InvalidCastException above.

Fourth, forgetting to register the type handler in Dapper. SqlMapper.AddTypeHandler(new VectorTypeHandler()) must be called once at startup; otherwise you get a NotSupportedException.

Fifth, believing "the index works" on a small table. At 53 rows the planner picks a seq scan and the results are correct; you only see that the index was built wrong on large data or with enable_seqscan = off.

A Small Experiment

★☆☆ Run the ask command with MaxInnerProduct instead of CosineDistance. Since nomic-embed-text's vectors are normalized, does the ranking stay the same? Which operator class would the index need?

★★☆ Add 100,000 rows of the synthetic data from the fifth post to doc_chunks (with a different tenant) and look at the plan of the query filtered by tenant = 'bookstore'. Which index does the planner pick, and is an iterative scan needed?

★★★ Add the embedding_half column to the EF Core model as a HalfVector property, define its HNSW index with halfvec_cosine_ops in the model and move SearchQuery to this column. Does recall change on the 30 questions?

Block A ends here: ingestion, chunking, embeddings and the vector store are in place. But retrieval still misses "bedava" versus "ücretsiz" and product codes like BKS-20114. In the next post we move to hybrid search, which combines keyword search with vector search, and Reciprocal Rank Fusion.