pgvector on Oracle Autonomous DB: 6 Months, 10k Vectors, and RAG Failure

Originally published on AIdeazz — cross-posted here with canonical link.

My RAG production system, built on Oracle Autonomous Database with pgvector, failed to scale past 10,000 vectors. Retrieval quality plummeted from 92% precision at 5,000 vectors to 68% at 10,000. This wasn't a theoretical benchmark; this was a live multi-agent system, routing customer queries for a shipping logistics client, where a 24% drop in precision meant 24% more manual interventions. The initial promise of pgvector on a managed Oracle service for cost-effective RAG quickly hit a wall, forcing a re-evaluation of embedding models, index choices, and ultimately, my infrastructure strategy.

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

We started with text-embedding-ada-002 (1536 dimensions) because it was the default and "good enough" for initial testing. My Oracle Autonomous Database (ADB) instance, a shared Exadata infrastructure, offered pgvector out of the box. The setup was straightforward:

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content TEXT NOT NULL,
    embedding vector(1536)
);
CREATE INDEX ON documents USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

I chose IVFFlat with lists = 100 based on general recommendations for datasets under 100,000 vectors. My initial dataset was small, around 2,000 internal policy documents, averaging 500 tokens each. Ingesting these, generating embeddings via OpenAI's API, and storing them was simple. Retrieval latency was consistently under 50ms for k=5 nearest neighbors. Our agent, running on Oracle Cloud Infrastructure (OCI) Container Instances, used langchain_pgvector for retrieval, feeding context to a Groq Llama 3 8B agent for initial processing, then escalating to Claude 3 Haiku for complex cases. Precision was high, around 92%, measured by human evaluation of retrieved chunks against ground truth answers.

The 10,000 Vector Cliff: Latency and Precision Degradation

As the client expanded, so did the knowledge base. We added more internal FAQs, shipping manifests, and customer service logs. At approximately 5,000 vectors, retrieval latency started to creep up, hitting 80ms. At 10,000 vectors, it spiked to 250ms, and precision dropped to 68%. This was unacceptable. The agents were hallucinating more often, or simply stating they couldn't find relevant information, leading to increased human intervention.

My pgvector index was the bottleneck. The IVFFlat index, with lists = 100, was struggling. The search_list parameter, which defaults to lists (100 in my case), meant that for each query, it was scanning 100 lists. With 10,000 vectors, each list contained 100 vectors on average. This was still a lot of distance calculations.

I tried increasing lists to 200, then 500.
ALTER INDEX documents_embedding_idx SET (lists = 200);
REINDEX INDEX documents_embedding_idx;

This improved latency slightly (down to 180ms at 10k vectors) but didn't recover precision. In fact, increasing lists too much can degrade precision if the query vector falls into a sparsely populated list. The trade-off was clear: IVFFlat was not robust enough for even this modest scale on my current setup.

Embedding Model Trade-offs: bge-small-en-v1.5 and e5-large-v2

The text-embedding-ada-002 model was costing me $0.0001 per 1K tokens. For 10,000 documents averaging 500 tokens, that's 5 million tokens, or $500 for initial ingestion. Not a huge cost, but every dollar counts when you're bootstrapping with zero VC. More importantly, its performance was now suspect.

I experimented with open-source models: bge-small-en-v1.5 (384 dimensions) and e5-large-v2 (1024 dimensions). I ran these locally on an OCI VM with a single NVIDIA A10 GPU for batch embedding generation, then uploaded to ADB.

• bge-small-en-v1.5 (384D):
  • Pros: Much faster embedding generation (local inference), significantly smaller vector size (384 dimensions vs 1536), reducing storage and potentially improving pgvector performance due to fewer floating-point operations per distance calculation.
  • Cons: Precision dropped to 60% at 10k vectors. The smaller dimensionality simply wasn't capturing enough nuance for my domain-specific documents.
• e5-large-v2 (1024D):
  • Pros: Better precision than bge-small-en-v1.5, reaching 75% at 10k vectors. Still better than ada-002's 68% at that scale. Local inference was manageable.
  • Cons: Larger vector size than bge-small, but still smaller than ada-002. Latency was still an issue, hovering around 150ms.

The e5-large-v2 offered a better balance, but the pgvector performance was still the primary bottleneck. The problem wasn't just the embedding model; it was the retrieval infrastructure.

