pgvector HNSW Tuning for 10M+ Rows Without Ripping It Out

pgvector does not fall off a cliff at a specific row count. It falls off when the active HNSW working set stops fitting in RAM, which for 1536-dimension vectors typically starts biting between 1 and 10 million rows. The row count is a proxy; memory is the variable.

ClickHouse's engineering team lays out the thresholds cleanly: "tens of gigabytes" at 1 million vectors, "memory pressure becomes a central design concern" at 10 million, and raw FP32 storage alone running "over 600 gigabytes before index overhead" at 100 million. The mechanism is specific: HNSW answers a query by walking a graph, and each hop reads a page. When those pages are resident in RAM the walk finishes in milliseconds; when the graph exceeds RAM, hops fault to disk and, in ClickHouse's words, query latency degrades "from milliseconds to seconds."

This is a tested regime, not a theoretical one. Issue #822 alone is a real deployment running HNSW over tens of millions of rows on production Postgres, and 1536-dimension indexes at 10-million scale are ordinary in retrieval systems. So the first job of pgvector HNSW tuning is not to pick clever parameters. It is to answer one question honestly: does the index fit in memory on the instance you are willing to pay for? Everything else follows from that. If your vector search is already returning empty or short result sets under load, our troubleshooting guide on why vector search returns nothing covers the query-side failure modes that compound the memory problem.

Size the index before you provision the instance, using a simple per-vector figure: about 6 kilobytes raw for a 1536-dimension FP32 vector, and roughly 20 to 25 kilobytes once the HNSW graph metadata is added. Multiply by your row count and you have your memory target.

Do the arithmetic by hand: a 1536-dimension vector at 4 bytes per float is 1536 x 4 = 6,144 bytes, the "roughly 6 kilobytes per vector" raw payload. The HNSW graph then stores neighbor links for every node across multiple layers. According to ClickHouse's engineering team (2026), with common settings the real per-vector footprint lands at 20 to 25 kilobytes once graph metadata is counted. Here is what that produces at scale:

Two settings decide whether that footprint stays in memory. shared_buffers is the chunk of RAM PostgreSQL reserves for caching data and index pages, and it is the memory Postgres controls directly. The OS page cache holds the rest. What actually matters for HNSW latency is that the pages your queries traverse are resident somewhere in RAM, whether in shared_buffers or the OS cache, rather than sitting on disk. You do not need shared_buffers to equal the full index, but total available RAM must comfortably exceed the hot part of the graph, or you are back to the latency cliff. For teams weighing whether that RAM bill is worth it against a managed service, our breakdown of the cost-per-QPS math for billion-vector search puts the self-hosted memory cost next to hosted alternatives.

HNSW builds crawl for a concrete reason: maintenance_work_mem defaults to 64 megabytes, and once the graph under construction exceeds that budget, pgvector abandons the fast in-memory build and switches to a much slower on-disk path. maintenance_work_mem is the PostgreSQL setting that caps how much memory a single maintenance operation, such as building an index, may use. According to the PostgreSQL documentation (2025), its default is 64 megabytes, unchanged across versions 16, 17, and 18, with Postgres 18 reaching general availability in September 2025.

Sixty-four megabytes is nowhere near enough for a real vector index. When the graph outgrows it, pgvector emits the exact warning seen in issue #822, that the "hnsw graph no longer fits into maintenance_work_mem after N tuples" and that "Building will take significantly more time," then continues on the slower on-disk build. In that issue the warning fired after only 570,905 tuples, and the build later stalled for hours. Raising the limit helps, and ClickHouse recommends "values in the 8 GB to 16 GB range may be appropriate on sufficiently large instances" as a starting point.

Be honest about what 8 to 16 gigabytes buys you, though. That is a starting recommendation for the setting, not the memory a large build actually consumes. A rough community estimate for holding a full HNSW graph in memory during the build is N x D x 4 x 2 bytes, which for a 5-million-row, 1536-dimension table is roughly 60 gigabytes. In other words, 8 to 16 gigabytes will not hold a multi-million-row graph, so large builds still spill to the slower on-disk path regardless. The realistic plan is to raise it as high as the instance allows, add parallel workers, and budget real wall-clock time for the build.

-- Set generously for the session running the build, not globally.
SET maintenance_work_mem = '16GB';
SET max_parallel_maintenance_workers = 7;

Always build large HNSW indexes with CREATE INDEX CONCURRENTLY so the build does not lock writes on the table for hours. CREATE INDEX CONCURRENTLY is the standard PostgreSQL option that builds an index without taking an ACCESS EXCLUSIVE lock, which means inserts and updates keep flowing to the table while the index builds.

This matters because, as the previous section established, a multi-million-row HNSW build is not a quick operation. In issue #822 the total build ran roughly 19 hours before the team gave up, and any plain CREATE INDEX would have blocked writes for that entire window, which on a live application is an outage. The concurrent build trades a longer total build time and a small risk of leaving an invalid index behind (if it fails, you drop and retry) for the ability to keep serving traffic throughout. On a production table at this scale, that trade is not optional.

-- Non-blocking build: writes continue during the (long) build.
CREATE INDEX CONCURRENTLY ON items
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 200);

Where possible, build the index on a replica or a freshly loaded table first, verify recall and latency there, and only then promote it, so the multi-hour build never happens on the primary under user load. If you must build on the primary, do it in a low-traffic window and monitor pg_stat_progress_create_index to catch a stall like #822's before it burns your maintenance window.

