Vector search in the Postgres you already run
When a RAG prototype outgrows its in-memory list, the usual next step is a new piece of infrastructure: a dedicated vector database, with its own deployment, its own backups and a sync job that copies data into it. If you already run Postgres, there is a shorter step. pgvector adds a vector type, distance operators and approximate indexes to the database you already have, and the vectors sit in the same rows as the text they describe.
This post is what I would want to know before putting it in production: the column, the operators, the indexes and their knobs, the filtering trap, and how it looks from .NET.
Why start here
The case for pgvector is mostly not about vectors. It is about everything else Postgres already does. A chunk and its embedding are written in the same transaction, so there is never a vector for text that was rolled back. Deleting a document deletes its vectors, with no second system to forget. The vectors are in the same backups, the same replicas and the same point-in-time recovery. And the filters you need at query time, such as tenant, language, document type or permissions, are ordinary columns you can put in a WHERE clause or join against.
A dedicated vector database can still win at very large scale. I would move when a measurement says so, not on day one.
The column
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE chunks (
id bigserial PRIMARY KEY,
tenant_id int NOT NULL,
source text NOT NULL,
content text NOT NULL,
embedding vector(1536) NOT NULL
);
The number in vector(1536) is the dimension count, and it has to match the embedding model: a vector of
any other length is rejected. 1536 is what OpenAI's text-embedding-3-small returns by default. Each
element is a 4-byte float and each value has 8 bytes of overhead, so this column alone is about 6 KB per
row.
One limit to know up front. A vector column can store up to 16,000 dimensions, but both index types
only index up to 2,000. A 3072-dimension model can't be indexed as vector directly. The ways out are
halfvec, which stores 2-byte floats and indexes up to 4,000 dimensions, asking the model for fewer
dimensions if it supports that, or binary quantization. The pgvector README covers all three.
Three operators
Distance in pgvector is an operator, and the operator decides which index can serve the query:
<->is L2, or Euclidean, distance.<=>is cosine distance, which is 1 minus cosine similarity.<#>is the negative inner product. It is negative because Postgres only scans indexes in ascending order for these operators, so "largest inner product first" has to become "smallest negative first".
For normalized embeddings, and OpenAI's are normalized, cosine and inner product rank results the same way, and the README notes inner product is the faster one. I still default to cosine because it doesn't depend on the model normalizing its output. If you switch, switch the index too.
All three are distances: smaller means closer. If you want a similarity score for display, compute
1 - (embedding <=> $1) in the SELECT list, but don't order by it. The index is only used when ORDER BY
is the bare distance operator, ascending, with a LIMIT.
Two index types
Without an index, pgvector does exact search: it computes the distance to every row. Recall is perfect, and for a few thousand rows that is fast enough. An index makes the search approximate. It trades some recall for speed, which means adding one can change your results.
HNSW builds a layered graph. It has the better speed-to-recall trade-off and needs no training step, so you can create it on an empty table. The price is memory and build time.
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
m is the maximum number of connections per node, 16 by default, and ef_construction is the candidate
list size while building, 64 by default. Higher values buy recall with build time and insert speed. The
README says to keep the defaults unless recall is low, and I agree. The knob you will actually turn is at
query time: hnsw.ef_search, the candidate list size during a search, 40 by default.
IVFFlat groups the vectors into lists clusters and, at query time, searches only the probes lists
closest to the query. It builds faster and uses less memory, but recall is worse at the same speed, and
the clusters are learned from whatever data is in the table when the index is built. So create it after
loading data. The README's starting points are rows / 1000 lists up to a million rows, sqrt(rows)
above that, and sqrt(lists) for probes. ivfflat.probes defaults to 1, which is low.
The operator class has to match the operator: vector_cosine_ops for <=>, vector_l2_ops for <->,
vector_ip_ops for <#>. An index built for one won't serve the others. I start with HNSW and only
consider IVFFlat when build time or memory is a real constraint.
Filtering: where the rows go missing
This is the part that surprises people. A RAG query almost always has a filter:
SELECT id, source, embedding <=> $1 AS distance
FROM chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 5;
With an approximate index, the filter is applied after the index scan. HNSW collects ef_search
candidates, then Postgres drops the rows that belong to other tenants. If tenant 7 owns 10% of the rows,
about 4 of those 40 candidates survive on average. You asked for 5 and got 4, or 2, or none, and nothing
warned you.
The same cap applies without a filter: LIMIT 100 with the default ef_search returns at most 40 rows.
pgvector 0.8.0 added iterative index scans for this. When they are on, the scan keeps going until enough
rows pass the filter, or until it reaches hnsw.max_scan_tuples (20,000 by default) or
ivfflat.max_probes.
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SET LOCAL hnsw.iterative_scan = relaxed_order; -- or strict_order
-- the SELECT above
COMMIT;
strict_order keeps results exactly in distance order. relaxed_order allows slight disorder in return
for better recall, and you can re-sort in a materialized CTE if order matters. Managed Postgres services
don't always ship the newest extension, so check SELECT extversion FROM pg_extension WHERE extname = 'vector'; before you rely on it.
The other tools are plain Postgres. A B-tree index on tenant_id lets the planner pick an exact search
over one tenant's rows when the filter is selective, and that is often the best plan. A partial HNSW index
helps when there are only a few filter values. For real tenant isolation the README recommends list
partitioning or separate tables, because in a shared index one tenant's vectors affect the recall and
speed of every other tenant.
SET LOCAL matters in .NET: it ends with the transaction, so a setting can't leak into the next request
that borrows the same pooled connection.
From .NET with Npgsql
The Pgvector package adds the type mapping. Register it on the data source, and a Vector is an
ordinary parameter:
using Npgsql;
using Pgvector;
var builder = new NpgsqlDataSourceBuilder(connectionString);
builder.UseVector();
await using var dataSource = builder.Build();
await using var cmd = dataSource.CreateCommand("""
SELECT id, source, embedding <=> $1 AS distance
FROM chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 5
""");
cmd.Parameters.AddWithValue(new Vector(queryEmbedding));
cmd.Parameters.AddWithValue(tenantId);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
Console.WriteLine($"{reader.GetInt64(0)} {reader.GetString(1)} {reader.GetDouble(2):F3}");
$1 appears twice, which positional parameters allow. Vector also has a constructor that takes a
ReadOnlyMemory<float>, which is what Embedding<float>.Vector from Microsoft.Extensions.AI gives you, so
the output of the embedding generator goes straight in.
And with EF Core
Pgvector.EntityFrameworkCore (current versions support EF Core 9 and 10) maps the type and translates
distance methods to the operators:
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
using Pgvector;
using Pgvector.EntityFrameworkCore;
public sealed class Chunk
{
public long Id { get; set; }
public int TenantId { get; set; }
public string Source { get; set; } = "";
public string Content { get; set; } = "";
[Column(TypeName = "vector(1536)")]
public Vector Embedding { get; set; } = null!;
}
public sealed class AppDb(DbContextOptions<AppDb> options) : DbContext(options)
{
public DbSet<Chunk> Chunks => Set<Chunk>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasPostgresExtension("vector");
modelBuilder.Entity<Chunk>()
.HasIndex(c => c.Embedding)
.HasMethod("hnsw")
.HasOperators("vector_cosine_ops")
.HasStorageParameter("m", 16)
.HasStorageParameter("ef_construction", 64);
}
}
Register it with UseNpgsql(connectionString, o => o.UseVector()). If you build your own
NpgsqlDataSource, call UseVector() on that builder as well. Then the query reads like any other LINQ:
var q = new Vector(queryEmbedding);
var hits = await db.Chunks
.Where(c => c.TenantId == tenantId)
.OrderBy(c => c.Embedding.CosineDistance(q))
.Take(5)
.Select(c => new { c.Id, c.Source, Distance = c.Embedding.CosineDistance(q) })
.ToListAsync();
ToQueryString() shows ORDER BY c."Embedding" <=> @q LIMIT @p, so the HNSW index applies.
L2Distance and MaxInnerProduct map to the other two operators. The index is part of the model and
comes out of migrations like any other. For SET LOCAL, open a transaction with
db.Database.BeginTransactionAsync() and run ExecuteSqlRawAsync("SET LOCAL hnsw.ef_search = 100")
before the query.
What I would check before trusting it
- The plan.
EXPLAIN (ANALYZE, BUFFERS)shows whether the index is used at all. On a small table a sequential scan can be cheaper. - Recall. Run a set of real queries with
SET LOCAL enable_indexscan = offto get exact results, run them again with the index, and compare. That is the only way to know what the approximation costs you. Measure retrieval before you tune prompts is about building that set. - Short results. Log when a filtered query returns fewer rows than its LIMIT. It is the cheapest
signal that
ef_searchor iterative scans need attention. - Index size.
pg_size_pretty(pg_relation_size('index_name')). It doesn't have to fit in memory, but it is faster when it does. Build it with enoughmaintenance_work_mem, andCONCURRENTLYin production so writes aren't blocked.
And because this is Postgres, the full-text search for the keyword half of hybrid search is already in the same database, one join away.
