PostgreSQL with pgvector or a dedicated vector database: how to choose

There is no shortage of vector database benchmarks, and most of them are useless to you. They measure a synthetic dataset at a dimension you are not using, at a recall target you did not choose, on hardware you will not rent — and the ranking changes when any of those move.

So this is not another benchmark. It is the set of questions that actually decides the answer, and the test to run on your data, which is the only benchmark that transfers.

The question is rarely "which is faster"

At the scale most products operate at — under a few million vectors — both options are fast enough, and the decision is made on operational cost, consistency and query power instead.

The consistency argument is the one people underrate

If your embeddings live in a different system from your rows, you now own a synchronisation problem: a document is updated, one system has the new version, the other does not, and there is a window where search returns text that no longer exists.

With pgvector the embedding is a column on the row. It is updated in the same transaction as the content it describes, and it is deleted by the same DELETE. That is not a performance property; it is a whole class of bug that cannot occur.

-- The embedding cannot drift from the text, because it is the same write.
UPDATE documents
SET body = $1,
    embedding = $2,
    tsv = to_tsvector('english', $1)
WHERE id = $3;

The filtering argument is the one that decides most real cases

Production queries are almost never "nearest neighbours in the whole corpus". They are "nearest neighbours within this workspace, published, in these two categories, that this user may see".

In Postgres those are joins and WHERE clauses you already know how to write, against constraints the database already enforces. In a dedicated store they are metadata filters, and the expressiveness varies considerably by product — which is worth checking against your actual authorisation model before choosing, not after.

Choosing an index: the trade is explicit

Within pgvector the index choice is its own decision, and it is unusually legible.

Both expose recall as a runtime setting, which is the important part: you can trade accuracy for latency per query rather than per index.

CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
 
-- Raise for accuracy, lower for speed. Measure both on your data.
SET hnsw.ef_search = 100;

Dimension is a cost multiplier you control

Embedding dimension drives storage, memory and query cost roughly linearly, and many teams pay for dimensions that buy them nothing.

1. Test a smaller model before assuming you need a larger one

   On domain-specific corpora the accuracy difference between a 1536-dimension and a 768-dimension model is frequently small — and the smaller one halves your index memory.

2. If your model supports Matryoshka truncation, measure the truncated version

   Some embeddings are trained so that a prefix of the vector is still meaningful. Truncating to a third of the dimensions can cost very little accuracy.

3. Quantise before you shard

   Halving precision is a much smaller operational change than adding a system.

The test that actually transfers

Run this on your data. It takes an afternoon and it beats every published benchmark for your purposes.

1. Take a real sample — 100k to 1M of your own chunks

   Not a public dataset. Your dimension, your text distribution, your metadata.

2. Build a ground truth with exact search

   Brute-force nearest neighbours for 200 real queries. Slow, and it only has to run once.

3. Measure recall@k against that ground truth, per configuration

   Sweep ef_search or probes. Plot recall against p95 latency — this curve is the actual answer, and it is specific to you.

4. Measure with your filters applied

   This is the step everyone skips and the one that most often changes the ranking. Filtered approximate search behaves very differently from unfiltered.

5. Measure ingest, not just query

   How long to embed and index a full rebuild? How long to update one document? A system that queries beautifully and takes six hours to rebuild has a deployment problem.

A decision procedure

1. Already on Postgres, under a few million vectors, filtered queries? Use pgvector.

   The consistency and SQL-filtering advantages are concrete, and the operational cost is zero. This covers most products.

2. Hundreds of millions of vectors, or vector search IS the product? Dedicated.

   At that scale purpose-built indexing, sharding and memory management stop being a luxury.

3. Somewhere in between? Start with pgvector and measure the curve.

   Migrating later is a well-understood re-index, not a rewrite. Adding a second datastore on day one to avoid a possible migration is paying now to maybe save later.

4. Whichever you choose, add lexical search alongside it.

   Dense-only retrieval fails on exact strings — identifiers, error codes, names — and it fails quietly, returning plausible neighbours instead of the match.

The honest summary: for the large majority of applications, the vector store is not the bottleneck and not the interesting decision. Chunking, hybrid retrieval and reranking move answer quality far more than picking a different nearest-neighbour implementation — and they are cheaper to change.