Three parameters drive HNSW behavior, and only three are worth your attention: m, ef_construction, and ef_search. Across current pgvector releases, their defaults remain m=16, ef_construction=64, and ef_search=40. Keep m at 16, raise ef_construction for build-time recall, and tune ef_search per query.

Each one controls a different tradeoff. m is the number of neighbor links each node keeps in the graph, and 16 is a well-tested default that rarely needs changing. ef_construction is the size of the candidate list explored while building the graph: higher values produce a better-connected graph and higher recall, at the cost of a slower, more memory-hungry build. The pgvector default is 64, but a higher-recall production setting is commonly 128 to 200, and issue #822 itself used 200. ef_search is the size of the candidate list explored at query time and is the single knob you can change without rebuilding the index. Raising it improves recall and increases latency; lowering it does the reverse. Recall, here, is the fraction of the true nearest neighbors an approximate search actually returns.

The practical workflow is to fix m at 16, choose ef_construction based on how much build time you can tolerate for the recall you need (200 if recall matters and you can eat the build cost, 64 to 128 if builds are already painful), and then treat ef_search as a live dial. Set it per session or per query, measure recall against a ground-truth set, and find the lowest value that meets your recall target so you are not paying latency for recall you do not use.

-- Query-time recall/latency dial: raise for recall, lower for speed.
SET hnsw.ef_search = 100;
SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;

If pgvector returns too few rows when you add a WHERE clause, enable iterative index scans by setting hnsw.iterative_scan to relaxed_order or strict_order. This is the fix for over-filtering, and it shipped in pgvector 0.8.0 in October 2024.

Iterative index scans are pgvector's mechanism for re-entering the index to fetch more candidates when a metadata filter discards most of the first batch. The problem they solve is subtle and common. HNSW fetches a fixed candidate list sized by ef_search, and the filter is applied afterward. The arithmetic is unforgiving: with the default ef_search of 40 and a filter matching about 10 percent of rows, only about 4 of those 40 candidates survive the post-filter, so a query asking for the top 10 comes back short. Before iterative scans, the only workaround was to inflate ef_search globally, which slows every query. With them, pgvector keeps re-scanning the index for more candidates until it has enough results that pass the filter, capped by hnsw.max_scan_tuples (documented default 20,000) so a highly selective filter cannot scan forever. IVFFlat, the other pgvector index type, has the parallel settings ivfflat.iterative_scan and ivfflat.max_probes.

-- Fix under-filled filtered results without inflating ef_search globally.
SET hnsw.iterative_scan = 'relaxed_order';
SET hnsw.max_scan_tuples = 20000;
SELECT id FROM items
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 10;

Use relaxed_order in most cases: it allows results in slightly non-exact distance order in exchange for materially better recall and latency on filtered queries. Reserve strict_order for the narrow situations where results must come back in exact distance order. This is one of the highest-return settings in the tuning surface: a filtered RAG query that silently returns 4 rows instead of 10 looks like a retrieval-quality problem when it is actually an index-configuration problem.

When your index no longer fits in RAM and no reasonable instance will hold it, switch from HNSW to a disk-resident index: pgvectorscale's StreamingDiskANN. DiskANN is a graph-based approximate-nearest-neighbor algorithm from Microsoft Research designed to keep most of the index on SSD, so it degrades gracefully instead of falling off the latency cliff when memory runs out.

pgvectorscale, from Timescale (now Tiger Data), is a PostgreSQL extension that adds the StreamingDiskANN index plus Statistical Binary Quantization, a compression scheme that shrinks vectors so more of the working set fits in a given amount of memory. Its headline benchmark is strong. According to Tiger Data's pgvectorscale benchmark (2024), PostgreSQL with pgvector and pgvectorscale achieved 28x lower p95 latency, 16x higher query throughput, and 75 percent lower cost, at 99 percent recall, tested on 50 million Cohere embeddings (768-dimension) and run self-hosted on AWS EC2. Read that number with the right frame: it is a vendor benchmark measured against Pinecone's storage-optimized s1 index, not against pgvector's own HNSW, so it tells you pgvectorscale is competitive with a managed vector database, not that it is 28x faster than HNSW. The architectural claim underneath it is the durable one: because DiskANN keeps part of the index on disk by design, it survives the moment the index outgrows RAM, where HNSW starts thrashing.

The decision is binary in practice. As long as the HNSW working set fits in memory, HNSW is the right and simpler tool, and you should stay on it. The moment the index reliably exceeds RAM and buying more memory stops being economical, DiskANN via pgvectorscale is the escape hatch that keeps you inside Postgres instead of migrating to a separate system. If you are already comparing that separate-system route, our Pinecone vs Qdrant comparison, the broader Pinecone vs Weaviate vs Qdrant breakdown, and our field notes on migrating from Pinecone to turbopuffer lay out what you trade when you leave Postgres.

Before you conclude pgvector cannot scale and start a migration, work this checklist in order. In the systems we audit, most "pgvector is too slow" verdicts are reached with three or four of these steps still untouched.

The bottom line: use pgvector HNSW for anything up to roughly 50 to 100 million vectors as long as you can keep the index in memory, and do the five tuning steps above before you draw any conclusion about scale. Reach for pgvectorscale's DiskANN when, and only when, the index genuinely outgrows RAM, because it keeps you in Postgres. Skip a full migration to a dedicated vector database until a disk-first Postgres index has actually failed your requirements, not before. This is precisely the call Particula Tech's RAG infrastructure audits are built to make: we size the index against your data, tune the build and the query path, and give you a defensible answer on whether to stay on Postgres or move, rather than a vendor's benchmark run on someone else's workload.