10 Million Documents Broke My RAG Pipeline: The Hard Truth About Scaling Vector Search

Every RAG tutorial starts the same way: chunk your documents, embed them, throw them into a vector database, and boom, you’ve got an AI that answers questions about your data. Works great on 50 PDFs. Falls apart somewhere around document 50,001.

The gap between a demo and a production system handling 10 million documents isn’t just about buying a bigger server. It’s about architectural decisions that look trivial at 1,000 documents and become existential at 10 million. Chunking strategies that seemed fine suddenly produce garbage. Indexes that worked beautifully start timing out. And that vector database you picked because the tutorial said so? It’s now your biggest operational headache.

This is the system design deep-dive I wish I’d had before building my first large-scale RAG pipeline. We’ll cover the architecture, the indexing strategies, the retrieval trade-offs, and the hard numbers that separate a demo from a deployment.

The 10 Million Document Problem: Why Naive RAG Breaks

Let’s be precise about what “10 million documents” actually means. If each document averages 2,000 tokens and you chunk at 500 tokens with 75-token overlap, you’re looking at roughly 40 million chunks. Each chunk generates a 1536-dimensional vector. That’s about 240 GB of raw vector data before you add any indexes.

The naive approach, throw everything into a vector database, run cosine similarity, hope for the best, collapses at this scale for three reasons:

1. Index build times go exponential. Building an HNSW index on 40 million vectors isn’t a background job, it’s an overnight operation that consumes every available CPU core.
2. Memory pressure crushes performance. An HNSW index for 40 million 1536-dim vectors needs roughly 30-40 GB of RAM just for the graph structure. If it doesn’t fit in memory, your query latency goes from milliseconds to seconds.
3. Metadata filtering becomes the bottleneck. When you need to filter by tenant, document type, or date range before the vector search, the naive approach of post-filtering means you’re searching the entire index and then discarding results. At 10 million documents, that’s catastrophic.

The rest of this post is about how to actually solve these problems.

The Architecture: What Changes at 10 Million

The architecture for 10 million documents isn’t fundamentally different from the architecture for 10,000, but every component needs to be rethought for throughput, memory, and operational complexity. Here’s the high-level design:

The Ingestion Pipeline: Idempotency Is Not Optional

At 10 million documents, you will re-ingest. Something will fail halfway through. A document will be updated. Your pipeline must handle all of these without re-embedding the entire corpus.

The key insight from production systems is the content_sha256 pattern. Hash every document before processing. Store the hash. On re-ingestion, compare hashes and skip unchanged documents entirely. This turns a full re-index from a multi-day disaster into a 5-minute diff operation.

The chunking strategy also needs to be more sophisticated than the naive recursive split. At scale, you need to handle heterogeneous document types, Markdown, PDFs, web pages, source code, each with different optimal chunk boundaries. The default of 512 tokens with 64-token overlap works for prose, but code benefits from AST-aware chunking, and legal documents benefit from sentence-window retrieval.

The Embedding Bottleneck

Embedding 10 million documents isn’t a one-time cost. If each document averages 2,000 tokens and you chunk at 500 tokens, you’re looking at roughly 40 million chunks. At OpenAI’s text-embedding-3-small pricing ($0.02 per 1M tokens), the initial embedding pass costs about $1,600. That’s manageable. The problem is when you need to change your embedding model.

Swapping from text-embedding-3-small to bge-m3 means re-encoding the entire corpus. At 40 million chunks, that’s not a weekend job, it’s a multi-day operation that consumes significant API throughput or GPU time. This is why the choice of embedding model is one of the most consequential decisions you’ll make. The Matryoshka Representation Learning (MRL) trick, where the first N dimensions of a vector are themselves a valid embedding, gives you some flexibility. You can store 1536 dimensions but index only 512, getting a 3x speedup without re-embedding. But swapping models entirely? That’s a full re-index.

The Indexing Showdown: HNSW vs. IVFFlat vs. Everything Else

The index is where the theoretical meets the practical. At 10 million documents, your index choice determines whether queries take 5 milliseconds or 5 seconds.

