All You Need to Know about PGVector | Part 2

Photo by Arno Smit on Unsplash

This post isthe Part 2 of the series. In the first part I talked about the foundation of pgvector, you can find it here.

Indexing: Scaling to Millions

To make queries fast at scale, you need an Approximate Nearest Neighbor (ANN) index. ANN approach aims to prioritize speed over precision to find data points close to the query point in high dimensional space. pgvector supports two main types; Hierarchical Navigable Small World(HNSW) and Inverted File Flat(IVFFlat).

Please note that recall is considered as the main performance metric that measures how good an indexing approach is at finding the closest data points in the db. Therefore when examining an approach we’ll use “recall” as our main performance metric.

HNSW (Hierarchical Navigable Small World)

Although HNSW is considered the gold standard for ANN search performance, optimal results depend heavily on data characteristics, index parameters, and workload patterns. It builds a multi-layered graph structure to build indexes. And during search time it starts scanning the top layer that has less data points compared to the deeper layer. Then moves to the next layer until it finds the closest data points to the query point.

For read-heavy workloads with relatively stable data, it consistently delivers sub-millisecond approximate nearest neighbor search at recall levels that would be impossible with a IVFFlat scan. The hierarchical graph structure means search complexity scales logarithmically rather than linearly, so the performance gap over brute force grows as your dataset does. If you know your data distribution upfront and can tune once, it is hard to beat.

• Pros: extremely fast queries; excellent recall; robust against data updates.
• Cons: slower build time; uses more memory.

-- Create an HNSW index using Cosine Distance (<=>)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

Parameters

• m: Max connections per node. Higher m value = better recall but higher memory usage.
• ef_construction: The size of the dynamic candidate list during build. Higher = better index quality but slower build.
• ef_search: The size of the dynamic candidate list during query execution.

But HNSW index comes with real trade-offs.The graph is built at index time and does not adapt to how your data evolves. In high-churn datasets where vectors are frequently updated or deleted, the graph degrades silently. pgvector marks deleted rows as dead tuples, but the graph structure is not rebuilt around them, so you can end up traversing stale paths. Detecting this kind of drift is non-trivial; there is no built-in signal telling you when your index has gone stale.

Tuning is another friction point. The two main parameters, m (number of connections per node) and ef_construction (size of the candidate list during build), have a significant effect on both recall and performance, and the right values are not universal. They depend on your embedding dimensionality, dataset size, and how tightly clustered your vectors are. Getting this wrong means either a slow build, a bloated index, or poor recall at query time.

Memory pressure is also a real concern. HNSW keeps the full graph in memory to deliver its best performance. For large datasets with high-dimensional vectors, this can be substantial. If the index spills to disk, the latency characteristics change entirely and you lose most of the benefit.

Finally, build time is front-loaded. Unlike IVFFlat which does a faster one-pass clustering, HNSW construction is expensive, especially for large m or ef_construction values. For workloads that need to rebuild indexes regularly, this cost adds up. Therefore the choice should be made intentionally depends on your data.

IVFFlat (Inverted File Flat)

This approach uses a well known clustering algorithm (K-Means) to group vectors into lists(clusters). Each list contains vectors closest to a particular centroid. During search the system first finds the closest centroid(s) to your query vector. Then it does exhaustive search only in those list(s).

• Pros: faster build time; uses less memory.
• Cons: lower recall; requires you to have data to be stored fully, before building the index (to calculate cluster centers).

-- Create an IVFFlat index
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) 
WITH (lists = 100);

Pro Tip: For IVFFlat, set roughly lists = #rows / 1000 for datasets up to 1M rows, or sqrt(rows) for larger ones.

Although common knowledge of IVFFlat says it’s lower recall, one way to recover some of that lost recall at scale is binary quantization. Instead of storing full-precision vectors, each dimension gets compressed down to a single bit. This makes the vectors drastically smaller, which means more of them fit in memory and the exhaustive search within each list gets much faster. The trade-off in raw recall is real, but at the scale where IVFFlat starts to struggle, the speed gains often let you probe more lists within the same latency budget, which brings recall back up. For datasets in the hundreds of millions, this combination can outperform a well-tuned HNSW index that is choking on memory pressure.

Tips & Tricks for Faster Index Building

1. By default, PostgreSQL only uses 2 background workers for index creation. If you have a 16-core server, you are wasting resources. For both IVFFlat and HNSW, you can increase max_parallel_maintenance_workers right before building the index. (Note: A value of 7 usually results in 8 total processes: 7 workers + 1 leader).

SET max_parallel_maintenance_workers = 7; - plus leader

2. HNSW graphs are built in memory. If the graph fits entirely in RAM (maintenance_work_mem), the build is blazing fast. If it spills to disk, performance creates a cliff.

If you see this notice, your memory is too low and you need to increase memory allocation temporarily for the session.:

NOTICE: HNSW graph no longer fits into maintenance_work_mem after 100000 tuples.

-- Allocate 8GB RAM for the build (ensure your server has enough!)
SET maintenance_work_mem = '8GB';

3. You can check exactly how far along the build is using Postgres internal stats.

SELECT phase, 
       round(100.0 * tuples_done / nullif(tuples_total, 0), 1) AS progress_percent 
FROM pg_stat_progress_create_index;

What to look for:

• IVFFlat Phases: initializing → performing k-means → assigning tuples → loading tuples. (Note: % usually updates only during loading tuples).
• HNSW Phases: initializing → loading tuples.

Pro Tip: Always build the index after inserting your initial bulk data. Inserting data into an indexed table is significantly slower than building the index on a populated table.

Next part I’ll be talking about Advanced Features & Optimization aspect for pgvector.