pgvector or a Dedicated Vector Database? It Is a Memory Arithmetic Problem

Every comparison of pgvector against a dedicated vector database is organised as a feature table, and almost nobody chooses on features. The decision is made on one question with an arithmetic answer: does the index fit in memory you are prepared to pay for?

That question is answerable in twenty minutes with numbers you already have, and it is more decisive than any of the columns in the comparison tables. This article is about doing the arithmetic, and about the two constraints that surprise people after they have committed.

The calculation that decides it

An HNSW index stores the vectors plus a navigable graph over them. The vector storage is exact and easy: four bytes per dimension per vector for single-precision floats. The graph adds overhead on top, driven by the connections-per-layer parameter.

vector bytes   = N x D x 4
graph overhead = roughly the same order again for default m

  N = number of vectors
  D = dimensions
  4 = bytes per float32

Worked, at 1,536 dimensions (text-embedding-3-small):

     100,000 vectors  ->  0.6 GB vectors  ->  ~1.2 GB with graph
   1,000,000 vectors  ->  6.1 GB vectors  ->  ~12  GB with graph
  10,000,000 vectors  ->   61 GB vectors  ->  ~120 GB with graph

At 3,072 dimensions every figure doubles.
At   384 dimensions every figure drops fourfold.

Now compare that against the RAM on the instance you are willing to run, remembering that Postgres also needs memory for everything else it does. If the index comfortably fits with room to spare, pgvector is the straightforward answer and adding a second datastore buys you operational cost rather than capability. If it does not fit, you are choosing between a much larger instance, a quantised or reduced-dimension representation, or an engine designed to serve indexes from disk.

This is why the same advice appears with wildly different vector-count thresholds across articles. Each author had a dimension count and an instance size in mind and did not state either. The arithmetic is the same; only the inputs differ, and the inputs are yours.

Reducing dimensions is the cheapest lever

Before accepting that an index will not fit, note that dimensionality is negotiable in a way most teams do not realise. OpenAI's embedding endpoints accept a dimensions parameter that shortens the output, and shorter vectors cost proportionally less memory. Halving dimensions halves the vector storage.

It costs some retrieval quality, and how much depends on your corpus. The point is that it is a dial rather than a fixed property, and it is worth measuring against your own labelled evaluation set before spending on hardware to store dimensions you may not need.

The 2,000-dimension ceiling nobody mentions

This one catches teams after they have built. The pgvector documentation states that the standard vector type supports up to 2,000 dimensions for HNSW and IVFFlat indexes. Storage of larger vectors is fine; indexing them with those methods is not.

text-embedding-3-large emits 3,072 dimensions. Its full-size output therefore cannot be indexed as a standard vector column, which means an unindexed sequential scan over every row — fine on ten thousand documents, unusable on a million.

The three parameters that actually matter

pgvector's HNSW implementation exposes a small surface, and the defaults are documented: m is 16, ef_construction is 64, and ef_search is 40. Two of those are set once at build time; the third is the one you will actually tune.

ef_search controls how much of the graph a query explores. Raising it improves recall and costs latency; lowering it does the reverse. The useful property is that it can be set inside a transaction, so it does not have to be a single global compromise across every query your application makes.

-- Recall is a per-query decision, not a server-wide setting.

BEGIN;
  -- A user-facing autocomplete: latency matters more than perfect recall.
  SET LOCAL hnsw.ef_search = 20;
  SELECT id FROM chunks ORDER BY embedding <=> $1 LIMIT 5;
COMMIT;

BEGIN;
  -- A grounded answer where a missed document is a wrong answer.
  SET LOCAL hnsw.ef_search = 200;
  SELECT id FROM chunks ORDER BY embedding <=> $1 LIMIT 20;
COMMIT;

That distinction is worth building in early. A single global ef_search forces the strictest endpoint's recall requirement onto the most latency-sensitive one, and the usual outcome is that both are wrong.

Index builds and the memory cliff

The documentation notes that indexes build significantly faster when the graph fits into maintenance_work_mem. What that phrasing understates is the shape of the failure: this is not a gentle gradient but a cliff. When the graph does not fit, the build falls back to a slower disk-based path, and the difference is large enough that people conclude pgvector cannot handle their data and migrate.