HNSW is the 2026 default. The numbers don’t lie: higher recall, lower latency, and the ability to build the index before you have any data, which matters enormously for CI/CD pipelines and blue/green deployments. IVFFlat only makes sense for insert-heavy workloads above ~100K writes per hour, and even there, modern HNSW implementations are usually competitive.

The real question isn’t which index to use. It’s whether your index fits in memory. An HNSW index for 40 million 1536-dim vectors needs roughly 30-40 GB of RAM. If it doesn’t fit, your query latency goes from milliseconds to seconds. This is where quantization becomes your best friend.

The Quantization Trick: Half the Memory, Same Recall

pgvector 0.7+ introduced halfvec, storing each dimension as a 16-bit float instead of 32-bit. This cuts storage and index memory in half with essentially no recall hit for most embedding models. On a 1M-chunk, 1536-dim index, that’s the difference between 6 GB and 3 GB.

-- Add halfvec column alongside the main one
ALTER TABLE chunks ADD COLUMN embedding_half halfvec(1536)
    GENERATED ALWAYS AS (embedding::halfvec(1536)) STORED;

CREATE INDEX chunks_embedding_half_hnsw
    ON chunks USING hnsw (embedding_half halfvec_cosine_ops);

-- Query the halfvec index for 3x less memory pressure
SELECT id, content
FROM chunks
WHERE tenant_id = $2
ORDER BY embedding_half <=> ($1::halfvec(1536))
LIMIT 5;

The recall hit from halfvec quantization is negligible for most embedding models. You’re trading a 0.5-1% drop in recall for a 50% reduction in memory. At 10 million documents, that trade is a no-brainer.

The Real Bottleneck: Metadata Filtering

Here’s the problem that every RAG tutorial ignores: real-world retrieval isn’t just about finding semantically similar chunks. It’s about finding semantically similar chunks that belong to the right tenant, are from the right document type, and were created within the right date range.

In a dedicated vector database like Pinecone, metadata filtering happens as a pre-filter or post-filter step. Pre-filtering means you scan the metadata index first, then search only the matching vectors. Post-filtering means you search all vectors, then discard those that don’t match. Both approaches have problems at scale.

Pre-filtering with a restrictive filter can return too few candidates for the ANN search to work well. Post-filtering means you’re searching 40 million vectors and discarding 39 million of them, a colossal waste of compute.

This is where pgvector’s ability to do transactional joins becomes the killer feature. Because vectors live alongside your relational data, you can filter by tenant_id, document_type, or created_at in the same query that does the vector search. The HNSW index respects the WHERE clause, so you’re only searching the relevant subset of vectors.

-- $1 = query embedding, $2 = tenant_id, $3 = min_date
SELECT c.id, c.content, d.title, c.embedding <=> $1 AS distance
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE c.tenant_id = $2
  AND d.created_at > $3
ORDER BY c.embedding <=> $1
LIMIT 5;

In Pinecone, this would require two network hops: one to fetch metadata, one to search vectors. In pgvector, it’s one query, one round-trip, one transaction. At 10 million documents, saving that extra network hop is the difference between a 50ms response and a 500ms response.

The Hybrid Search Imperative

Pure vector search is great at semantic similarity. It’s terrible at exact matches. If your user asks for “SKU-404” or “version 2.3.1”, vector search will return chunks about “product identifiers” and “software releases”, semantically related, but not the specific answer.

This is why hybrid search, combining vector similarity with traditional keyword scoring, isn’t optional at scale. It’s mandatory.

The standard approach uses Reciprocal Rank Fusion (RRF) to combine the results from a vector index and a BM25 inverted index. Postgres makes this surprisingly elegant with its built-in tsvector full-text search:

WITH
vector_hits AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS rank
    FROM chunks
    WHERE tenant_id = $2
    ORDER BY embedding <=> $1
    LIMIT 50
),
keyword_hits AS (
    SELECT id, ROW_NUMBER() OVER
        (ORDER BY ts_rank_cd(content_tsv, plainto_tsquery('english', $3)) DESC) AS rank
    FROM chunks
    WHERE tenant_id = $2
      AND content_tsv @@ plainto_tsquery('english', $3)
    LIMIT 50
)
SELECT c.id, c.content, d.title, d.source_uri,
       COALESCE(1.0 / (60 + v.rank), 0) + COALESCE(1.0 / (60 + k.rank), 0) AS rrf_score
