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

We run an autonomous agent swarm that ingests millions of documents daily. Each document is chunked, embedded, and stored in Postgres with pgvector. For months, it worked fine at 1M vectors. Then we scaled to 10M and the entire system collapsed. This is the story of that crash, the root causes, and the production-tested fixes that now let us handle 100M+ vectors without drama.

The Setup

Our stack: Postgres 15 on a dedicated R6g.8xlarge (32 vCPU, 256GB RAM), pgvector 0.5.1. The table:

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()
);

We created an HNSW index 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

At roughly 9.5M rows, the index build started consuming all available memory. The OOM killer terminated the Postgres process. We had no monitoring on the index build itself—only on the running query load. The CREATE INDEX command was issued via a migration script that ran on the primary, which also served read traffic. The result: 47 seconds of 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 the distance matrix.

At 10M vectors of 1536 dimensions, each vector is ~6KB (1536 * 4 bytes). 10M * 6KB = 60GB just for the vectors. But pgvector also 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 ballooned to ~110GB. Our instance had 256GB, but Postgres's shared_buffers was set to 64GB, and the OS was using ~20GB for cache. That left ~170GB for the index build—which should have been enough. But we missed two things.

Root Cause #1: Shared Buffers Contention

shared_buffers = 64GB. The index build bypasses shared_buffers for its own internal allocations, but the existing data (the 9.5M rows) is cached in shared_buffers. When the index build starts, it allocates memory from the OS. The OS sees shared_buffers as dirty cache and starts swapping to free memory. Swap on an R6g instance is EBS—slow. The index build thrashed, and the OOM killer struck.

Fix: Set shared_buffers to 16GB (6.25% of RAM) and rely on OS page cache for the rest. Also, we moved the index build to a replica with zero 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 10M vectors, that's 2 billion distance calculations. Each distance calculation for 1536 dimensions is a dot product of two floats—~6KB 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 is: layers (avg 3) * 200 * 6KB ≈ 3.6MB per insertion. That's not huge, but the real killer is the distance matrix caching: pgvector precomputes distances for all pairs in the current layer to speed up graph construction. For a layer with 1M nodes, that's 1M * 1M distances—impossible. But pgvector doesn't do that; it computes distances on the fly. However, the ef_construction queue holds vectors themselves, not just IDs. So each insertion brings ef_construction full vectors into memory. That's 200 * 6KB = 1.2MB per insertion. With 10M insertions, that's 12TB of memory—obviously not all at once, but the cumulative allocation pattern caused fragmentation and high watermark.

Fix: Reduce ef_construction to 64 during building. After the index is built, we can set ef_search to 200 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

Never build a large index on a primary serving live traffic. The index build holds a ShareLock on the table, blocking writes. Our migration script ran CREATE INDEX CONCURRENTLY? No—we used plain CREATE INDEX because we thought it would be faster. It wasn't concurrent, so it blocked all inserts and updates for the duration. That caused a backlog of writes, which filled the WAL, which triggered a checkpoint, which caused more I/O. The combination was fatal.

Fix: Use CREATE INDEX CONCURRENTLY on a replica, then promote. Or build on the primary with CONCURRENTLY and accept the longer build time (2x). We chose the replica approach.

The Rebuild

We set up a dedicated index-building environment:

• Fresh Postgres instance on a R6gd.16xlarge with local NVMe storage (no EBS swap).
• shared_buffers = 16GB.
• maintenance_work_mem = 8GB (for the index build).
• max_parallel_workers = 8.
• effective_io_concurrency = 200.

We loaded the 10M vectors via COPY from a CSV:

\copy memory_vectors (agent_id, chunk_id, embedding, metadata, created_at) FROM '/data/vectors.csv' WITH (FORMAT csv, FREEZE ON);

Note FREEZE ON—this avoids WAL logging for the bulk load, cutting I/O in half.

Then we built the index 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);

This took 2 hours 47 minutes (vs. estimated 1.5 hours with default ef_construction, but we avoided the OOM). Memory usage peaked at 140GB (out of 256GB). No swapping.

Lessons Learned

1. Monitor index builds: We now have a Prometheus alert on pg_stat_progress_create_index that triggers if memory usage exceeds 80% of available RAM.
2. Benchmark before scaling: We should have tested indexing 10M vectors in a staging environment first. The jump from 1M to 10M was too aggressive.
3. Know your index internals: HNSW's ef_construction is a memory/accuracy trade-off. For bulk loads, lower it. For online inserts, keep it moderate.
4. Use CONCURRENTLY: Even on replicas. It prevents blocking writes and allows rollback if the build fails.
5. Consider partitioning: We now partition the memory_vectors table by agent_id. Each partition has at most 2M vectors. Index builds are per-partition, so memory usage is bounded.

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

Current Production State

We now run 120M vectors across 60 partitions. Index builds happen on a rolling basis on a standby, then we switch. Query performance: p99 recall 0.98 with ef_search = 200, latency < 50ms. The agent swarm never notices.

The crash taught us that Postgres with pgvector is a solid autonomous memory store, but you must treat the index build as a heavy batch job, not a simple migration step. Respect the memory, reduce the ef_construction, and never build on the primary.