Oracle pgvector 生产翻车：1万向量后 RAG 质量急剧下降

My RAG system's retrieval quality tanked at 10,000 vectors. Not at 100k, not at 1M. Ten thousand. This wasn't a theoretical scaling limit; it was a production reality on Oracle Autonomous Database with pgvector. We had shipped a multi-agent system for a client, handling customer support queries via Telegram and WhatsApp, routing to specialized Groq-powered agents. The core knowledge base, critical for accurate responses, lived in pgvector. The initial 5,000 vectors worked beautifully. Then we doubled the data, and the system started hallucinating specific details, citing irrelevant documents, and generally failing to provide the precise answers our agents needed.

The Initial Setup: text-embedding-ada-002 and IVFFlat

We started with text-embedding-ada-002 for its cost-effectiveness and decent performance on general knowledge. Each embedding was 1536 dimensions. Our Oracle Autonomous Database instance (shared infrastructure, OCPU count scaled on demand) runs PostgreSQL 14.8. pgvector was installed as an extension. We chose IVFFlat with lists = 100 as our index type, primarily because it was simpler to configure and seemed sufficient for our projected data size of "tens of thousands." Our data consisted of product manuals, internal FAQs, and customer interaction transcripts, chunked to ~250 tokens.

The ingestion pipeline was straightforward:

Embed with OpenAI API.

Insert into pgvector table:

CREATE TABLE knowledge_base (
  id UUID PRIMARY KEY,
  content TEXT,
  embedding VECTOR(1536)
);

Create index:

CREATE INDEX ON knowledge_base USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

For retrieval, we used ORDER BY embedding <-> query_embedding LIMIT 5. This worked. Our agents were pulling relevant context, and our human validation step showed an average precision@5 of 0.85 for the first 5,000 vectors.

The 10,000 Vector Cliff: Why IVFFlat Failed

At 10,000 vectors, our precision@5 dropped to 0.55. The agents started pulling documents that were semantically related but not specifically relevant to the user's query. For example, a query about "refund policy for digital goods" would retrieve documents about "general refund process" and "digital product activation," missing the specific clause about non-refundable digital items.

The root cause was IVFFlat. While lists = 100 seemed reasonable for 10,000 vectors, it wasn't. IVFFlat works by partitioning the vector space into lists clusters. When a query comes in, it finds the nearest n_probe clusters and searches only within those. My n_probe was implicitly 1 (the default for pgvector's IVFFlat search if not specified, or effectively very low if not tuned). With 10,000 vectors and 100 lists, each list contained an average of 100 vectors. This is too many for efficient and accurate nearest neighbor search within a single list, especially in high dimensions. The initial clustering itself might not have been optimal for our specific data distribution, leading to relevant vectors being placed in distant lists.

The solution wasn't to just increase n_probe. Increasing n_probe improves recall but significantly increases query latency. For our real-time agent interactions, a 500ms retrieval latency was already pushing it. Increasing n_probe to, say, 10, would have meant searching 10% of the data, which for 10,000 vectors is 1,000 vectors. This would have pushed latency beyond acceptable limits for a synchronous agent call.

The HNSW Migration and all-MiniLM-L6-v2 Experiment

The immediate fix was to switch to HNSW. HNSW (Hierarchical Navigable Small World) builds a graph structure that allows for much more efficient approximate nearest neighbor search, especially in higher dimensions and larger datasets.

The migration involved:

Dropping the IVFFlat index:

DROP INDEX knowledge_base_embedding_idx;

Creating the HNSW index:

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

• m = 16: Controls the number of neighbors each node connects to. Higher m means denser graph, better recall, slower build, more memory.
• ef_construction = 64: Controls the search scope during index build. Higher ef_construction means better quality index, slower build.

After rebuilding the index (which took about 3 minutes for 10,000 vectors on our Oracle instance), retrieval latency for LIMIT 5 queries dropped from ~400ms (with IVFFlat and its degraded recall) to ~150ms, and precision@5 jumped back to 0.88. This was a significant win.

However, the cost of text-embedding-ada-002 was becoming a concern. At $0.0001 / 1K tokens, and with daily ingestion of new customer interactions, it was adding up. We decided to experiment with a smaller, open-source model: all-MiniLM-L6-v2 (384 dimensions).