FROM chunks c
LEFT JOIN vector_hits  v ON v.id = c.id
LEFT JOIN keyword_hits k ON k.id = c.id
JOIN documents d ON d.id = c.document_id
WHERE v.id IS NOT NULL OR k.id IS NOT NULL
ORDER BY rrf_score DESC
LIMIT 5;

This single query replaces what would require two separate systems and a fusion service in a dedicated vector DB architecture. The tsvector column is generated automatically, so it’s always in sync with the content. No separate indexing pipeline to maintain.

The Cost Reality Check

Let’s talk about what this actually costs. The conventional wisdom says you need a dedicated vector database for production RAG. The numbers tell a different story.

The dedicated vector DB argument only really wins above 50M vectors or when you need globally distributed, multi-region HNSW. Below that, pgvector on managed Postgres is cheaper, simpler, and, critically, keeps your data on European soil if that matters for compliance.

The GDPR Elephant in the Room

If you’re building a RAG system for a European company, the GDPR implications of sending customer content to OpenAI for embedding are not theoretical. Sending content to OpenAI for embedding is a data transfer to a US processor. You need OpenAI’s DPA, Standard Contractual Clauses, a Transfer Impact Assessment, and documentation in your Record of Processing Activities.

The alternative is self-hosting embeddings on a European VPS. Models like bge-m3 or nomic-embed-text run perfectly well on a small VPS via Ollama or Hugging Face’s text-embeddings-inference. The quality gap between open-weight models and OpenAI’s offerings has narrowed dramatically. For GDPR-strict workloads, this is the only defensible path.

The Long-Context vs. RAG Debate: Settled?

The million-token context window has sparked a genuine debate: if your model can see the entire document at once, why bother with retrieval? The answer is cost and control.

At Gemini 1.5 Pro’s pricing, answering one question over a 500-page document with full-context costs roughly 100x what the equivalent RAG query costs. Long context also doesn’t solve access control (the model sees everything you pass in), doesn’t solve corpus updates (the cache invalidates on every change), and doesn’t solve citation (the model attends globally but doesn’t reliably attribute).

The settled position by late 2025: RAG and long context are complementary, not competitive. Long context absorbs more chunks per query (k = 50-200 instead of k = 3-10), which raises retrieval recall ceilings. RAG keeps cost manageable and citation tractable. Most production systems in 2026 retrieve aggressively, pass dozens of chunks rather than five, and rely on the LLM’s long-context attention to pick out the salient passages.

The Evaluation Trap

If you cannot measure retrieval Recall@k separately from end-to-end answer quality, you cannot tell whether a bad answer is the retriever’s fault or the generator’s fault. Build the labelled (query, relevant chunk) eval set before tuning anything else, every later decision depends on it.

The most common failure mode in production RAG systems isn’t bad generation. It’s bad retrieval. The LLM is doing its job, it’s synthesizing an answer from the context it was given. The problem is that the context doesn’t contain the relevant information. But because the LLM is fluent and confident, the output looks plausible. This is why evaluation frameworks like Ragas, TruLens, and DeepEval separate retrieval metrics (Recall@k, nDCG) from generation metrics (faithfulness, answer relevance).

When pgvector Isn’t Enough

Let’s be honest about the limits. pgvector on managed Postgres handles up to about 10 million 1536-dimensional vectors comfortably with HNSW and halfvec quantization. Many teams run tens of millions with careful tuning and partial indexes. The rule of thumb: if your HNSW index fits in RAM, you’re fine.

But there are three scenarios where a dedicated vector DB becomes the right call:

