Postgres as Autonomous Memory: Why Our AI Crashed When Indexing 10M Vectors

The Problem

When scaling a vector indexing workload from a moderate to a large number of entries, the system may collapse under memory pressure. This article examines the root causes of such a failure when using PostgreSQL with pgvector and the HNSW index, and the production-tested fixes that allow handling an order of magnitude more vectors without incident.

Initial Setup

The table storing vectors is straightforward:

CREATE TABLE memory_vectors (
    id BIGSERIAL PRIMARY KEY,
    agent_id UUID NOT NULL,
    chunk_id UUID NOT NULL,
    embedding vector(1536),
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT now()
);

An HNSW index was created with default parameters:

CREATE INDEX idx_memory_vectors_embedding ON memory_vectors
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);

The Crash Sequence

As the table grew to millions of rows, an index build started consuming all available memory. The out-of-memory (OOM) killer terminated the Postgres process. The CREATE INDEX command was issued via a migration script running on the primary, which also served read traffic. The result was downtime, corrupted connections, and a half-built index.

What Actually Broke

pgvector's HNSW index builds in two phases:

1. Layer assignment: For each vector, determine which layer(s) it belongs to. This is O(N) and memory-bound because it loads all vectors into memory.
2. Graph construction: For each layer, connect each node to its nearest neighbors. This is O(N log N) and CPU-bound, but also memory-intensive due to intermediate data structures.

At large scale, each vector of 1536 dimensions occupies several kilobytes. With millions of vectors, the total memory for the vectors alone is substantial. Additionally, pgvector builds internal data structures: a list of all vectors (another copy), a graph adjacency list per layer, and a priority queue for ef_construction. Total memory usage can balloon far beyond the vector data. Even if the instance has a large amount of RAM, improper configuration can lead to swapping and OOM.

Root Cause #1: Shared Buffers Contention

A large shared_buffers setting (e.g., a quarter of RAM) causes the OS to see dirty cache and start swapping when the index build allocates memory. The index build bypasses shared_buffers for its own allocations, but the existing data is cached there. The ensuing swap thrashing leads to OOM.

Fix: Reduce shared_buffers to a smaller fraction of RAM and rely on OS page cache for the rest. Also, move the index build to a replica with no other connections.

Root Cause #2: Default ef_construction = 200

HNSW's ef_construction controls the size of the dynamic candidate list during graph construction. At 200, each insertion evaluates up to 200 candidates. For millions of vectors, that results in billions of distance calculations. Each distance calculation for 1536 dimensions involves a dot product of two vectors—several kilobytes per pair. The intermediate results are stored in a priority queue that can grow to ef_construction entries per layer. With m = 16 and ef_construction = 200, the memory for the priority queue alone, though not enormous per insertion, leads to cumulative allocation patterns that cause fragmentation and high watermark issues.

Fix: Reduce ef_construction to a lower value (e.g., 64) during building. After the index is built, set ef_search to a higher value for queries.

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

Root Cause #3: The Index Build on Primary

Building a large index on a primary serving live traffic is dangerous. The index build holds a ShareLock on the table, blocking writes. Using plain CREATE INDEX (not concurrent) blocks all inserts and updates for the duration, causing a backlog of writes that fills the WAL and triggers checkpoints, increasing I/O.

Fix: Use CREATE INDEX CONCURRENTLY on a replica, then promote. Alternatively, build on the primary with CONCURRENTLY and accept a longer build time.

The Rebuild

A dedicated index-building environment was set up:

• Fresh Postgres instance with fast local storage.
• shared_buffers set to a modest fraction of RAM.
• maintenance_work_mem set appropriately for index builds.
• max_parallel_workers and effective_io_concurrency tuned.

Vectors were loaded via COPY with FREEZE ON to minimize WAL logging. Then the index was built with reduced ef_construction:

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

The build completed without OOM, using a large fraction of available memory but no swapping.

Lessons Learned

1. Monitor index builds: Use progress reporting and system memory alerts to catch runaway builds.
2. Benchmark before scaling: Test indexing at target scale in a staging environment.
3. Know your index internals: HNSW's ef_construction is a memory/accuracy trade-off. For bulk loads, lower it.
4. Use CONCURRENTLY: Even on replicas, to prevent blocking writes.
5. Consider partitioning: Partition the table by a shard key (e.g., agent_id) to bound per-partition index build memory.

CREATE TABLE memory_vectors_agent_001 PARTITION OF memory_vectors
FOR VALUES IN ('agent-uuid-001');
-- repeat for each partition

Production State

With partitioning and careful index build practices, the system now handles a large number of vectors across many partitions. Index builds happen on a rolling basis on a standby, then switch. Query performance remains good with appropriate ef_search. The crash taught that PostgreSQL with pgvector is a solid vector store, but the index build must be treated as a heavy batch job—respect memory, reduce ef_construction, and never build on the primary.