My RAG production system, built on Oracle Autonomous Database with pgvector, failed at 10,000 vectors. Retrieval quality plummeted from 95% to below 60% for critical queries. This wasn't a theoretical scaling limit; it was a hard, operational wall I hit after six months of shipping production AI agents for clients. The initial setup, using text-embedding-ada-002 and a basic IVFFlat index, worked perfectly for smaller datasets. The problem wasn't pgvector itself, nor Oracle's managed PostgreSQL. The problem was my naive assumptions about index choice and embedding model stability under real-world data growth.
The Initial Setup: Ada-002 and IVFFlat
When I started building multi-agent systems for clients, I needed a robust, cost-effective vector store. Oracle Autonomous Database, with its integrated PostgreSQL and pgvector extension, was a natural fit given my existing Oracle Cloud infrastructure. I provisioned an Always Free Autonomous Database (PostgreSQL flavor) for initial development, then scaled to a 2 OCPU, 16GB RAM instance for production.
My first embedding model was OpenAI's text-embedding-ada-002. It was the industry standard, easy to integrate, and performed well on my initial datasets, which rarely exceeded 2,000 documents. Each document averaged 500 tokens, resulting in roughly 2,000 vectors of 1536 dimensions.
The pgvector index choice was IVFFlat. I configured it with lists = 100 for 1536 dimensions. This seemed reasonable based on common recommendations for datasets under 100,000 vectors. Query latency for ORDER BY embedding <-> ? LIMIT 5 was consistently under 50ms, even with concurrent agent requests. Retrieval accuracy, measured by human evaluation of agent responses, was above 95% for the first few clients. This setup shipped.
The 10,000 Vector Cliff
The problems began when a new client's knowledge base pushed the vector count past 10,000. This client had extensive product documentation, legal agreements, and internal FAQs. The total vector count for their RAG system reached 10,234.
Suddenly, agent responses started hallucinating or providing irrelevant information. My internal monitoring showed a sharp drop in retrieval quality. For queries that previously yielded perfect results, the top 5 retrieved documents now contained 2-3 irrelevant chunks. This wasn't a gradual degradation; it was a distinct drop-off.
My first thought was data quality. I re-chunked, re-embedded, and re-indexed. No change.
Next, I suspected the embedding model. I ran a small test with text-embedding-3-small (512 dimensions) and 3-large (3072 dimensions). While 3-small was faster and cheaper, it didn't solve the retrieval quality issue at 10k vectors. 3-large was too expensive for my current cost structure.
The issue was the IVFFlat index. At 10,000 vectors, with lists = 100, the average number of vectors per list was 100. This is too high for effective nearest neighbor search. IVFFlat works by partitioning the vector space into lists clusters. During a search, it only checks a subset of these lists (controlled by probes). If the relevant vectors are spread across too many lists, or if a list becomes too dense, the search becomes inefficient and inaccurate. My IVFFlat index was effectively performing a near-brute-force search within a large subset of the data, missing true nearest neighbors.
The HNSW Migration and Embedding Model Shift
The solution was to switch to HNSW (Hierarchical Navigable Small World) indexing. HNSW is generally more robust for larger datasets and higher dimensions, offering a better recall-latency tradeoff.
Migrating to HNSW required dropping and recreating the index:
DROP INDEX IF EXISTS idx_document_embeddings;
CREATE INDEX idx_document_embeddings ON document_chunks USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 100);
I chose m = 16 and ef_construction = 100 based on pgvector recommendations for a balance of build time and search quality. m controls the number of neighbors each node connects to, and ef_construction controls the size of the dynamic list during graph construction. Higher values improve recall but increase index build time and memory usage.
After rebuilding the index (which took about 15 minutes for 10k vectors on my 2 OCPU instance), retrieval quality immediately jumped back to over 90%. Query latency remained under 60ms. This confirmed the IVFFlat bottleneck.
At the same time, I started experimenting with open-source embedding models. OpenAI's ada-002 was costing me around $0.0001 per 1K tokens. For a client with 10,000 documents, each 500 tokens, that's 5M tokens. Re-embedding costs were becoming significant during development and data updates.
I integrated bge-small-en-v1.5 (384 dimensions) and nomic-embed-text-v1.5 (768 dimensions) via self-hosted inference endpoints on Oracle Cloud. nomic-embed-text-v1.5 proved to be the sweet spot for my use cases. It offered comparable retrieval quality to ada-002 for my specific domains, but at a fraction of the cost (zero inference cost beyond GPU rental, which I amortize across multiple clients). The reduced dimensionality (768 vs 1536) also meant smaller index sizes and slightly faster queries.
My current embedding pipeline now looks like this:
1. Document ingestion and chunking.
2. Embed with nomic-embed-text-v1.5 (self-hosted on a single NVIDIA A10 GPU on OCI).
3. Store in Oracle Autonomous DB with pgvector and HNSW index.
This setup has scaled to 50,000 vectors for a single client without any degradation in retrieval quality. Query latency remains under 100ms.
Oracle Autonomous DB Performance and Cost
My Oracle Autonomous Database instance (PostgreSQL, 2 OCPU, 16GB RAM) costs approximately $150/month. This includes the database, storage, and managed services. For this price, I get:
- Automatic patching and backups.
- High availability.
- Scalability on demand (though I haven't needed to scale beyond 2 OCPU yet).
- Integrated
pgvector.
Compared to self-hosting PostgreSQL with pgvector on a VM, the managed service saves significant operational overhead. The HNSW index build for 50,000 vectors (768 dimensions) took about 45 minutes on this instance. Subsequent incremental updates are fast.
The key takeaway for practitioners: don't assume your initial index choice will scale. Test it. Monitor retrieval quality, not just latency. And seriously evaluate open-source embedding models for cost efficiency once you have a baseline. The "best" model is the one that meets your quality bar at the lowest operational cost.
Frequently Asked Questions
Q: What ef_search value do you use for HNSW?
A: I typically set ef_search = 40 for my production queries. This value balances recall and latency effectively for my 768-dimensional vectors and dataset sizes up to 50,000. Higher values increase recall but also search time.
Q: How do you monitor retrieval quality in production?
A: I use a combination of automated checks and human feedback. Automated checks involve running a fixed set of "golden queries" against the RAG system and asserting that specific, known relevant document IDs are returned in the top-k. Human feedback comes from agent users flagging irrelevant responses, which triggers a review of the retrieved documents.
Q: Why Oracle Autonomous DB over other managed PostgreSQL services or dedicated vector databases?
A: My primary reason is existing infrastructure and cost optimization within Oracle Cloud. I already run other services and custom inference endpoints on OCI. Autonomous DB offers a robust, managed PostgreSQL with pgvector at a predictable cost, avoiding vendor lock-in to specialized vector databases while leveraging my existing cloud credits and expertise.
Q: Did you consider diskann or other pgvector index types?
A: I evaluated diskann briefly but found HNSW to be sufficient for my current scale and performance requirements. diskann is designed for even larger datasets that exceed memory capacity, which isn't a constraint for me yet. Sticking with HNSW simplified the operational aspects.