1. You have >50M vectors and need distributed HNSW. At this scale, the index doesn’t fit on a single node, and you need the distributed architecture that Pinecone, Qdrant, or Weaviate provide.
2. You need globally replicated vector search with low latency on every continent. Multi-region HNSW is a hard problem that dedicated vector DBs have solved better than Postgres.
3. Your team explicitly does not want to run Postgres. Sometimes the operational simplicity of a managed vector DB outweighs the cost and feature advantages of pgvector.

Below those bars, pgvector on managed Postgres is cheaper and simpler. The numbers are stark: a full production RAG stack on European infrastructure costs about €36/month. The equivalent with dedicated vector DBs and US-hosted services runs $112+. And that’s before you factor in the GDPR compliance overhead.

The GDPR Trap Most Teams Miss

If you’re building a RAG system for a European company, the GDPR implications of sending customer content to OpenAI for embedding are not a legal edge case, they’re a core architectural constraint.

OpenAI’s API does not train on API inputs by default (since March 2023), and they offer an enterprise DPA with Standard Contractual Clauses. But you still need a Transfer Impact Assessment and a documented legal basis. For truly strict setups, health, legal, public sector, self-hosting embeddings on a European VPS is the only defensible path.

The beauty of pgvector on European-managed Postgres is that your vectors never leave the EU. Only the ephemeral embedding API call leaves, and even that can be kept in-EU with OpenAI’s EU data residency options or by switching to self-hosted embeddings. Your database, your vectors, your data center, all in Germany.

The Evaluation Discipline That Separates Pros from Amateurs

The single most important thing you can do for your RAG system is build a labelled evaluation set before you tune anything else. You need at least 100 (query, relevant chunk, gold answer) examples. Without them, you’re flying blind.

The most common failure mode in production RAG systems isn’t bad generation, it’s bad retrieval. The LLM is doing its job, synthesizing an answer from the context it was given. The problem is that the context doesn’t contain the relevant information. But because the LLM is fluent and confident, the output looks plausible. This is why you need to measure retrieval Recall@k separately from answer faithfulness.

Frameworks like Ragas, TruLens, and DeepEval make this tractable. Build the eval set, measure both dimensions, and you’ll know exactly where to invest your optimization effort. Without it, you’re guessing.

The Bottom Line

Building a RAG system for 10 million documents is absolutely feasible on a single managed Postgres instance with pgvector. The key decisions are:

1. Use HNSW, not IVFFlat. Higher recall, lower latency, buildable on empty tables.
2. Quantize with halfvec. Half the memory, negligible recall loss.
3. Denormalize tenant IDs onto every chunk for fast filtering.
4. Build hybrid search from day one. Pure vector search misses exact matches.
5. Measure retrieval and generation separately. You can’t fix what you can’t measure.
6. Consider GDPR from the start. Self-host embeddings if you can’t justify the data transfer.

The dedicated vector database vendors will tell you that Postgres can’t handle this scale. The numbers say otherwise. With HNSW, halfvec quantization, and careful schema design, a single managed Postgres instance handles 10 million documents comfortably. The threshold where a dedicated vector DB becomes necessary is around 25-50 million vectors or heavy multi-region requirements.

Everything below that, pgvector wins. And it does so at roughly one-third the cost, with zero additional operational complexity, and with your data staying exactly where you put it.

For a deeper dive into alternative retrieval backends for RAG at scale, check out our analysis of Elastic Search as a vector store. And if you’re wondering about the limitations of vector-only RAG for complex retrieval, the semantic code graphs approach offers a compelling alternative for code-heavy corpora.

The Bottom Line

Building a RAG system for 10 million documents is absolutely feasible on a single managed Postgres instance. The key is understanding where the bottlenecks actually are, memory pressure from the HNSW index, the cost of metadata filtering, the need for hybrid search, and the operational complexity of keeping everything in sync.

The dedicated vector database vendors will tell you that Postgres can’t handle this scale. The numbers say otherwise. With HNSW, halfvec quantization, and careful schema design, you can serve 10 million documents for under €40/month on European infrastructure. That’s not a compromise. That’s an architectural win.

The real question isn’t whether Postgres can handle 10 million documents. It’s whether your team can resist the siren song of adding another database to the stack.