Raise maintenance_work_mem for the build session specifically rather than globally, since it is allocated per maintenance operation and a high global value is a memory hazard under concurrent autovacuum. Build the index after loading data rather than before, and raise the parallel worker count, which defaults to two. Crunchy Data's walkthrough of HNSW index behaviour in Postgres is a useful companion on how the build actually proceeds.

Where the architectures genuinely differ

Almost every real query is filtered. You want the nearest neighbours among documents this tenant may read, from this date range, of this type. This is the point where a general-purpose database and a purpose-built engine stop being interchangeable.

The problem is that a filter and a similarity search pull against each other. Search the graph first and the filter may eliminate most of what you found, leaving too few results. Filter first and you may have discarded the structure the index needs to navigate. pgvector 0.8 added iterative index scans, which handle this by continuing to scan further into the index until enough results survive the filter — a pragmatic fix that covers the common cases well.

Where dedicated engines retain an advantage is the combination of a highly selective filter and a very large collection, because filter-aware traversal is designed into the index rather than layered over it. That is a real distinction and it is also a narrower one than the marketing suggests: most applications are not running highly selective filters over hundreds of millions of vectors, and the ones that are usually know it.

Common mistakes

1. Choosing on a vector-count threshold from an article. It encodes an unstated dimension count and instance size. Compute your own index size instead; it takes minutes.
2. Generating 3,072-dimension embeddings and discovering the indexing limit at scale. Check the dimension count against the storage type before backfilling.
3. Leaving maintenance_work_mem at its default for a large build, then concluding pgvector is slow. The disk fallback is a cliff, not a gradient.
4. Setting one global ef_search. Recall requirements differ per endpoint, and the parameter is settable per transaction.
5. Building the index before loading data. Building after is faster, and the documentation says so.
6. Adding a dedicated vector store while keeping the source records in Postgres. You have taken on a synchronisation problem to solve a performance problem you may not have had.
7. Assuming a dedicated engine removes tuning. It relocates it — different parameters, same need to measure recall against labelled cases.

Security and performance considerations

Keeping vectors in Postgres means row-level security applies to them, which is a substantial advantage when documents have per-tenant or per-role visibility. The filter is enforced by the database rather than by application code remembering to add it, and enforcement in the query is the only place it is reliable — filtering after retrieval means restricted content has already reached a context window.

Retrieved chunks are also untrusted content entering the model's context, whatever stores them. That is the same structural weakness as tool descriptions carrying instructions, and it does not change with the database — a point worth reading alongside the recall and precision failures that determine what ends up in that context in the first place.

On performance, the operational cost people underestimate is vacuum. Vector rows are large, updates create dead tuples, and dead tuples in an HNSW index degrade both recall and speed. A corpus that is re-embedded periodically needs autovacuum tuned for that write pattern, or performance decays over months in a way that looks like the index having been fine at launch and mysteriously worse later.

Troubleshooting

1. Queries fast in testing, slow in production — the index no longer fits in RAM. Check index size against available memory before anything else.
2. Index build taking hours — maintenance_work_mem too low, so the build fell back to disk. Raise it for the session and rebuild.
3. Recall worse than expected — ef_search too low for this endpoint. Raise it in the transaction and measure against labelled cases rather than guessing.
4. Filtered queries returning too few results — pre-0.8 behaviour, or iterative scans not engaged. Confirm the version.
5. Sequential scan on a table that has an index — often the dimension limit. A column too wide for HNSW has no usable index regardless of what was created.
6. Gradual degradation over months with no change — dead tuples from re-embedding. Check autovacuum against the write pattern.

The honest summary is that pgvector covers considerably more ground than the comparison tables imply, and that the cases where a dedicated engine wins are real but narrower and more identifiable than they are usually presented. Do the arithmetic first. If the index fits with headroom, the operational simplicity of one datastore is worth a great deal, and it is a decision you can revisit with measurements rather than predictions.

If you are designing retrieval infrastructure and want the sizing settled before it becomes a migration, that is the sort of work our AI engineering team does. Where the database and deployment side is the harder half, our cloud and DevOps practice covers the infrastructure underneath it.