pgvector in Production Doesn't Fail on Accuracy. It Fails on Three Specific Numbers.

The demo lies: pgvector looks fine up to a point

A pgvector deployment in a demo is not the same animal as pgvector in production. Add the extension, create an HNSW index, embed a few thousand documents, and cosine similarity search returns clean results in single-digit milliseconds. Most teams ship exactly this setup, watch it hold up in staging against 50,000 or 500,000 rows, and assume it will keep scaling the same way.

It doesn't, and not because the search algorithm gets less accurate as the table grows. Three specific numbers quietly change the shape of the problem: a memory setting almost nobody sets on purpose, the size of the resulting graph relative to available RAM, and a row count past which query latency stops being predictable. None of these show up in a demo. They show up months after launch, when an index build that took ninety seconds in testing now takes four hours, or a query that returned in 8 milliseconds in the spring is taking 300 in the autumn.

This piece names those numbers precisely enough to check them before they become an incident, plus the two failure modes that have nothing to do with scale at all.

Why pgvector, and not a dedicated vector database, in the first place

Most teams that reach for pgvector aren't choosing it over Pinecone or Qdrant on search quality. They're choosing it because a retrieval-augmented generation feature usually needs vector search sitting next to relational data that's already in Postgres: user accounts, permissions, document metadata, tenant boundaries. Running a second database for embeddings means keeping two systems consistent, handling a second set of credentials and backups, and joining across a network boundary every time a result needs to be filtered by something that lives in the relational schema. pgvector avoids all of that. A vector column sits in the same table as everything else, in the same transaction, behind the same access controls.

That trade-off holds up well at small and medium scale, which is exactly why it's easy to miss the point where it stops holding up. A support-ticket search feature with 200,000 embedded tickets and a single Postgres primary behaves nothing like the same feature at 8 million tickets across a fleet of read replicas. The architecture hasn't changed. The three thresholds below have just been crossed one at a time, usually without anyone noticing until a dashboard does.

The first number: 64 MB

PostgreSQL's maintenance_work_mem defaults to 64 MB. Almost nobody sets it deliberately for vector workloads, because nothing in a typical Postgres deployment ever needed more than that to build a B-tree index. HNSW is different. Building the graph is a memory-bound operation, and when the working set doesn't fit inside maintenance_work_mem, Postgres falls back to a disk-based build. Discussions among pgvector's own maintainers on the project's GitHub issue tracker put that fallback at roughly 10 to 50 times slower than an in-memory build, depending on disk speed and graph size.

For anything past a few hundred thousand rows, 64 MB isn't close to enough. Teams building HNSW indexes over 5 million vectors at 1536 dimensions, the size OpenAI's common embedding models produce, typically need 8 to 16 GB of maintenance_work_mem to keep the build in memory. The number worth remembering: if an HNSW build is taking hours instead of minutes, check this setting before touching anything else.

The second number: the RAM your graph actually needs

Once the build itself is fast, the next threshold is whether the resulting graph fits in memory during queries. A workable estimate for HNSW memory use is rows × dimensions × 4 bytes × 2, where the factor of two covers the graph's edge overhead on top of the raw vector data. At 1536 dimensions, that works out to roughly 12 KB per vector before adding any metadata columns. A million rows costs about 12 GB. Five million costs around 60 GB.

Once the index no longer fits in shared_buffers or the operating system's page cache, every query starts paying random-access disk reads instead of memory reads, and p95 latency climbs from single-digit milliseconds into the hundreds. This is usually the first threshold teams hit, because nothing has to change in the application to trigger it. It happens automatically as the table grows. A useful early check: pull pg_relation_size() for the HNSW index and compare it against the instance's available RAM, not its total RAM. Once the index alone crosses roughly 60 to 70 percent of available memory, plan for the next section before getting paged about it.

