When teams add semantic search or RAG, the reflex is to bolt on a dedicated vector database. Often you don’t need to. If you already run PostgreSQL, the pgvector extension gives you vector storage and similarity search in the database you know — no new infrastructure, no separate service to operate. Here’s how to use it from .NET, and where it bites.
Why pgvector
Dedicated vector databases are excellent at massive scale. But for a lot of applications, pgvector on Postgres is the pragmatic choice:
- One database — your relational data and your vectors live together; you can filter by metadata and similarity in a single SQL query.
- Nothing new to operate — you already back up, monitor, and secure Postgres.
- It scales further than people expect — comfortably into millions of vectors with the right index.
The argument that actually decides it for most teams is the second one, and it is not really about pgvector at all. A separate vector store means a second thing to provision, secure, back up, patch, monitor and page someone about at three in the morning. It also means your embeddings and your rows can disagree: a document gets deleted in Postgres, the delete against the vector store fails, and now your search returns a citation to a record that no longer exists. Keeping both in one transaction makes that failure impossible rather than merely unlikely, and that is worth more than a percentile of query latency to almost everyone.
Step 1: enable the extension and create a table
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE documents (
id bigserial PRIMARY KEY,
tenant_id bigint NOT NULL,
content text NOT NULL,
embedding vector(1536) -- match your embedding model's dimensions
);The vector(1536) column stores the embedding; set the size to your model’s output dimensions.
Choose that number deliberately, because it is expensive to change later and it interacts with the index. The vector type itself accepts up to 16,000 dimensions, but both index types cap indexable vector columns at 2,000 dimensions. If you reach for a 3,072-dimension model, CREATE INDEX fails outright and you are left with sequential scans. There are two ways out: ask the embedding API for fewer dimensions if the model supports shortening, or build the index over a halfvec cast, which stores half-precision floats and raises the index ceiling to 4,000 dimensions while halving the storage. The recall loss from half precision is small in practice; the storage saving is not. One caveat if you shorten embeddings yourself by truncating the array rather than asking the API for a smaller output: re-normalise the result to unit length before storing it, or your cosine distances will quietly stop meaning what you think they mean.
Step 2: wire up Npgsql
The .NET support lives in the Pgvector NuGet package (with Pgvector.EntityFrameworkCore and Pgvector.Dapper on top of it for those stacks). Register the type mapping once on the data source, not per connection:
var builder = new NpgsqlDataSourceBuilder(connectionString);
builder.UseVector();
await using var dataSource = builder.Build();Skipping UseVector() is the first error most people hit, and the message is unhelpful — Npgsql simply reports that it cannot map the vector type, usually at the first parameter bind rather than at startup. Registering it on the data source means every connection from the pool knows about the type.
Step 3: insert embeddings from C#
Generate an embedding and store it alongside the text:
using Pgvector;
var vector = (await embedder.GenerateAsync(content)).Vector.ToArray();
await using var conn = await dataSource.OpenConnectionAsync();
await using var cmd = new NpgsqlCommand(
"INSERT INTO documents (tenant_id, content, embedding) VALUES ($1, $2, $3)", conn);
cmd.Parameters.AddWithValue(tenantId);
cmd.Parameters.AddWithValue(content);
cmd.Parameters.AddWithValue(new Vector(vector));
await cmd.ExecuteNonQueryAsync();Insert in batches when you are backfilling. Embedding a corpus one row per round trip is dominated by network latency, and a bulk load through COPY is an order of magnitude faster than a loop of INSERT statements. Build the index after the backfill, not before — every insert into an existing HNSW index does graph maintenance work you are about to invalidate anyway.
Step 4: search by similarity in SQL
pgvector adds distance operators, and picking the right one matters more than it looks:
| Operator | Distance |
|---|---|
<=> |
Cosine |
<-> |
L2 (Euclidean) |
<#> |
Negative inner product |
<+> |
L1 (taxicab) |
<=> is the safe default for text embeddings. Note that it returns distance, not similarity — 0 means identical and larger is worse, so you order ascending and you convert with 1 - (embedding <=> $1) if you want to show a score to a user. If your model returns unit-length vectors, which the common text embedding models do, cosine and inner product produce the same ranking and <#> is marginally cheaper; the difference rarely justifies the extra thing to remember.
SELECT content, 1 - (embedding <=> $1) AS similarity
FROM documents
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 5;That WHERE clause is the power move, and the main reason to choose pgvector — “the 5 most similar documents for this tenant” in one query, with the tenant filter enforced by the same database that enforces it everywhere else in your application. A bolt-on vector store makes this awkward at best and a security review finding at worst.
One habit worth forming: never put embedding in a SELECT *. A 1,536-dimension vector is roughly 6 KB, so returning it for every row of a result set moves megabytes across the wire for no reason, and it is the kind of overhead that hides successfully until someone profiles the endpoint.
Step 5: add an index — and match the operator class
Without an index, Postgres scans every row and computes every distance. That is exact, and it is fine up to perhaps tens of thousands of rows. Beyond that you want an approximate-nearest-neighbour index:
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);The operator class must match the operator you query with. An index built with vector_l2_ops is invisible to a query that orders by <=>, and Postgres will not warn you — it silently falls back to a sequential scan, which is correct but slow. This is the single most common “why is my vector search still taking two seconds” cause. Confirm with EXPLAIN ANALYZE that you see an index scan and not Seq Scan.
HNSW versus IVFFlat is a genuine trade-off rather than a formality. HNSW builds a navigable graph: better recall, faster queries, robust to the data changing underneath it, but the build is slow and the index wants to live in memory. IVFFlat partitions the vectors into lists and only searches the nearest few: much faster to build and smaller, but it needs representative data present at build time and recall degrades as you insert vectors that no longer match the clustering. Recommendation: HNSW unless build time or RAM is your binding constraint, and if you use IVFFlat, plan to rebuild it periodically.
Build tuning is worth thirty seconds of attention. HNSW build time collapses when the graph fits in maintenance_work_mem; if it doesn’t, the build emits a notice saying so and then grinds. Raise maintenance_work_mem for the session, raise max_parallel_maintenance_workers, then put them back. At query time, hnsw.ef_search (default 40) is your recall dial — raise it for better results at proportionally more work per query.
The filtering problem nobody warns you about
Here is the failure mode that catches people, and it is exactly the feature they chose pgvector for.
You ask for the top 5 documents for one tenant. The HNSW index does not know about tenant_id; it walks the graph, produces a candidate set governed by ef_search, and then the filter is applied. If that tenant owns 0.1% of your rows, most candidates get discarded and you get back two results instead of five — or none. Nothing errors. The query just quietly under-returns, and it does so more often for your smallest tenants, which is precisely where it is least likely to be noticed in testing.
There are three fixes and you should know all of them. Raise hnsw.ef_search so the candidate pool is large enough to survive the filter — cheapest, but it is a guess. Use iterative scan, which recent pgvector versions added (hnsw.iterative_scan), so the index keeps producing candidates until the query is satisfied rather than giving up after one pass — the right general answer. Or, when a filter column is genuinely low-cardinality and always present, build partial indexes per value so the filter is baked into the index and recall is unaffected. That last one only scales to a handful of values, so it suits a status or document_type column rather than a tenant id.
Sizing: what a million vectors actually costs
The arithmetic is simple enough to do in your head, and it is the number that decides whether this works. A 1,536-dimension vector is 4 × 1536 + 8 bytes, about 6 KB. A million of them is roughly 6 GB for the column alone, before row overhead and before the HNSW index, which is a graph over all of them and is not small. Approximate search only stays fast while that structure is in memory, so the practical question is not “can Postgres store this” — it can store far more — but “does my managed instance have enough RAM to keep the index resident”. If the answer is no, you get disk reads inside the graph walk and latency goes from milliseconds to something you have to apologise for.
That single calculation reframes the cost comparison honestly. pgvector is not free; you pay for it in instance memory. It is usually still cheaper than a second managed service, and it is dramatically cheaper in engineering time. But halfvec halving the storage stops being a micro-optimisation once you are near the edge of an instance size.
Hybrid search: the underrated argument
Pure vector search is bad at exact tokens. Ask for an error code, a part number or a surname, and semantic similarity will return things that are about the same topic while missing the row that literally contains the string. Postgres already has full-text search built in, so you can run both and fuse the rankings in one query — no second system, no application-side merge, no consistency problem. That combination is often a larger accuracy win than any amount of index tuning, and pgvector is one of the few options where you get it without extra infrastructure.
When not to use pgvector
Be honest about the ceiling. Once you are into hundreds of millions of vectors, or you need sharding across nodes for the vector workload specifically, or your write pattern is a continuous high-rate stream of updates that constantly churns the graph, a purpose-built store earns its operational cost. Same if you need a hard single-digit-millisecond latency budget at high concurrency, or features Postgres has no answer for like built-in multi-vector reranking. The useful thing is that none of those are where you start. Begin on Postgres, measure, and migrate the day the numbers say so — the embeddings are portable and the query is four lines.
Note: Npgsql’s pgvector type names and index options evolve; verify against the pgvector and Npgsql docs. The approach — a
vectorcolumn,<=>for similarity, an HNSW index — is stable.
Takeaway
pgvector gives .NET teams vector search without adopting a new database: enable the extension, register UseVector() on the Npgsql data source, insert embeddings, and query with <=> plus an HNSW index whose operator class matches the operator you query with — the mismatch is a silent sequential scan. Watch the 2,000-dimension index ceiling and reach for halfvec when you exceed it. Expect the filtered-search recall problem and solve it with hnsw.ef_search or iterative scan before a user reports missing results. Size the instance so the index stays in memory, because that is where the real cost lives. Keeping relational data and vectors in one transactional store removes an entire class of drift bugs, and for most applications that is worth more than the last few milliseconds a dedicated engine would buy you.
Next: how pgvector compares to Qdrant, Azure AI Search and SQL Server 2025 if you are still choosing a store — and if your application data already lives in SQL Server, native vector search in SQL Server 2025 may remove the second database entirely.