pgvector Performance Limits Measured: When HNSW Breaks Down at 1M and 10M Vectors

When I first built a RAG pipeline, I adopted pgvector without much thought, telling myself "we're already using PostgreSQL, so pgvector should be enough." The benchmark numbers looked good, after all. But when our data crossed 5 million vectors and I added a single category filter, the P99 latency spiked to 10x the average. I can still vividly remember that moment at 2 AM, staring at a Slack alert and thinking, "Oh, I missed something."

There's exactly one reason benchmarks and production differ. pgvector's real limits only emerge under real-world conditions where vector count, dimensionality, concurrency, and metadata filter complexity all increase together. Benchmarks run in single-threaded, low-concurrency, filter-free pure ANN search environments tell a completely different story from actual production services.

After reading this post, you'll be able to predict numerically when pgvector will hit its limits in your environment, and decide what you need to prepare before that happens. This will be especially useful if you've just adopted pgvector, or if you're worried about "it seems fine now, but will it cause problems later?"

Core Concepts

The Two Index Types pgvector Offers, and Their Trade-offs

To understand how pgvector works, it helps to first understand the two index types. Which index you choose completely changes memory usage and search accuracy.

HNSW (Hierarchical Navigable Small World) offers fast search speeds and high recall. The catch is that the entire index must be loaded into memory. This is no problem when data is small, but beyond 10 million vectors, the index alone requires 80–120 GB. If RAM is insufficient, it automatically falls back to a disk-based build, which increases build time by 10–50x. I didn't know this at first and once ran a large index build with maintenance_work_mem at its default value — the server ran all night.

What is maintenance_work_mem? It's the memory limit PostgreSQL can use for maintenance operations like index builds and VACUUM. The default is set low at 64–256 MB, so for large HNSW builds, it falls back to disk-based mode and build times increase dramatically.

IVFFlat uses less memory but has lower search accuracy than HNSW, and you need to decide the number of clusters (the lists parameter) before creating the index. If you get this value wrong in the early stages when you don't know your data distribution well, you'll need to rebuild the entire index later.

-- HNSW index: m=16, ef_construction=64 are typical starting points
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
 
-- Adjust accuracy/speed balance with ef_search during search
-- Higher values are more accurate but slower
SET hnsw.ef_search = 100;
 
-- IVFFlat: sqrt(row_count) is a commonly used guideline for lists
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

What is Recall? In ANN (Approximate Nearest Neighbor) search, it's the proportion of the true Top-K nearest vectors that were actually returned. A recall of 0.95 means 95% of exact results were found — it's used as a trade-off metric between speed and accuracy.

If You Want to Save Memory: Quantization Options

When discussing memory issues with HNSW indexes, quantization can't be left out. It's been supported since pgvector 0.7+, but is surprisingly little-known.

The default vector type stores each dimension as float32 (4 bytes). Switching to halfvec (float16, 2 bytes) cuts the index size in half. The accuracy loss is negligible for most workloads.

-- Create a column with float16-based halfvec type (~50% memory savings)
ALTER TABLE documents ADD COLUMN embedding halfvec(1536);
 
-- HNSW index based on halfvec
CREATE INDEX ON documents USING hnsw (embedding halfvec_cosine_ops)
  WITH (m = 16, ef_construction = 64);
 
-- Check the actual index size directly
SELECT pg_size_pretty(pg_relation_size('documents_embedding_idx'));

You can compare the value from this query against the "expected size by scale" table in the practical application section to verify which range your environment falls into.

How to Estimate Index Size in Advance

If you want to roughly estimate index size ahead of time, you can use this formula:

HNSW index size ≈ vector count × dimensions × 4 bytes × index overhead multiplier (1.3–2.0)

For example, with 1 million vectors and 1,536 dimensions: 1,000,000 × 1,536 × 4 × 1.5 ≈ 9.2 GB. With halfvec, substitute 2 bytes for the 4 bytes. It's cut in half.

Why Benchmarks and Real-World Measurements Differ

Most official benchmarks are measured under single-threaded, low-concurrency conditions. But production environments are different. As concurrent users increase, P95 and P99 latencies rise much faster than the P50 (median) latency.

The Reddit engineering team, working with 340 million vectors in a high-concurrency environment, observed that when CPU became focused on interpreting metadata filters, P99 latency spiked 10x relative to the average. This is a number that never appears on single-threaded benchmark graphs.

Tail latency (P95, P99): The response time for the slowest 5% and 1% of all requests. Even if average latency is low, high tail latency means some users experience slow responses. As concurrency increases, tail latency degrades much faster than the average.

HNSW Indexes and VACUUM: An Easy Trap to Miss in Production

