Choosing a vector database: pgvector or a dedicated engine

The first architectural decision in most RAG projects is which vector database to use, and it is usually made backwards — engine first, requirements second. The honest answer for a large share of projects is that Postgres is already sufficient.

Start with pgvector

If you are already running Postgres, pgvector is an extension away. That single fact removes an enormous amount of operational work: one database to back up, one to monitor, one connection pool, one set of credentials.

The bigger advantage is transactional consistency. Your embeddings live in the same database as the rows they describe, so a document and its vectors are inserted, updated and deleted in one transaction. With a separate vector store you own that synchronisation problem forever — and orphaned vectors returning results for deleted documents is a genuinely unpleasant bug.

CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE chunks ADD COLUMN embedding vector(1536);

CREATE INDEX ON chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Metadata filter and vector search in one query
SELECT id, content
FROM chunks
WHERE tenant_id = $1 AND published
ORDER BY embedding <=> $2
LIMIT 20;

That last query is the point. Filtering by tenant and status alongside the vector search, in one statement, with the planner handling it — no application-side joining of two systems.

When to move to a dedicated engine

There is a real boundary, and it is mostly about scale and specialised features:

• Tens of millions of vectors. pgvector is comfortable into the low millions. Beyond that, purpose-built engines pull ahead on index build time and memory efficiency.
• High write throughput. Continuous streaming ingest with live index updates is a workload dedicated engines are designed for.
• Advanced quantization. Product and binary quantization can cut memory dramatically at large scale.
• Independent scaling. Search load that is unrelated to your transactional load may be better off on its own hardware.

Index parameters matter more than the engine

Teams switch engines chasing performance when the real problem is an untuned index. HNSW has three knobs worth understanding:

• m — connections per node. Higher means better recall and more memory. 16 is a sane default.
• ef_construction — effort at build time. Higher means a better graph and slower indexing. Set it once, live with it.
• ef_search — effort at query time. The one you actually tune. Raise it until recall is acceptable, then stop.

ef_search is adjustable per query, which is more useful than it sounds — you can spend more effort on a user's explicit search than on a background enrichment job.

Dimensions are a cost decision

A 3,072-dimension embedding is not automatically better than a 768-dimension one for your task, but it is four times the storage and slower to compare. Some modern models support Matryoshka truncation — you can cut the vector down and keep most of the quality.

Test it. Measure recall@10 at full dimensionality and truncated. If the difference is within noise on your golden set, take the smaller vectors and the cheaper index.

A reasonable default

1. 1Start on pgvector with HNSW if you already run Postgres.
2. 2Build a golden set and measure recall@k before optimising anything.
3. 3Tune ef_search, chunking and the embedding model first.
4. 4Move to a dedicated engine when you have a measured reason, not an anticipated one.