HNSW: A Necessary Migration

IVFFlat is a good starting point, but for anything beyond trivial scale, HNSW (Hierarchical Navigable Small World) is generally superior for recall and speed. The challenge with HNSW on pgvector is its memory footprint. It's an in-memory index, meaning it consumes RAM directly proportional to the number of vectors and their dimensions. On a shared Oracle Autonomous Database, I have limited control over memory allocation for my specific pgvector index.

I dropped the IVFFlat index and created an HNSW index:

DROP INDEX documents_embedding_idx;
CREATE INDEX ON documents USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 100);

• m = 16: The number of bi-directional links created for each new element during index construction. Higher m means more connections, better recall, but slower construction and larger index size.
• ef_construction = 100: The size of the dynamic list for nearest neighbors during index construction. Higher ef_construction means better quality index, but slower construction.

After rebuilding the index with e5-large-v2 embeddings, the results were immediate:

• Latency: Dropped to 60ms at 10k vectors.
• Precision: Recovered to 88%.

This was a significant improvement, bringing us back to acceptable performance levels. However, the HNSW index size for 10,000 e5-large-v2 vectors (1024 dimensions) was approximately 1.5GB. This is a concern for scaling on a shared ADB instance, where memory resources are not dedicated. I anticipate hitting memory limits or performance degradation as I approach 50,000 vectors, potentially leading to swapping or eviction of the index from memory.

The Oracle Autonomous DB Constraint

Oracle Autonomous Database is fantastic for managed relational workloads. Its auto-scaling, patching, and backup features are invaluable. However, for specialized workloads like pgvector with HNSW, the "autonomous" nature becomes a constraint. I cannot directly control:

1. Dedicated Memory: HNSW thrives on dedicated RAM. On ADB, I'm sharing resources. If other tenants on the same Exadata infrastructure are hammering their databases, my pgvector index might get less memory, leading to performance drops.
2. CPU Cores for Vector Operations: While pgvector can utilize multiple cores for distance calculations, I don't have direct control over how many cores are allocated to my specific pgvector queries on a shared system.
3. Storage Type: While Exadata is fast, I can't specify NVMe SSDs for my pgvector index specifically, which could further reduce latency for index lookups.

These limitations mean that while pgvector on Oracle ADB is convenient for small-scale RAG, it's not a long-term solution for high-performance, high-scale vector search where precise resource control is critical. My current plan is to monitor performance closely as we approach 20,000 vectors. If performance degrades again, the next step will be migrating the vector store to a dedicated OCI VM running PostgreSQL with pgvector or even a specialized vector database like Qdrant or Weaviate, giving me full control over hardware resources. This would introduce additional operational overhead, but it's a necessary trade-off for production stability.

Frequently Asked Questions

Q: Why not use Oracle's own vector capabilities instead of pgvector?
A: Oracle Database 23ai offers native vector capabilities, but it's not yet generally available on Autonomous Database. My production system needed a solution six months ago, and pgvector was the only viable option on ADB at the time.

Q: What was the exact cost of the Oracle Autonomous Database for this workload?
A: My ADB instance was an "Always Free" tier initially, then scaled to 2 OCPU and 1TB storage for $0.35/OCPU-hour. For 10,000 vectors, the database cost was approximately $50/month, primarily for compute, not storage.

Q: Did you consider using a cloud-managed vector database service?
A: Yes, but with zero VC funding, every dollar counts. Services like Pinecone or Zilliz were significantly more expensive for my initial scale (e.g., $70-$100/month for 10k vectors, 1536D) compared to the pgvector on ADB approach. The goal was to leverage existing infrastructure.

Q: How did you measure precision for your RAG system?
A: We used a combination of human evaluation and a small, manually curated test set of 100 queries. For each query, human annotators rated the relevance of the top 5 retrieved chunks on a 3-point scale (relevant, partially relevant, irrelevant). Precision was calculated as (number of relevant chunks) / (total chunks retrieved).

Q: What's your plan if HNSW on ADB hits a wall at 50k vectors?
A: The plan is to migrate the vector store to a dedicated OCI VM running PostgreSQL with pgvector or a specialized vector database like Qdrant. This allows for dedicated memory and CPU allocation, giving full control over HNSW performance parameters and scaling.

— Elena Revicheva · AIdeazz · Portfolio