Concretely: a support-ticket search feature at 3 million embedded tickets, 1536 dimensions each, puts roughly 36 GB of graph on disk before overhead. On an instance with 32 GB of total RAM, most of that never stays resident, and every query pays for it. The same feature on an instance with 64 GB of RAM, where the graph comfortably fits alongside the rest of the working set, keeps its single-digit millisecond latency. Nothing about the query changed between those two cases. Only the ratio of index size to available memory did.

The third number: somewhere past 10 million rows, latency stops being predictable

pgvector doesn't fall off a cliff at any single row count on its own. The first two thresholds matter more than raw row count by itself. But in practice, teams running HNSW past roughly 10 million vectors with sub-50ms p95 requirements consistently report needing to leave single-node Postgres behind. That isn't a hard architectural wall. It's the point where RAM cost, build time, and query latency stop being minor tuning problems and start being infrastructure decisions.

pgvectorscale, Timescale's extension built on top of pgvector, addresses this directly with DiskANN, a graph structure designed to stream from NVMe storage rather than hold the entire index in RAM. Teams who move to it report the practical ceiling extending well past 100 million vectors, trading some of HNSW's pure in-memory speed for the ability to run vector search at a size where keeping a full graph in RAM stops being financially sensible.

The cost comparison is what usually forces the decision, not the latency numbers on their own. Keeping a 10-million-row, 1536-dimension HNSW graph resident in RAM means provisioning an instance with well over 100 GB of memory, most of it dedicated to a single index. A DiskANN-backed index on the same dataset can run on an instance a fraction of that size, because it only pulls the parts of the graph a given query needs from fast local storage. The trade is a few extra milliseconds of latency per query in exchange for not paying for RAM the workload only needs during the busiest fraction of its traffic.

A common first reaction to climbing query latency is to add read replicas. It helps with query throughput, but it does nothing for the underlying problem: every replica needs its own copy of the same oversized HNSW graph in its own memory, at the same cost per instance. Three replicas that each fail to fit the index in RAM produce three copies of the same random-access disk reads, not a fix for any of them. Replicas address concurrency. They don't address an index that's too large for the memory it's running on.

Two knobs to try before provisioning anything new

HNSW exposes two tuning parameters that most default configurations leave untouched. ef_construction controls how many candidate neighbours the index considers while building each node's connections. A higher value produces a more accurate graph at the cost of a slower build. ef_search does the equivalent job at query time: raising it trades a few extra milliseconds of latency for meaningfully better recall, and lowering it does the reverse.

Before assuming a recall or latency problem requires new infrastructure, it's worth checking whether ef_search is still sitting at its default of 40, a value tuned for a much smaller, less crowded graph than the one now running in production. Neither knob rescues a genuinely undersized memory allocation. But both are free to try, reversible in seconds, and often the actual fix when a team's first instinct is to migrate off Postgres entirely.

Quantization: trading precision for headroom on the first two thresholds

Half-precision (halfvec) and binary quantization reduce the HNSW graph's memory footprint directly, which affects both earlier thresholds at once. Smaller vectors mean maintenance_work_mem stretches further during the build, and the resulting graph takes up less RAM at query time. Testing across maintenance_work_mem settings from 1 GB to 30 GB found that unquantized vector index builds saw little further improvement past a moderate memory allocation, while halfvec and bit-quantized builds kept getting meaningfully faster as more memory was made available, because the smaller representation lets more of the graph fit in memory at once.

The trade-off is retrieval accuracy. halfvec roughly halves memory at a small, usually acceptable recall cost for most retrieval-augmented generation use cases. Binary quantization cuts memory further, with a larger accuracy hit that's worth benchmarking against a real query set before committing to it. For a team hitting the RAM threshold before the row-count threshold, quantization is typically the cheapest fix available: no new infrastructure, just an index rebuilt with a different type.

The benchmarking step matters more than it sounds. Recall loss from quantization isn't uniform across a dataset. Documents whose nearest neighbours are close together in the embedding space tend to survive quantization with little practical difference, while edge cases sitting near a decision boundary are the ones most likely to get bumped out of a top-10 result. Running quantization against a saved set of real production queries, not a synthetic benchmark, is the only reliable way to know whether the accuracy trade is acceptable for a specific use case.

