Every RAG (Retrieval-Augmented Generation) implementation needs a vector store, and the market has exploded with options. This post gives an engineer's comparison of pgvector, Oracle Database 23ai, and Pinecone — with focus on latency, recall accuracy, operational complexity, and total cost of ownership for enterprises that already run Oracle or PostgreSQL.

What Makes a Good Vector Store?

Four metrics matter for production RAG: recall@10 (does similarity search return the right chunks?), p99 query latency, write throughput (embedding ingestion speed), and operational overhead (separate service vs. embedded in existing DB).

pgvector: The PostgreSQL Option

pgvector adds a vector type and <-> (L2), <#> (dot), <=> (cosine) operators to Postgres. The ivfflat and hnsw index types enable ANN search:

-- HNSW index creation
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Query
SELECT id, content
FROM   docs
ORDER  BY embedding <=> $1
LIMIT  5;

Recall@10: ~94% at ef_search=100. p99 latency: 8–15ms for 1M vectors on PostgreSQL 16 with 8 vCPUs. Weakness: index lives in shared_buffers — large datasets need careful memory tuning.

Oracle Database 23ai: The Enterprise Option

Oracle's HNSW index is memory-managed by the Buffer Cache and participates in the cost-based optimiser. Key advantage: you can join vector similarity results with relational filters in a single SQL statement with one consistent ACID transaction:

-- Hybrid search: semantic + relational filter
SELECT d.chunk_text, d.doc_date
FROM   doc_chunks d
WHERE  d.tenant_id = :tenant          -- relational filter
ORDER  BY VECTOR_DISTANCE(d.embedding, :qvec, COSINE)
FETCH  FIRST 5 ROWS ONLY;

Recall@10: ~96% with TARGET ACCURACY 97. p99 latency: 5–12ms on ADW medium. Advantage for Oracle shops: zero new infrastructure — vectors, relational data, and JSON all in one DB, one backup, one security model.

Pinecone: The Managed Specialist

Pinecone is purpose-built for vectors. Its serverless tier auto-scales with zero ops overhead. The trade-off: data lives outside your existing database, requiring a separate ETL process to keep vectors in sync, and you pay per query + storage:

import pinecone
pc = pinecone.Pinecone(api_key=API_KEY)
index = pc.Index("enterprise-docs")
results = index.query(vector=query_vec, top_k=5,
                      filter={"tenant": {"$eq": tenant_id}})

Recall@10: ~97%. p99 latency: 20–50ms (network round-trip). Cost: $0.096/1M vectors/month + $2/1M read units. For 10M vectors + 10M queries/month: ~$21/month.

Decision Matrix

Recommendation

If you are an Oracle shop building RAG on top of ERP data — use Oracle 23ai. No new service to operate, vectors sit next to the data they describe, and hybrid relational+vector queries are first-class citizens. For greenfield Python microservices with no existing database dependency, Pinecone's serverless tier removes all operational friction and is worth the per-query cost.