Optimizing PostgreSQL for RAG: How to Configure pgvector for Sub-10ms Queries on Millions of Vectors

Introduction: The Scalability Challenge in Enterprise RAG

Retrieval-Augmented Generation (RAG) has emerged as the architecture of choice for enterprises looking to ground Large Language Models (LLMs) in proprietary business data. At the heart of any production-grade RAG pipeline lies the vector database, responsible for conducting semantic searches across millions of document chunks. While specialized vector databases exist, PostgreSQL with the pgvector extension has become immensely popular due to its operational simplicity, ACID compliance, and the ability to unify relational and vector data in a single system.

However, running vector searches at scale presents unique challenges. As your dataset grows from thousands to millions of high-dimensional embeddings (such as those from OpenAI's text-embedding-3-large or Cohere's embed-english-v3.0), naive implementations quickly suffer from high latency and resource exhaustion. To maintain a responsive user experience, engineering teams must target a sub-10ms query latency. Achieving this performance at scale requires deep architectural understanding and precise configuration of PostgreSQL and the pgvector extension.

Understanding the Baseline: IVFFlat vs. HNSW

Before diving into optimization configurations, it is critical to select the correct indexing mechanism within pgvector. The extension primarily supports two types of Approximate Nearest Neighbor (ANN) indexes: IVFFlat (Inverted File Flat) and HNSW (Hierarchical Navigable Small World).

IVFFlat: Operates by clustering vector space into lists. It offers faster build times and a smaller memory footprint but suffers from lower recall accuracy and degraded query performance at extreme scales.
HNSW: Builds a multi-layer graph structure where layers represent different granularities of vector connections. HNSW provides exceptional query latency and high recall accuracy even under heavy concurrent loads, making it the golden standard for enterprise RAG applications despite its higher memory consumption and slower index build times.

For applications managing millions of vectors with a strict sub-10ms latency requirement, HNSW is non-negotiable. The remainder of this guide focuses exclusively on unlocking the maximum performance of HNSW indexes within pgvector.

Step 1: Production-Grade Table Schema and Data Insertion

Proper schema design is the foundation of high-performance vector search. Ensure your vector column explicitly defines the dimensions matching your embedding model. For example, using a 1536-dimensional model:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE document_embeddings (
    id BIGSERIAL PRIMARY KEY,
    document_id UUID NOT NULL,
    chunk_index INT NOT NULL,
    content TEXT NOT NULL,
    embedding vector(1536) NOT NULL
);

Pro Tip: To accelerate mass data ingestion prior to indexing, always load your data using the COPY command or multi-row INSERT statements in batches rather than individual transactions. Build your HNSW index after the initial bulk load is complete to avoid massive index rebuild overhead.

Step 2: Configuring the HNSW Index for Peak Performance

When creating an HNSW index, two parameters dictate the trade-off between build time, memory size, and search accuracy: m and ef_construction. Choosing the right values is crucial for handling millions of rows efficiently.

CREATE INDEX ON document_embeddings 
USING hnsw (embedding vector_cosine_ops) 
WITH (m = 16, ef_construction = 64);

Deconstructing HNSW Parameters:

m (Default: 16): Represents the maximum number of bidirectional connection links created for each new element at each graph layer. Increasing m (e.g., to 24 or 32) improves recall accuracy for highly dimensional data but increases index size and build time. For 1536 dimensions, 16 to 24 is typically optimal.
ef_construction (Default: 64): Determines the size of the dynamic candidate list evaluated during index construction. A higher value (e.g., 128 or 256) results in a more accurate graph topology, directly improving search quality at the expense of longer index creation times.

Step 3: Fine-Tuning Server Memory and Hardware Allocations

An optimized graph index is useless if PostgreSQL is forced to read it from disk. For sub-10ms queries, the entire HNSW index must fit comfortably within memory (RAM). If PostgreSQL drops to disk to resolve a vector query, latency will instantly spike past 100ms.

Modify your postgresql.conf file to align with the following enterprise-scale memory guidelines:

shared_buffers: Allocate 25% to 40% of total system RAM to PostgreSQL's shared buffers to ensure frequently accessed index pages stay cached in memory.
work_mem: Vector operations are CPU and memory-intensive. Increase work_mem (e.g., to 64MB or 128MB) to accommodate complex sorting and graph traversal operations per query connection.
maintenance_work_mem: Building HNSW indexes on millions of rows requires massive memory allocations. Set this to at least 2GB to 4GB depending on your instance size to avoid index creation failures.

Step 4: Runtime Query Optimization (The ef_search Parameter)

Once the index is built and memory is tuned, you can control the speed-versus-accuracy trade-off at the session or query level using the hnsw.ef_search parameter. This variable dictates the size of the dynamic candidate list checked during a live search query.

-- Set local session parameter for RAG search
SET hnsw.ef_search = 40;

-- Execute the semantic search query
SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM document_embeddings
ORDER BY embedding <=> $1
LIMIT 5;

By tuning hnsw.ef_search downward (e.g., to 20 or 30), you drastically reduce the number of graph nodes evaluated, pushing latencies down to the single-digit millisecond range while maintaining acceptable recall. Conversely, increasing it to 100 maxes out recall fidelity but consumes more CPU cycles.

Step 5: Monitoring, Vacuuming, and Maintenance

Production environments are dynamic. Continuous CRUD operations on your document tables cause index bloat and fragment graph paths. To ensure performance does not degrade over time:

Execute Routine VACUUM: Use VACUUM ANALYZE document_embeddings; regularly to clean up dead tuples and update database statistics, allowing the query planner to make optimal choices.
Monitor Index Cache Hit Ratio: Utilize PostgreSQL internal statistics tables to ensure your vector index hit rate remains above 99%. A dropping ratio indicates your system needs more RAM.

Conclusion: The Sub-10ms Standard

Scaling a RAG application to millions of vectors within PostgreSQL does not require abandoning the platform for specialized niche infrastructure. By leveraging HNSW indexing, optimizing m and ef_construction parameters, aggressively tuning system memory variables, and dynamically modifying ef_search, you can easily achieve sub-10ms query latencies. This strategy allows you to enjoy the unparalleled stability of PostgreSQL while meeting the high-performance demands of modern artificial intelligence workflows.