PostgreSQL pgvector has become the go-to choice for teams building AI-powered features without adding another database to their infrastructure. If you’re working on semantic search, RAG systems, or embedding-based recommendations, you’ve likely encountered the same challenge: vector search performance degrades as your data grows. The difference between a 50ms query and a 2-second query often comes down to one decision: which index strategy to use.
This article walks through the practical side of running pgvector in production. We’ll compare the two main indexing approaches, cover the tuning knobs that actually matter, and share patterns from teams scaling to millions of vectors without moving to a dedicated vector database.
Why Vector Search Indexing Matters
Without an index, PostgreSQL scans every vector in your table and computes distances to your query vector. With 100,000 embeddings, this becomes slow. With 10 million, it becomes unusable. Indexes solve this by organizing vectors in a way that lets PostgreSQL skip most of the table and search only nearby candidates.
The key insight: different indexes make different tradeoffs. Some are fast but use more memory. Some are memory-efficient but slower at query time. Some excel at exact nearest neighbors while others optimize for approximate search. Choosing the right one for your workload means balancing resources, query speed, and accuracy requirements.
IVFFlat: Fast to Build, Memory-Light
IVFFlat (Inverted File with Flat quantization) divides your vectors into clusters. When you search, PostgreSQL finds the closest clusters first, then searches within those clusters. It’s like finding a restaurant by checking only the neighborhoods closest to you, not the entire city.
Setup is straightforward:
CREATE TABLE embeddings (
id BIGSERIAL PRIMARY KEY,
content TEXT,
embedding vector(1536)
);
CREATE INDEX ON embeddings USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
The lists parameter controls how many clusters to create. According to DigitalOcean’s pgvector documentation, use lists = rows / 1000 for datasets under 1 million rows, and lists = sqrt(rows) for larger datasets. More lists means finer partitioning, which usually speeds up search but increases build time and memory usage.
IVFFlat works well when:
- Your data is mostly static or grows slowly
- You have memory constraints
- You’re building batch pipelines with nightly reindexing
- You need fast index builds
The design choice: IVFFlat partitions vectors at build time based on the data present then. If a true neighbor lives in a cluster you didn’t probe during search, you won’t find it. This is usually acceptable for semantic search where a slightly different result is often good enough. However, it means you’re optimizing for speed and memory efficiency rather than guaranteed accuracy.
HNSW: Better Recall, Higher Memory Cost
HNSW (Hierarchical Navigable Small World) builds a graph structure where each vector is connected to its nearest neighbors at multiple levels. Searching means starting at the top level and navigating down, like finding a restaurant by asking locals for the closest good option, then asking those people where the best spot is nearby.
Setup:
CREATE INDEX ON embeddings USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
The parameters:
m: how many neighbors each vector connects to. Default is 16. Higher values improve recall and query quality but increase memory and build time. Range is typically 8 to 64.ef_construction: how much exploration to do while building the graph. Default is 64. Higher values create a better quality index at the cost of longer build times. Range is typically 64 to 200.
HNSW excels when:
- You need high recall (very accurate nearest neighbors, typically 95% or higher)
- Your data changes frequently (HNSW handles inserts and updates better than IVFFlat)
- You can afford more memory for the index
- You’re building real-time features like chat or search where users expect accurate results immediately
The design choice: HNSW uses more memory than IVFFlat for the same data. According to Rivestack’s analysis, for 1,536-dimensional vectors, comfortable in-memory ceilings are roughly 350k vectors on 4 GB, 600k on 8 GB, and 1M on 16 GB. Index builds take longer, but query speed is often faster and recall is consistently higher.
Choosing Between Them: A Decision Framework
According to production experience from Rivestack, here’s how to decide:
Default to HNSW when:
- Your table takes ongoing inserts or updates
- You need recall of 90% or higher at single-digit-millisecond query times
- Your team won’t actively tune and rebuild indexes
- Your dataset is under approximately 1 million rows
Choose IVFFlat when all of these hold:
- Your corpus is static or rebuilt wholesale on a schedule (nightly batch pipelines qualify)
- Build time or build memory is your binding constraint
- Moderate recall is acceptable for your use case
- Someone on your team will verify recall after each rebuild
One team we know started with IVFFlat on a multi-tenant SaaS platform. As their customer base grew and they added more AI features, they found that approximate search was affecting their product quality. Switching to HNSW improved their query times from 80ms to 45ms and increased their nearest-neighbor accuracy from 92% to 99%. The memory increase was acceptable because more accurate results meant fewer retries and better user experience.
Tuning for Your Workload
Index parameters are a starting point, not the final answer. Measure and adjust based on your actual queries:
For IVFFlat
Start with lists = sqrt(row_count) for large datasets and run a test query:
EXPLAIN ANALYZE
SELECT id, embedding <-> '[0.1, 0.2, ...]'::vector AS distance
FROM embeddings
ORDER BY embedding <-> '[0.1, 0.2, ...]'::vector
LIMIT 10;
If the query is slow, increase lists. If index builds are taking too long, decrease it. If recall is lower than acceptable, increase the search probe count:
SET ivfflat.probes = 10; -- default is 1
SELECT id, embedding <-> query_vector AS distance
FROM embeddings
ORDER BY embedding <-> query_vector
LIMIT 10;
According to Multigrid’s tuning guide, a reasonable starting point for probes is sqrt(lists).
For HNSW
Start with m = 16, ef_construction = 64. If builds are too slow, lower ef_construction. If query speed is poor, increase ef_search at query time:
SET hnsw.ef_search = 100; -- default is 40
SELECT id, embedding <-> query_vector AS distance
FROM embeddings
ORDER BY embedding <-> query_vector
LIMIT 10;
Higher ef_search values improve recall at the cost of latency. Start with 100 and adjust based on your latency budget and recall measurements.
Scaling Vector Search in Multi-Tenant Systems
In a multi-tenant SaaS, each customer’s vectors live in the same table. You need to partition by tenant and index each partition separately:
CREATE TABLE embeddings (
tenant_id BIGINT NOT NULL,
id BIGSERIAL NOT NULL,
content TEXT,
embedding vector(1536),
PRIMARY KEY (tenant_id, id)
) PARTITION BY LIST (tenant_id);
CREATE TABLE embeddings_tenant_1 PARTITION OF embeddings
FOR VALUES IN (1);
CREATE INDEX ON embeddings_tenant_1 USING hnsw (embedding vector_cosine_ops);
CREATE TABLE embeddings_tenant_2 PARTITION OF embeddings
FOR VALUES IN (2);
CREATE INDEX ON embeddings_tenant_2 USING hnsw (embedding vector_cosine_ops);
This isolates each tenant’s index and prevents one large tenant from affecting index performance for everyone else. Each partition can have its own tuning parameters too.
A query then becomes:
SELECT id, embedding <-> query_vector AS distance
FROM embeddings
WHERE tenant_id = $1
ORDER BY embedding <-> query_vector
LIMIT 10;
PostgreSQL automatically searches only the relevant partition.
Monitoring and Maintenance
As vectors accumulate, index bloat can develop. Monitor it:
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_indexes
WHERE tablename = 'embeddings'
ORDER BY pg_relation_size(indexrelid) DESC;
Rebuild indexes periodically to reclaim space and maintain performance:
REINDEX INDEX embeddings_embedding_idx;
On large tables, use CONCURRENTLY to avoid locking:
REINDEX INDEX CONCURRENTLY embeddings_embedding_idx;
Track query performance with slow query logging:
SET log_min_duration_statement = 100; -- log queries slower than 100ms
Cost-Performance in Production
Here’s what matters in practice:
Memory: HNSW uses more memory, but you pay once for the index, then every query benefits from that investment. You’re not paying per-query like some vector databases. For reference, Rivestack reports that 1 million 1,536-dimensional vectors on HNSW require roughly 16 GB of memory. IVFFlat uses significantly less for the same data.
Query latency: Both can achieve 10 to 50ms for top-k queries on well-tuned systems. HNSW typically delivers faster queries and higher recall. IVFFlat can be competitive if you’re comfortable with approximate results and tune the probe count aggressively.
Index build time: IVFFlat builds faster, often in minutes. HNSW takes longer, sometimes hours on 100 million vectors. This matters if you’re re-indexing frequently.
Operational overhead: PostgreSQL handles backups, replication, and recovery. You don’t need to learn a new ops stack. This is why many teams choose pgvector over dedicated vector databases: they already know how to run PostgreSQL.
When to Stay with pgvector vs When to Move
pgvector is the right choice if:
- Your vectors are part of a larger relational schema (user data, documents, products)
- You need ACID guarantees and multi-tenant isolation
- Your team already runs PostgreSQL
- Your query patterns mix vector search with SQL filters
A dedicated vector database makes sense if:
- You’re doing pure vector search at extreme scale (billions of vectors)
- You need specialized distance metrics or custom algorithms
- Your infrastructure team wants to separate storage layers
Most teams building AI features fall into the first category. The operational simplicity and ACID guarantees outweigh the specialized performance of dedicated tools.
Wrapping Up
Vector search in PostgreSQL isn’t magic, but it doesn’t need to be complicated. Start with HNSW if you can afford the memory and want predictable quality. Use IVFFlat if you’re constrained on resources or have a write-heavy workload. Measure your specific queries and adjust the parameters. Monitor index size and rebuild periodically.
The best index is the one that matches your actual workload. Spend an afternoon profiling, then stop worrying about it. Your AI features will be fast, your data will be safe, and your ops team will thank you for not adding another database to their stack.
Should I use HNSW or IVFFlat for my production system?
Start with HNSW if you need high recall (95% or higher) and can afford the memory cost. Use IVFFlat if you’re memory-constrained or have mostly static data that you rebuild on a schedule. Profile your actual queries and measure recall and latency before deciding. Many teams start with IVFFlat and migrate to HNSW as they scale and product quality becomes more important.
How do I know if my pgvector index is performing well?
Use EXPLAIN ANALYZE on your search queries to see how many rows are scanned. For top-10 queries, a well-tuned index should scan only a few hundred to a few thousand rows, not millions. Monitor query duration with log_min_duration_statement. If queries are consistently above 100ms on top-k searches, your index parameters need tuning or your hardware needs more memory.
Can pgvector handle millions of vectors?
Yes. Teams run 10 to 100 million vectors on pgvector with good performance. Beyond that, you may hit memory or query latency limits. Partitioning by tenant or time helps. At extreme scale (billions of vectors), a dedicated vector database might be more appropriate, but most AI applications don’t need that.
What’s the best way to handle vector updates in pgvector?
For frequent updates, HNSW handles them better than IVFFlat because HNSW supports incremental updates without requiring a full rebuild. IVFFlat may need periodic reindexing if data distribution shifts significantly. If you’re updating embeddings constantly (for example, retraining daily), consider a batch update pattern: insert new vectors into a staging table, reindex once, then swap. This avoids index bloat.
Do I need a separate vector database if I’m using pgvector?
Not usually. pgvector plus PostgreSQL gives you vector search, ACID transactions, multi-tenant isolation, and proven operational tools. Use a dedicated vector database only if you need extreme scale (billions of vectors), specialized distance metrics, or your infrastructure team wants to separate storage layers.
