My RAG production system on Oracle Autonomous Database hit a wall at 10,000 vectors. Retrieval quality, previously acceptable, plummeted. The initial setup, a basic pgvector index with IVFFlat, was no longer sufficient. This wasn't a theoretical scaling issue; it was a live system failing to deliver accurate responses to paying users. The cost of a single text-embedding-ada-002 call was $0.0001, but the cost of a bad answer was a lost customer.
We're running multi-agent systems, routing between Groq and Claude, serving Telegram and WhatsApp users. Our knowledge base isn't massive, but it's critical. Each agent needs precise context. When retrieval failed, agents hallucinated or defaulted to general knowledge, which is useless for specific user queries about their orders or our internal processes. This article details the specific changes we made, the numbers we saw, and why IVFFlat failed us, leading to a shift to HNSW and a re-evaluation of our embedding strategy.
The Initial Setup: pgvector and IVFFlat on Oracle Autonomous DB
Our first iteration used pgvector on an Oracle Autonomous Database, specifically the PostgreSQL-compatible service. The setup was straightforward: a table with a vector(1536) column for OpenAI's text-embedding-ada-002 embeddings. We chose IVFFlat with lists = 100 for our initial index. This seemed reasonable for a dataset under 5,000 vectors. Query latency was consistently under 50ms for k=5 nearest neighbors.
The problem wasn't immediate. For the first few thousand vectors, IVFFlat performed adequately. Our RAG accuracy, measured by human evaluation of agent responses, hovered around 85% for relevant queries. This was acceptable for our MVP. However, as our knowledge base grew, incorporating more internal documentation, product specifications, and customer support FAQs, we crossed the 10,000-vector mark. Suddenly, our retrieval quality dropped to below 60%. Agents started pulling irrelevant documents, leading to nonsensical answers. A query for "how to reset my password" might retrieve a document about "billing cycles."
The IVFFlat index, while fast for small datasets, sacrifices recall for speed. As the number of vectors increases, the probability of the true nearest neighbors falling into a different list than the query vector increases significantly. Our lists = 100 was too coarse for 10,000 vectors. Increasing lists would improve recall but degrade query performance, potentially pushing us over our 100ms agent response time budget.
Embedding Model Trade-offs: ada-002 vs. text-embedding-3-small
Before diving deeper into indexing, we re-evaluated our embedding model. We started with text-embedding-ada-002 due to its ubiquity and reasonable cost. Each embedding cost $0.0001 per 1,000 tokens. A typical document chunk of 250 tokens cost $0.000025. With 10,000 vectors, our embedding cost was negligible, perhaps $2.50 total for the entire knowledge base.
However, OpenAI released text-embedding-3-small at a significantly lower cost ($0.00002 per 1,000 tokens) and improved performance. We ran a small experiment: re-embedding 1,000 critical documents with text-embedding-3-small (1536 dimensions, same as ada-002 for direct comparison) and comparing retrieval quality.
The results were subtle but positive. For the same k=5 retrieval, text-embedding-3-small showed a 3-5% improvement in relevant document retrieval, as judged by our internal evaluators. The cost reduction was a bonus, but the primary driver was the slight quality bump. We decided to re-embed our entire knowledge base using text-embedding-3-small. This cost us approximately $0.50 for 10,000 vectors. This change alone didn't fix the 10k vector retrieval issue, but it provided a better foundation.
The Shift to HNSW: Reclaiming Retrieval Quality
The IVFFlat index was the bottleneck. After extensive reading and testing on a staging environment, HNSW (Hierarchical Navigable Small World) emerged as the clear successor for our vector count. HNSW builds a graph structure that allows for more efficient nearest neighbor searches, offering a better balance between recall and speed than IVFFlat for larger datasets.
Creating an HNSW index on pgvector is straightforward:
CREATE INDEX ON documents USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 100);
We chose m = 16 (number of neighbors for graph construction) and ef_construction = 100 (size of dynamic list for construction). These parameters are crucial. Higher m and ef_construction values lead to a more accurate index but take longer to build and consume more memory.
The index build time for 10,000 vectors on our Oracle Autonomous DB instance (2 OCPUs, 16GB RAM) was approximately 15 minutes. This was acceptable for our deployment cycle.
After switching to HNSW, we immediately saw a significant improvement. Query latency for k=5 increased slightly to 60-80ms, but retrieval quality jumped back to 80-82%. This was a crucial win. The HNSW index, with its graph-based approach, was far more effective at finding true nearest neighbors in our 10,000-vector dataset.
We also experimented with ef_search during query time:
SET hnsw.ef_search = 50;
SELECT id, content FROM documents ORDER BY embedding <-> '[...query_vector...]' LIMIT 5;
Increasing ef_search (size of dynamic list for search) from the default of ef_construction (100 in our case) to 150 further improved recall by about 2% but pushed query latency to 100-120ms. We settled on ef_search = 100 to maintain our 100ms response time budget for agents.
Oracle Autonomous DB Specifics and Cost Implications
Running pgvector on Oracle Autonomous Database (PostgreSQL-compatible) has its quirks. While it provides a managed PostgreSQL environment, direct access to OS-level tuning is limited. We rely on Oracle's underlying infrastructure for performance and stability. Our instance is a 2 OCPU, 16GB RAM configuration, costing approximately $0.30/hour. This translates to about $216/month.
The storage for our 10,000 vectors (1536 dimensions, 4 bytes per dimension) is roughly 60MB. This is negligible in terms of storage cost. The primary cost driver is the compute for the database instance itself.
We considered moving to a dedicated VM with PostgreSQL for more control, but the managed nature of Autonomous DB, including automatic backups, patching, and scaling, outweighed the desire for granular control over pgvector parameters. The performance we achieved with HNSW on this setup was sufficient for our current scale.
Future Scaling: Beyond 50,000 Vectors
Our current knowledge base is around 12,000 vectors. We anticipate reaching 50,000 vectors within the next 6-9 months. At that point, we expect HNSW to continue performing well, but we will need to re-evaluate m and ef_construction parameters.
For datasets exceeding 100,000 vectors, we would consider sharding our knowledge base or exploring more specialized vector databases. However, for our current and projected scale, pgvector with HNSW on Oracle Autonomous DB provides a cost-effective and performant solution. The key was understanding the limitations of IVFFlat and making a data-driven decision to switch to a more robust indexing algorithm.
Frequently Asked Questions
Q: Why not use a dedicated vector database like Pinecone or Weaviate?
A: For our current scale (under 50k vectors), pgvector on Oracle Autonomous DB is significantly more cost-effective and simpler to manage within our existing infrastructure. Dedicated vector databases introduce additional operational overhead and cost that we don't need yet.
Q: How do you monitor retrieval quality in production?
A: We use a combination of automated metrics (e.g., cosine similarity distribution of retrieved documents) and human-in-the-loop evaluation. Our agents log the retrieved documents, and a subset of agent responses are reviewed daily by a human to assess relevance and accuracy.
Q: What was the exact performance difference in query latency between IVFFlat and HNSW for 10,000 vectors?
A: With IVFFlat (lists=100), k=5 queries were consistently under 50ms. With HNSW (m=16, ef_construction=100, ef_search=100), k=5 queries were 60-80ms. The slight increase in latency was a worthwhile trade-off for the significant improvement in recall.
Q: Did you consider other embedding models besides OpenAI?
A: Yes, we briefly experimented with open-source models like all-MiniLM-L6-v2 but found their quality insufficient for our specific domain without extensive fine-tuning. The cost-performance ratio of OpenAI's text-embedding-3-small was optimal for our needs, especially given its low price point.
Q: How do you handle document chunking for RAG?
A: We use a fixed-size chunking strategy with overlap. For most documents, we chunk into 250-token segments with a 50-token overlap. For structured data like tables, we use a more sophisticated approach that attempts to keep related rows or sections together.