PostgreSQL `pgvector` in Production: HNSW vs. IVFFlat Indexes for Low-Latency RAG Search

Workload Isolation: To ensure high-dimensional vector similarity queries do not saturate primary transactional memory or block write threads, consider offloading analytical querying via zero-impact analytics using PostgreSQL read replicas and DuckDB.

The Challenge of In-Database Vector Search at Scale

With the widespread adoption of Retrieval-Augmented Generation (RAG) and semantic search architectures, engineers face a critical infrastructure decision: deploy dedicated vector databases (such as Pinecone, Qdrant, or Milvus) or leverage the open-source pgvector extension directly within PostgreSQL. Maintaining a separate vector database introduces operational complexity: data synchronization lag, dual backup pipelines, distributed transaction boundaries, and network serialization overhead.

PostgreSQL with pgvector eliminates these architectural headaches by storing vector embeddings alongside existing relational tables. However, vector similarity search across high-dimensional vectors (such as 1536-dimensional embeddings from OpenAI or 1024-dimensional embeddings from BGE/Cohere) is computationally intensive. Without proper indexing and memory sizing, sequential vector scans across 500,000 rows quickly escalate query latency from 8ms to over 1,500ms, saturating server CPU.

1. Index Anatomy: HNSW vs. IVFFlat

pgvector provides two primary Approximate Nearest Neighbor (ANN) index algorithms, each with distinct performance characteristics:

1. IVFFlat (Inverted File Flat): IVFFlat clusters vectors into a predefined number of Voronoi partitions (called lists). During querying, the engine calculates the distance only to the closest centroids, inspecting a subset of vectors (controlled by ivfflat.probes). While IVFFlat builds quickly and consumes very little RAM, its recall accuracy degrades rapidly as datasets grow, and it requires pre-populating the table before building the index.
2. HNSW (Hierarchical Navigable Small World): HNSW constructs a multi-layer graph where lower layers contain high-density local connections and higher layers contain long-range skip links. Query traversal begins at the sparse top layer and greedily descends to find nearest neighbors with logarithmic time complexity. HNSW delivers outstanding sub-10ms query latency and 98%+ recall accuracy, but requires significantly more memory during index construction.

2. Empirical Comparison Matrix

The operational and architectural differences between IVFFlat and HNSW in production environments are compared below:

3. Production Tuning for HNSW Indexes

To build an HNSW index on a production table without running out of memory or locking readers, configure your session parameters before issuing the index creation DDL:

-- 1. Allocate sufficient maintenance memory to build the graph in RAM
SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;

-- 2. Create HNSW index using Cosine Distance (<=>)
CREATE INDEX CONCURRENTLY idx_knowledge_embeddings_hnsw 
ON knowledge_document_chunks 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);

-- 3. Tune runtime search depth for fast queries (set in postgresql.conf or session)
SET hnsw.ef_search = 40;

4. Hybrid Search: Combining Vector Distance with Full-Text Search

One of the greatest advantages of pgvector is executing hybrid semantic-keyword queries within a single transaction using PostgreSQL Reciprocal Rank Fusion (RRF):

WITH semantic_search AS (
    SELECT id, RANK() OVER (ORDER BY embedding <=> :query_vector) AS rank
    FROM knowledge_document_chunks
    ORDER BY embedding <=> :query_vector
    LIMIT 30
),
keyword_search AS (
    SELECT id, RANK() OVER (ORDER BY ts_rank_cd(search_vector, plainto_tsquery('english', :query_text)) DESC) AS rank
    FROM knowledge_document_chunks
    WHERE search_vector @@ plainto_tsquery('english', :query_text)
    LIMIT 30
)
SELECT 
    k.id, 
    k.title, 
    k.content,
    COALESCE(1.0 / (60 + s.rank), 0.0) + COALESCE(1.0 / (60 + kw.rank), 0.0) AS hybrid_score
FROM knowledge_document_chunks k
LEFT JOIN semantic_search s ON k.id = s.id
LEFT JOIN keyword_search kw ON k.id = kw.id
WHERE s.id IS NOT NULL OR kw.id IS NOT NULL
ORDER BY hybrid_score DESC
LIMIT 10;

Key Architectural Takeaways

For modern production applications, HNSW indexes in pgvector provide an optimal balance of sub-10ms query latency, high recall accuracy, and effortless incremental insert maintenance. By properly provisioning maintenance_work_mem and pairing HNSW with PostgreSQL's native full-text search, you achieve enterprise-grade hybrid retrieval without managing external vector database clusters.