1. Re-embed all 10,000 vectors with all-MiniLM-L6-v2. This was a batch job, run on a local machine, taking about 2 hours.
2. Alter table column: ALTER TABLE knowledge_base ALTER COLUMN embedding TYPE VECTOR(384);
3. Rebuild HNSW index:

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

The results were mixed. Retrieval latency dropped further to ~80ms due to the smaller vector size. However, precision@5 dropped to 0.70. While all-MiniLM-L6-v2 is fast and free to run locally, its semantic understanding for our specific domain (technical product support, nuanced policy details) was not on par with ada-002. The agents started making subtle errors again, misinterpreting user intent due to less precise context.

The Current State: text-embedding-3-small and HNSW

We reverted to an OpenAI model, but opted for text-embedding-3-small. This model offers 1536 dimensions (or configurable down to 256) at a significantly lower cost: $0.00002 / 1K tokens, a 5x reduction from ada-002.

• Embedding Model: text-embedding-3-small (1536 dimensions). Cost is now manageable.
• Vector Database: pgvector on Oracle Autonomous Database (PostgreSQL 14.8).
• Index: HNSW with m = 16, ef_construction = 64.
• Data Size: Currently at 15,000 vectors, growing by ~500 vectors daily.
• Retrieval Performance: Precision@5 is back to 0.85. Latency for LIMIT 5 is ~120ms.

This configuration provides the best balance of cost, performance, and retrieval quality for our specific RAG needs. The Oracle Autonomous Database handles the scaling of compute and storage seamlessly, which is crucial for a lean operation like AIdeazz with zero VC funding. We pay for what we use, and the PostgreSQL service is robust.

The key takeaway is that pgvector is powerful, but its performance is heavily dependent on the index type and parameters, especially as data grows. Don't assume an IVFFlat index with default parameters will scale beyond a few thousand vectors, even if benchmarks suggest it. Always test with your actual data and query patterns. And for production RAG, the embedding model choice is a critical trade-off between cost, speed, and semantic precision. Don't optimize for cost if it means your agents start hallucinating.

Scaling Beyond 100k Vectors

Our current HNSW setup is projected to handle up to 100,000 vectors with acceptable performance. Beyond that, we anticipate needing to tune HNSW parameters (m, ef_construction, ef_search) more aggressively or consider sharding the knowledge base. Oracle Autonomous Database's PostgreSQL service supports read replicas, which could offload query load, but for a single-node pgvector instance, the index itself becomes the bottleneck.

For truly massive scale (millions of vectors), dedicated vector databases like Qdrant or Pinecone, or even a distributed pgvector setup with TimescaleDB's columnar storage and sharding, would be on the roadmap. But for now, pgvector on Oracle Autonomous DB is a cost-effective and performant solution for our production RAG needs.

Frequently Asked Questions

Q: Why Oracle Autonomous Database for pgvector instead of a dedicated PostgreSQL instance on a VM?

A: Autonomous Database handles patching, backups, and scaling automatically. For a small team with zero ops budget, the managed service cost is offset by the lack of administrative overhead. We pay for OCPU and storage, not for managing the underlying infrastructure.

Q: What was the exact query latency for IVFFlat at 10,000 vectors before switching to HNSW?

A: Average query latency for ORDER BY embedding <-> query_embedding LIMIT 5 was 400ms. This was measured from our application code, including network roundtrip to the Oracle DB instance.

Q: How did you measure precision@5 for your RAG system?

A: We have a human-in-the-loop validation process. For a sample of 100 agent interactions per week, human reviewers assess the top 5 retrieved documents for relevance to the user's query. Precision@5 is the average number of relevant documents in the top 5.

Q: Did you consider other embedding models besides OpenAI and MiniLM?

A: Yes, we briefly tested Cohere Embed v3. Its performance was comparable to ada-002 but at a slightly higher cost at the time. Given our existing OpenAI API integration for LLMs, staying within the ecosystem simplified our tooling.

Q: What's the cost difference between text-embedding-ada-002 and text-embedding-3-small for your current usage?

A: With 15,000 vectors and 500 new vectors daily, plus ~1000 queries daily, ada-002 would cost approximately $150/month. text-embedding-3-small reduced this to about $30/month for embeddings, a significant saving.

— Elena Revicheva · AIdeazz · Portfolio