Semantic Search in PostgreSQL With pgvector and .NET: A Practical Setup
Store embeddings next to relational data, build HNSW or IVFFlat indexes, filter and blend hybrid ranks, and judge when pgvector is enough for your app.
This article is published in English.
Sources: Semantic Search with PostgreSQL: Pragmatism Beats Hype – Most of the Time
Adding semantic search to an app usually brings up the same shortlist: Pinecone, Weaviate, Qdrant, Milvus or some other dedicated vector database. Any of them can be the right answer. Still, a lot of applications already run a PostgreSQL instance, and for many semantic search needs that database can handle the job on its own.
The piece that makes it work is pgvector, an open-source extension that gives Postgres a vector type and similarity search. Embeddings sit beside the relational data and get queried with plain SQL. Beyond having fewer parts to run, the benefit is structural. There is nothing to keep in sync, there is only one place where the truth lives, and you add infrastructure only when the load demands it.
Semantic search in a sentence
Classic search matches words, and semantic search matches meaning. A query like "How do I get over the pass?" can find text about a mountain pass instead of a school corridor, because the query and the documents are both turned into numeric vectors known as embeddings.
The flow has four steps:
- Turn text into an embedding vector.
- Store the vector in PostgreSQL.
- Convert the search query into the same vector space.
- Retrieve the stored vectors nearest to the query vector.
The phrase "same vector space" carries weight. Embeddings from different models can't be freely mixed.
Pick the embedding model first
The model comes before the schema, because it fixes the vector dimension, the per-request cost, the languages handled well and whether you can run it locally. With OpenAI's text-embedding-3-small, a vector(1536) column is the usual match. Another model, including a local one served through Ollama, may produce a different dimension.
That isn't a minor detail. PostgreSQL refuses vectors with the wrong number of dimensions.
Switching models later means a data migration. You would:
- add a new vector column or table;
- re-embed every document;
- rebuild the vector index;
- never compare old and new embeddings directly.
Installing pgvector
Turn the extension on once per database:
CREATE EXTENSION IF NOT EXISTS vector;
On managed PostgreSQL services, confirm that pgvector is offered for your plan and Postgres version. Many do support it, but check before designing around it.
Schema: store chunks
For short records, one vector per row works. For real documents it is usually better to embed chunks, since a long document can cover several topics and one vector for the whole thing tends to blur them together. A workable layout:
CREATE TABLE documents (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
source TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE document_chunks (
id BIGSERIAL PRIMARY KEY,
document_id BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
chunk_index INTEGER NOT NULL,
content TEXT NOT NULL,
embedding vector(1536) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (document_id, chunk_index)
);
Keep vector(1536) only if your model really returns 1536 numbers, and otherwise change it to match.
Using it from .NET with Npgsql
Add the packages:
dotnet add package Npgsql
dotnet add package Pgvector
Dapper users also need one more:
dotnet add package Pgvector.Dapper
With plain Npgsql, call UseVector() on the data source builder:
using Npgsql;
using Pgvector;
var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
dataSourceBuilder.UseVector();
await using var dataSource = dataSourceBuilder.Build();
In production, create the extension and tables through migrations or provisioning. If the extension gets created at runtime, reload the connection's PostgreSQL types before using the new type.
Getting embeddings from OpenAI
The OpenAI .NET SDK exposes an embedding client:
using OpenAI.Embeddings;
var embeddingClient = new EmbeddingClient("text-embedding-3-small", apiKey);
async Task<float[]> GetEmbeddingAsync(string text)
{
var result = await embeddingClient.GenerateEmbeddingAsync(text);
return result.Value.ToFloats().ToArray();
}
For local embeddings, use a local model through whichever provider you prefer. The rule stays the same: the model that indexes the documents and the model that embeds queries must match, or at least be deliberately compatible. Never index with one model and search with another.
Saving a chunk
async Task InsertChunkAsync(
long documentId,
int chunkIndex,
string content,
CancellationToken cancellationToken = default)
{
var embedding = await GetEmbeddingAsync(content);
var vector = new Vector(embedding);
await using var conn = await dataSource.OpenConnectionAsync(cancellationToken);
await using var cmd = new NpgsqlCommand("""
INSERT INTO document_chunks (document_id, chunk_index, content, embedding)
VALUES (@documentId, @chunkIndex, @content, @embedding)
ON CONFLICT (document_id, chunk_index)
DO UPDATE SET
content = EXCLUDED.content,
embedding = EXCLUDED.embedding
""", conn);
cmd.Parameters.AddWithValue("documentId", documentId);
cmd.Parameters.AddWithValue("chunkIndex", chunkIndex);
cmd.Parameters.AddWithValue("content", content);
cmd.Parameters.AddWithValue("embedding", vector);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
The update path is the key detail. If a chunk's text changes, its embedding has to change as well, which is why the statement upserts both together.
Running a search
The <=> operator computes cosine distance, where a smaller number means a closer match. Sorting ascending returns the nearest vectors first.
public sealed record SearchResult(
long DocumentId,
long ChunkId,
string Title,
string Content,
double Distance);
async Task<List<SearchResult>> SearchAsync(
string query,
int limit = 5,
CancellationToken cancellationToken = default)
{
var queryEmbedding = await GetEmbeddingAsync(query);
var queryVector = new Vector(queryEmbedding);
await using var conn = await dataSource.OpenConnectionAsync(cancellationToken);
await using var cmd = new NpgsqlCommand("""
SELECT d.id,
c.id,
d.title,
c.content,
c.embedding <=> @queryVector AS distance
FROM document_chunks c
JOIN documents d ON d.id = c.document_id
ORDER BY c.embedding <=> @queryVector
LIMIT @limit
""", conn);
cmd.Parameters.AddWithValue("queryVector", queryVector);
cmd.Parameters.AddWithValue("limit", limit);
var results = new List<SearchResult>();
await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
results.Add(new SearchResult(
DocumentId: reader.GetInt64(0),
ChunkId: reader.GetInt64(1),
Title: reader.GetString(2),
Content: reader.GetString(3),
Distance: reader.GetDouble(4)));
}
return results;
}
Note that the query sorts directly by the distance expression. With pgvector indexes, the shape ORDER BY embedding <=> @query LIMIT n is what lets the planner use the index.
Distance operators
pgvector offers several operators:
| Operator | Measures |
|---|---|
<-> |
Euclidean (L2) distance |
<=> |
Cosine distance |
<#> |
Negative inner product |
<+> |
Taxicab (L1) distance |
For text embeddings, cosine distance is a sound default.
HNSW indexes
With no index, PostgreSQL scans the whole table for every vector search. That's exact and simple, but it slows down as the vector count rises. HNSW is often the best first index for application search:
CREATE INDEX document_chunks_embedding_hnsw_idx
ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
The two parameters are:
m, the maximum number of connections per layer, which defaults to 16;ef_construction, the candidate list size while building, which defaults to 64.
Larger values can raise recall but cost more memory and build time.
Recall can also be tuned at query time for a session:
SET hnsw.ef_search = 100;
or only inside a single transaction:
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT ...
COMMIT;
Remember that HNSW is approximate. It gives up perfect recall in exchange for speed.
IVFFlat as the alternative
IVFFlat can use less memory and build faster than HNSW, though its trade-off between speed and recall is normally worse.
CREATE INDEX document_chunks_embedding_ivfflat_idx
ON document_chunks
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
Don't build an IVFFlat index on an empty table, because it needs existing rows to form its lists. As a rough start for lists, a table of 10,000 vectors suits about 10 lists, not 100.
At query time, adjust the probes:
SET ivfflat.probes = 10;
More probes improve recall and slow queries down.
Filtering and hybrid search
A major reason to choose pgvector is that vectors live next to relational data. You can filter first and then rank:
SELECT d.id, d.title, c.content, c.embedding <=> @queryVector AS distance
FROM document_chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.created_at > NOW() - INTERVAL '30 days'
AND d.source = 'documentation'
ORDER BY c.embedding <=> @queryVector
LIMIT 10;
For genuine hybrid search, don't compute a keyword rank and then ignore it. Merge the semantic and keyword ranks. One practical approach is scoring in the style of Reciprocal Rank Fusion:
WITH semantic AS (
SELECT c.id,
row_number() OVER (ORDER BY c.embedding <=> @queryVector) AS semantic_rank
FROM document_chunks c
LIMIT 100
),
keyword AS (
SELECT c.id,
row_number() OVER (
ORDER BY ts_rank_cd(
to_tsvector('english', c.content),
plainto_tsquery('english', @query)
) DESC
) AS keyword_rank
FROM document_chunks c
WHERE to_tsvector('english', c.content) @@ plainto_tsquery('english', @query)
LIMIT 100
)
SELECT d.id AS document_id,
c.id AS chunk_id,
d.title,
c.content,
COALESCE(1.0 / (60 + semantic.semantic_rank), 0) +
COALESCE(1.0 / (60 + keyword.keyword_rank), 0) AS score
FROM semantic
FULL OUTER JOIN keyword ON keyword.id = semantic.id
JOIN document_chunks c ON c.id = COALESCE(semantic.id, keyword.id)
JOIN documents d ON d.id = c.document_id
ORDER BY score DESC
LIMIT 10;
It isn't the only way to blend the two, but it makes the ranking logic visible.
When pgvector fits
It's a strong choice when:
- PostgreSQL is already the main database;
- vectors need to join naturally with relational tables;
- transactional consistency matters;
- the team already knows how to run Postgres;
- the dataset is small or medium sized;
- you'd like to avoid another service and sync job.
Typical homes for it are internal search, product search, support tooling and documentation search, along with RAG setups whose facts already come from the relational database.
When a dedicated vector store may be better
Look at a purpose-built vector database when:
- the vector count reaches tens of millions or beyond;
- query volume is very high and latency budgets are tight;
- you want a managed service with specialised vector-search operations;
- you need richer built-in hybrid retrieval, reranking or filtering than Postgres gives you;
- your team doesn't run PostgreSQL in depth;
- multi-tenant isolation and scaling are central needs.
This isn't about ideology. It's about operational fit.
A complete ASP.NET Core endpoint
app.MapGet("/search", async (
string q,
NpgsqlDataSource db,
CancellationToken cancellationToken) =>
{
var queryEmbedding = await GetEmbeddingAsync(q);
var queryVector = new Vector(queryEmbedding);
await using var conn = await db.OpenConnectionAsync(cancellationToken);
await using var cmd = new NpgsqlCommand("""
SELECT d.id,
c.id,
d.title,
c.content,
c.embedding <=> @v AS distance
FROM document_chunks c
JOIN documents d ON d.id = c.document_id
ORDER BY c.embedding <=> @v
LIMIT 5
""", conn);
cmd.Parameters.AddWithValue("v", queryVector);
var results = new List<object>();
await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
results.Add(new
{
documentId = reader.GetInt64(0),
chunkId = reader.GetInt64(1),
title = reader.GetString(2),
content = reader.GetString(3),
distance = reader.GetDouble(4)
});
}
return Results.Ok(results);
});
Where this leaves you
Semantic search doesn't always call for a separate vector database. When PostgreSQL already holds your source of truth, pgvector lets you keep embeddings beside relational data, query them in SQL, filter with ordinary conditions and keep transactions in one system.
A few rules keep the setup clean:
- choose the embedding model before the vector dimension;
- never mix embeddings from different models;
- chunk documents that are long or cover several topics;
- add an index only once the table is big enough to need one;
- remember that HNSW and IVFFlat are approximate;
- treat a model change as a data migration.
For many production applications, pgvector is enough for a long time. That doesn't mean dedicated vector databases are unnecessary. It's just that the simplest reliable architecture often keeps the data where it already lives.
For further reading, see the pgvector README and its .NET package, the OpenAI .NET SDK and embeddings guide, and the Microsoft.Extensions.AI documentation.