The filter problem nobody mentions until it is in production

The other failure mode has nothing to do with scale. It's about how HNSW handles WHERE clauses. Add a tenant filter or a date range to a vector similarity query, and pgvector has two options: filter after fetching the k nearest neighbours, which under-returns results whenever some of the top matches don't pass the filter, or fall back to a sequential scan, which discards the index entirely. Neither is what most engineers expect from a database that just spent gigabytes of RAM building an index specifically for this query.

The practical workaround is a pre-filter common table expression: narrow the candidate rows with the WHERE clause first, then run the vector search against that smaller set. It gives up some of HNSW's raw performance advantage, but it returns correct results, which a silently under-filtered top-k query does not.

-- naive query: HNSW returns top-k first, filter applied after.
-- rows that would pass the filter but fell outside top-k are dropped.
SELECT id, content
FROM documents
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 10;

-- pre-filter CTE: narrows candidates before the vector search runs.
WITH candidates AS (
  SELECT id, content, embedding
  FROM documents
  WHERE tenant_id = $1
)
SELECT id, content
FROM candidates
ORDER BY embedding <=> $2
LIMIT 10;

IVFFlat's separate problem: centroids go stale

Teams who chose IVFFlat over HNSW for its lower build-time memory footprint hit a different threshold entirely. IVFFlat clusters vectors around a fixed number of centroids, chosen at index-creation time based on the table's size at that moment. Bulk-insert a few million new rows afterward, and the centroids no longer reflect the data's actual distribution. Recall degrades quietly, because new vectors are being matched against clusters computed for a smaller, differently shaped dataset. There's no error message. Search results simply get worse over time.

The fix is mechanical rather than architectural: rebuild the index with REINDEX, or CREATE INDEX CONCURRENTLY and swap, after any bulk load large enough to change the table's row count meaningfully. Size the lists parameter for the dataset expected six months out, not the one on launch day. A common rule of thumb is lists set to roughly the square root of the expected row count, though it's worth verifying against recall on a real query sample rather than trusting the formula blindly.

This is also why teams running frequent bulk imports, nightly syncs from a data warehouse, migrations from an old search system, tend to drift toward HNSW even though it costs more memory to build. HNSW's graph updates incrementally as rows are inserted, so there's no equivalent stale-centroid clock ticking in the background. IVFFlat can still be the right choice for a dataset that's loaded once and rarely touched again. It's a poor fit for one that keeps growing in large, irregular batches.

A decision framework, and what to check before you ship pgvector in production

None of the first four fixes require leaving Postgres. What they require is checking the right number before assuming pgvector itself is the problem. The most common thread in write-ups of failed pgvector deployments isn't a fundamental limitation of the extension. It's a maintenance_work_mem left at its default, discovered eight months after launch.

Before shipping a pgvector-backed retrieval feature at meaningful scale, it's worth writing down four numbers alongside the expected row count:

• The maintenance_work_mem to set for index builds, explicitly, in the build session.
• The RAM estimate for the embedding dimensionality at the projected row count, checked against available memory, not total memory.
• Whether any query filters on a column outside the vector search, and the pre-filter strategy for it.
• How often bulk loads happen relative to the reindex schedule, for teams using IVFFlat.

pgvector remains a reasonable default for most teams starting a retrieval feature. It avoids standing up a second database, keeps vector data transactionally consistent with the rest of the schema, and its failure modes, once known, sit in the same predictable category as any other Postgres capacity-planning problem. None of the four thresholds above are exotic; every one of them is a number a database engineer would recognise from tuning a large B-tree index or a big JSONB column, just applied to a newer data type.

The teams who get surprised by pgvector are usually the ones who treated vector search inside Postgres as a feature they switched on, rather than a subsystem with its own memory budget. The fix, in almost every case documented above, was cheaper than the migration everyone assumed they needed.