Real-World Problem: The Day Vector Search Hit a Wall
Six months ago, our team launched an enterprise RAG knowledge engine built on PostgreSQL and pgvector. In staging, performance looked stellar. Cosine distance queries across 40,000 embedded support tickets returned in roughly 18 milliseconds. We were thrilled: no standalone vector database to manage, zero sync lag, and minimal infrastructure overhead.
Then production scale hit us. Within three months, automated ingestion pipelines pushed 6.5 million documents into a single PostgreSQL table, each carrying 1536-dimensional embeddings from OpenAI’s text-embedding-3-large. Latency cratered almost overnight. P95 query times skyrocketed from 20ms to an unusable 2.8 seconds. Peak-hour traffic pinned our 32-vCPU database instance at 98% utilization, and connection pool timeouts started cascading across client apps.
Our dashboards showed a clear disaster pattern: query throughput collapsed from 140 QPS down to barely 12 QPS. Random I/O choked the 8,000-IOPS NVMe array, and our standard indexing strategy ground to a halt.
Root Cause Analysis: Why Default Lookups Break at Scale
Most teams starting with pgvector either rely on sequential scans or spin up an IVFFlat index. Here is why both approaches broke down under multi-million-row production workloads:
1. The IVFFlat Recall-Speed Dilemma
IVFFlat partitions vectors into Voronoi cells via k-means clustering (lists = N). At query time, the engine checks only a subset of clusters, defined by ivfflat.probes.
On our 6.5-million-row dataset, hitting acceptable recall above 92% forced us to crank probes from 10 up to 64. That caused severe disk read amplification, dragging response times down to the speed of raw table scans. Even worse, as daily inserts poured in, centroid drift eroded recall down to 78% unless we rebuilt the index entirely.
2. Working Memory Starvation
B-Tree indexes follow tidy, predictable memory layouts. Approximate nearest neighbor (ANN) graphs do not. Instead, graph traversals jump through scattered memory pointers across non-contiguous blocks. When an index outgrows PostgreSQL’s shared_buffers, every single graph hop triggers a random disk read. Our vector index swelled to 42 GB—on an instance with only 64 GB of RAM and 16 GB of shared buffers. Disk thrashing was inevitable.
3. Monolithic Table Bloat
Our document repository pooled multi-tenant enterprise records across three fiscal years. Querying an unpartitioned 6.5-million-row table forced PostgreSQL to evaluate graphs across completely irrelevant tenants and archived records before applying metadata filters.
Evaluating the Options: Three Architectural Paths
To stop production timeouts, we tested three approaches during a four-week benchmark sprint on AWS RDS:
Option 1: Migrating to a Dedicated Vector DB (Qdrant / Pinecone / Milvus)
An external vector database felt like the standard advice. Yet it created major engineering friction: dual-write transaction synchronization, eventual consistency lags between relational data and embeddings, and an estimated $1,800/month in extra SaaS fees. Losing native transactional ACID guarantees made this a tough sell for enterprise audit compliance.
Option 2: Vertical Hardware Scaling with IVFFlat Tuning
We bumped server RAM from 64 GB to 256 GB and scheduled weekly IVFFlat re-clustering jobs. This band-aid trimmed latency to 450ms. But exclusive locks disrupted ingestion pipelines for nearly 90 minutes each weekend, and recall still degraded midweek.
Option 3: HNSW Indexing Combined with Declarative Table Partitioning
pgvector introduced native Hierarchical Navigable Small World (HNSW) graphs. HNSW constructs multi-layer skip graphs directly in Postgres storage, removing the need for periodic cluster maintenance. Pairing HNSW with native range or list partitioning keeps each graph small, isolated, and cached neatly in memory.
The Solution: Production HNSW and Partitioning
We rolled this architecture into production with immediate results. P95 latency plummeted from 2,800ms to 14ms across an active 8-million-vector corpus. Read IOPS dropped by 85%, and CPU usage settled under 30% during peak traffic.
Here is the implementation walkthrough.
Step 1: Enable the pgvector Extension
Verify your PostgreSQL instance runs pgvector 0.5.0 or later, then enable the extension:
CREATE EXTENSION IF NOT EXISTS vector;
Step 2: Create Declarative Partitions
Choose partition keys matching your query patterns, such as creation timestamp or tenant boundaries. In our setup, quarterly range partitions capped individual index files at roughly 8 GB. This easily fit within RAM:
-- Master table partitioned by date range
CREATE TABLE enterprise_documents (
id BIGSERIAL,
tenant_id UUID NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
content TEXT NOT NULL,
metadata JSONB DEFAULT '{}'::jsonb,
embedding vector(1536) NOT NULL,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
-- Quarterly partitions
CREATE TABLE enterprise_documents_2025_q1 PARTITION OF enterprise_documents
FOR VALUES FROM ('2025-01-01 00:00:00+00') TO ('2025-04-01 00:00:00+00');
CREATE TABLE enterprise_documents_2025_q2 PARTITION OF enterprise_documents
FOR VALUES FROM ('2025-04-01 00:00:00+00') TO ('2025-07-01 00:00:00+00');
Step 3: Tune Memory and Build HNSW Indexes
Building HNSW graphs requires dedicated scratch memory. Temporarily increase maintenance_work_mem for your build session, and enable multi-worker builds if your CPU allows:
-- Allocate build memory and parallelism
SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 4;
-- Create HNSW indexes per partition
CREATE INDEX idx_docs_2025_q1_hnsw_cosine
ON enterprise_documents_2025_q1
USING hnsw (embedding vector_cosine_ops)
WITH (m = 24, ef_construction = 128);
CREATE INDEX idx_docs_2025_q2_hnsw_cosine
ON enterprise_documents_2025_q2
USING hnsw (embedding vector_cosine_ops)
WITH (m = 24, ef_construction = 128);
Key configuration choices:
- m = 24: Sets the maximum bidirectional links per node (default is 16). Bumping this to 24 or 32 substantially improves recall on 1536-dimensional embeddings, adding roughly 15% build time overhead.
- ef_construction = 128: Governs the dynamic candidate queue size explored during construction. A value of 128 balances index creation speed against top-10 nearest-neighbor accuracy.
Step 4: Execute High-Recall Production Queries
Tune hnsw.ef_search at runtime to trade query latency against recall. Crucially, always include partition keys in the WHERE clause so PostgreSQL activates partition pruning:
import psycopg2
def search_knowledge_base(query_vector, tenant_id, start_date, limit=5):
conn = psycopg2.connect("dbname=rag_db user=rag_user host=localhost")
with conn.cursor() as cur:
# Set candidate search breadth for this session
cur.execute("SET LOCAL hnsw.ef_search = 80;")
query = """
SELECT id, content, metadata, 1 - (embedding <=> %s::vector) AS similarity
FROM enterprise_documents
WHERE created_at >= %s
AND tenant_id = %s
ORDER BY embedding <=> %s::vector
LIMIT %s;
"""
cur.execute(query, (query_vector, start_date, tenant_id, query_vector, limit))
results = cur.fetchall()
conn.close()
return results
Production Lessons After 6 Months
- Partition pruning is non-negotiable: Without a filter on
created_at, the planner scans every partition index. Check withEXPLAIN (ANALYZE, BUFFERS)to verify partition elimination. - Warm the cache after restarts: Run
SELECT pg_prewarm('idx_docs_2025_q2_hnsw_cosine');after database maintenance to preload active graph layers into RAM before traffic hits. - Account for WAL generation: HNSW index construction is write-heavy. Building an 8 GB index generates 12–16 GB of WAL traffic. Monitor replication lag and ensure WAL archive disks have ample headroom.
Combining HNSW indexing with declarative partitioning matches the 15ms retrieval speeds of dedicated vector stores. Best of all, you keep standard PostgreSQL backups, point-in-time recovery, and unified relational joins in one place.