There's one more thing worth highlighting. HNSW indexes can suffer from index bloat in environments where vectors are frequently updated or deleted. PostgreSQL's autovacuum handles regular tables well, but index bloat requires separate attention. As bloat accumulates, search performance gradually degrades — and because it worsens slowly rather than suddenly, it's harder to catch.

Since REINDEX requires stopping the service, use REINDEX CONCURRENTLY in production:

-- Rebuild the index without service downtime (build time will be longer)
REINDEX INDEX CONCURRENTLY documents_embedding_idx;
 
-- Monitor index size trends
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE tablename = 'documents'
  AND indexname LIKE '%embedding%';

Practical Application

Example 1: Small-to-Medium SaaS Semantic Search (Up to 5 Million Vectors)

If your vector count is 5 million or below, Example 1 applies. Honestly, pgvector works quite well in this range. On a single PostgreSQL instance with 1,024-dimension vectors, 5K–15K QPS with P95 latency of 5–50ms meets most SaaS production requirements. Since you only need to add one extension to your existing PostgreSQL stack, operational complexity is low too. This is the range where "can we just use this for everything?" feels accurate — and it actually is.

-- Install pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;
 
-- Add embedding column (1536 dimensions: based on OpenAI text-embedding-3-small)
ALTER TABLE documents ADD COLUMN embedding vector(1536);
 
-- Allocate sufficient memory before building the HNSW index
-- maintenance_work_mem: the memory limit PostgreSQL can use for index builds
SET maintenance_work_mem = '4GB';
 
CREATE INDEX documents_embedding_idx ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
 
-- Semantic search query
SELECT id, content, embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1
LIMIT 10;

After creating the index, there's something you should always verify:

-- Confirm the index is being used
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, content, embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1
LIMIT 10;

If you see Index Scan using documents_embedding_idx on documents in the output, it's working correctly. If you see Seq Scan on documents, the index isn't being used — it's worth checking both the hnsw.ef_search value and the enable_indexscan setting.

Example 2: Metadata Filter + Vector Search Combination (1M+ Vectors with Filtered Queries)

When your vector count exceeds 1 million and you start combining vector search with conditional filters like "only from this user's documents" or "only within this category," Example 2 applies. It's also the situation you'll encounter most often in practice.

I still remember the bewilderment when I saw query time triple in the logs after adding a single category filter. I had clearly created an index, so I spent a long time wondering why it was slow. Up through pgvector 0.7.x, this combination was tricky. If the filter was highly selective (few candidate results), the ANN index scan couldn't find enough results and would fall back to a sequential scan, or recall would drop sharply.

-- Before 0.8.0: this kind of query often failed to use the index properly
SELECT id, content
FROM documents
WHERE user_id = $1          -- selective filter
  AND category = ANY($2)    -- another filter
ORDER BY embedding <=> $3
LIMIT 10;
 
-- 0.8.0+: enable iterative index scanning (session level)
SET hnsw.iterative_scan = relaxed_order;
-- Resets on reconnect, so for permanent application:
ALTER DATABASE mydb SET hnsw.iterative_scan = relaxed_order;
 
-- Use EXPLAIN to verify index scan is being used
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, content
FROM documents
WHERE user_id = $1 AND category = ANY($2)
ORDER BY embedding <=> $3
LIMIT 10;

If you see Index Scan using ... (iterative) in the output, the iterative scanning from 0.8.0 is working. If you still see Seq Scan, the filter selectivity may be too high and the query planner has given up on the index — it's worth trying a higher hnsw.ef_search or reviewing the combination with a filter index.

relaxed_order vs strict_order: relaxed_order returns enough results even if they don't perfectly match distance ordering. strict_order guarantees distance ordering but requires more scanning. For most recommendation and search systems, relaxed_order is the practical choice.

Example 3: Large Scale (5M–50M Vectors) — Combining with pgvectorscale

When you exceed 5 million vectors, Example 3 applies. From this range onward, loading the entire HNSW index into RAM becomes burdensome in itself.

Timescale's pgvectorscale adds a disk-friendly index called StreamingDiskANN to address this problem. While HNSW requires the entire graph to be in RAM, StreamingDiskANN (based on the DiskANN algorithm) keeps only frequently accessed portions in memory and streams the rest from NVMe SSD — trading RAM for disk I/O. There is a May 2025 benchmark showing 471 QPS at 99% recall with 50 million vectors (Cohere embeddings, 768 dimensions).

-- After installing pgvectorscale
CREATE EXTENSION IF NOT EXISTS vectorscale CASCADE;
 
