Vector Databases 101: Choosing the Right One

Every RAG project starts with the same question: which vector database? Having run Pinecone, OpenSearch, and pgvector in production, here is a practical comparison and a decision framework that avoids both over- and under-engineering.

What You're Actually Choosing

A vector database decision is really five decisions:

1. Scale — how many vectors, what dimensionality, what query rate?
2. Filtering — do you need metadata/ACL filters during search (you do)?
3. Hybrid support — can it combine keyword and vector search natively?
4. Operations — managed or self-hosted? What's your team's ops capacity?
5. Ecosystem — where does your data already live?

The Main Options

pgvector (Postgres) — If your corpus is under ~5M vectors and you already run Postgres, start here. Transactional consistency with your app data, familiar operations, HNSW indexes, and metadata filtering in plain SQL. The boring, correct default for most teams.

OpenSearch / Elasticsearch — The enterprise workhorse. Native BM25 + kNN in one engine makes hybrid retrieval trivial, mature security (document-level permissions), and it scales horizontally. Heavier to operate; ideal when search is a core capability, or on AWS where managed OpenSearch integrates with Bedrock-based stacks.

Pinecone — Fully managed, excellent developer experience, serverless pricing, fast at scale. You trade control and pay a premium; metadata filtering is good but joins with app data are on you. Great when the team is small and time-to-market rules.

Qdrant / Weaviate / Milvus — Strong open-source engines with rich filtering and quantization options. Choose when you need self-hosting with performance beyond pgvector, and you have the ops maturity to run them.

The Decision Framework

< 1M vectors, Postgres in stack        → pgvector
Enterprise search + security + hybrid  → OpenSearch
Small team, no ops budget, need speed  → Pinecone
Self-hosted at large scale             → Qdrant / Milvus

Benchmarks to Run Yourself

Public benchmarks rarely match your workload. Test with your embedding dimensionality, your filter selectivity, and your query concurrency:

• P95 latency at target QPS with filters applied
• Recall@10 vs. exhaustive search (HNSW parameters matter)
• Index build time and memory for a full corpus rebuild
• Cost at 3× current scale

Common Mistakes

• Choosing by benchmark blog posts instead of filter requirements — metadata filtering is where most migrations are forced
• Ignoring index rebuild strategy; you will re-embed everything at least once a year as models improve
• Treating the vector DB as the source of truth — it's a derived index; keep documents and metadata canonical elsewhere

Bottom Line

Start with the simplest engine that satisfies your filtering and hybrid requirements — usually pgvector or OpenSearch. Vector databases are the most replaceable layer of a RAG stack when (and only when) you keep ingestion idempotent and treat indexes as rebuildable artifacts.