-- Create a StreamingDiskANN index
CREATE INDEX documents_diskann_idx ON documents USING diskann (embedding);
 
-- Search queries use the exact same syntax as pgvector
SELECT id, content, embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1
LIMIT 10;
 
-- Compare HNSW vs DiskANN index sizes
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE tablename = 'documents'
  AND indexname IN ('documents_embedding_idx', 'documents_diskann_idx');

At the same vector count, the DiskANN index size will be noticeably smaller than HNSW, and RAM pressure will drop visibly. However, since it relies on disk I/O performance, you may not get the expected benefit if you're not on NVMe SSD.

What about 100M+ vectors?

pgvectorscale also has its limits. Beyond 100 million vectors, it's worth considering dedicated vector databases like Qdrant or Milvus. Unlike pgvector (+pgvectorscale), which is single-node based, these natively support distributed indexing and online index updates. In environments where vectors are added in real time at high volume, pgvector can suffer degraded search quality while rebuilding an HNSW index, whereas dedicated databases like Qdrant are architected to update indexes in real time. GPU acceleration is another difference.

Pros and Cons Analysis

Pros

If you choose pgvector, the biggest real-world advantage is that you don't need separate "vector DB team" and "DB team" — one PostgreSQL operations team handles everything. Until you reach significant scale, that simplicity itself is enormous value.

Cons and Caveats

If the advantages converge to "it's good because it's PostgreSQL," the disadvantages mostly converge to "it's hard because it's PostgreSQL." The advantage of existing stack integration is simultaneously the weakness of single-node limitations. Understanding this trade-off clearly is what prevents unpleasant surprises later.

Expected HNSW Index Sizes by Scale (for reference)

The Most Common Mistakes in Practice

1. Building an index with maintenance_work_mem at its default value: At the default (64–256 MB), large HNSW builds fall back to disk-based mode and build times increase by tens of times. I once watched a build run all night without knowing this — it's strongly recommended to set SET maintenance_work_mem = '4GB' or higher before building an index.

2. Guessing at performance issues without EXPLAIN ANALYZE: When a filter + vector search query is slow, if you don't check whether it's using an index scan or falling back to a sequential scan, you'll end up optimizing the wrong thing. In EXPLAIN (ANALYZE, BUFFERS) output, Index Scan means it's working; Seq Scan means it's worth checking hnsw.ef_search and the filter conditions first.

3. Not knowing that session-level SET resets on reconnect: SET hnsw.iterative_scan = relaxed_order is a session-level setting. It resets on reconnect, so for permanent application you need to use ALTER DATABASE mydb SET hnsw.iterative_scan = relaxed_order.

4. Locking in an index strategy without accounting for scale growth: If you set the wrong lists value for IVFFlat early on, or don't account for HNSW memory requirements when data grows 10x, you'll end up in a situation where rebuilding without service downtime is difficult. It's good practice to revisit the index strategy appropriate for your current scale each quarter.

Closing Thoughts

The biggest difference between before and after reading this post is this: instead of viewing pgvector simplistically as "good" or "slow at scale," you can now judge under what conditions and for what reasons its limits appear, using numbers and patterns. Below 5 million vectors it's a genuinely strong option, metadata filter combinations are greatly improved in 0.8.0, and beyond that scale, halfvec quantization, pgvectorscale, and dedicated vector databases become available as options in sequence.

Three steps you can take right now:

1. Diagnose your current environment: Add EXPLAIN (ANALYZE, BUFFERS) to your main vector search queries and check whether you see Index Scan or Seq Scan. If Seq Scan appears, it's worth checking both the hnsw.ef_search value and your filter conditions.

2. Upgrade to 0.8.0 and test iterative scanning: If you're combining metadata filters with ANN search, apply ALTER DATABASE mydb SET hnsw.iterative_scan = relaxed_order to your staging environment and measure the change in P95/P99 latency.

3. Simulate scale thresholds: Even if you currently have fewer than 1 million vectors, you can estimate your expected scale 12 months from now using the formula: expected vector count × dimensions × 4 bytes × 1.5 — if this value exceeds 70% of your server's RAM, it's recommended to evaluate halfvec type or pgvectorscale in advance.

References

• pgvector GitHub Official Repository
• pgvector 0.8.0 Release Notes | PostgreSQL.org
• AWS Aurora pgvector 0.8.0 Performance Analysis
• pgvector Performance Benchmarks and 5 Ways to Improve | Instaclustr
• Why pgvector Benchmarks Lie | The New Stack
• pgvectorscale GitHub Repository | Timescale
• HNSW Indexes with pgvector | Crunchy Data
• pgvector Memory, Quantization, and Index Build Strategies | DEV Community