A vector database comparison matters once you ship embeddings - not when you paste a demo notebook into production.
Vector stores retrieve by similarity: you embed text (or images), store the vectors, then query "nearest neighbors" with metadata filters. That underpins production RAG architecture, semantic search, and recommendations. The wrong store choice rarely fails on day one; it fails when filters, tenancy, reindexing, and cost meet real traffic.
This guide compares pgvector, Pinecone, and the usual managed/open alternatives so you can pick a default and know when to switch.
What a Vector Database Actually Does
At minimum you need:
- Insert - store embedding + payload (chunk text,
doc_id, ACL, locale) - ANN search - approximate nearest neighbor (HNSW, IVF, etc.)
- Filters - tenant, product, time range, permission tags
- Ops - backups, capacity, reindex after model change
"Vector database" can mean a dedicated product (Pinecone, Weaviate, Qdrant, Milvus) or an extension on an engine you already run (pgvector on PostgreSQL). Same job; different ops and scale curves.
pgvector vs Pinecone vs Others
| pgvector (Postgres) | Pinecone | Weaviate / Qdrant / Milvus | |
|---|---|---|---|
| Best fit | Early RAG, shared Postgres ops | Managed scale, low ops burden | Self-host or hybrid control |
| Filters | SQL + vector in one place | Strong metadata filtering | Strong; varies by product |
| Hybrid keyword | DIY (BM25 elsewhere) or extensions | Often app-side hybrid | Built-in hybrid on several |
| Ops | You already run Postgres | Fully managed | Cluster + upgrades (or cloud tier) |
| Cost shape | Disk/CPU on your DB | Usage + pods/indexes | Infra or cloud bill |
| Lock-in | Low (SQL + dump) | Higher (API/index shape) | Medium |
Postgres vs Mongo still decides your OLTP core - see PostgreSQL vs MongoDB. For vectors, ask: do we want another system of record, or an index next to the data we already trust?
When pgvector Is the Right Default
Choose pgvector when:
- You already run Postgres (RDS/Aurora/Cloud SQL) for the product
- Corpus is modest (tens of thousands to low millions of chunks - validate with your dims and QPS)
- You need joins: users ↔ docs ↔ ACLs in one transaction story
- Team size favors fewer vendors
pgvector shines for MVPs and many B2B RAG apps: chunk rows live beside documents and permissions. Hybrid search is usually app-orchestrated (SQL full-text + vector) unless you add more pieces.
Watch connection pooling, index build time after big re-embeds, and vacuum/bloat like any serious Postgres workload.
When Pinecone (or Similar Managed) Wins
Choose a managed vector service when:
- Query volume or recall SLOs outgrow comfortable Postgres tuning
- You want index ops, replicas, and scaling as a product feature
- Multiple services need a shared vector API without coupling to the OLTP DB
Pinecone-class tools trade control for speed-to-reliable-search. Budget for re-embedding when you change models (dims and space change), and for filter cardinality that matches your tenancy model.
Decision Guide
Ask, in order:
- Do we already operate Postgres well? → Start pgvector unless you have a hard reason not to.
- Are filters + permissions the hard part? → Prefer the store where ACLs are easiest to enforce at query time.
- Is reindex or QPS the pain? → Measure; then consider Pinecone/Qdrant/Weaviate.
- Do we need hybrid keyword + dense on day one? → Check native hybrid vs stitching yourself (often fine early).
Avoid polyglot "Postgres + Pinecone + OpenSearch" on week one. One retrieval path you can eval beats three you cannot explain.
Production Checklist
- Metadata filters enforced for tenant / role on every query
- Embedding model + dim version recorded per index
- Reindex playbook when the embedding model changes
- Eval set for recall@k and empty-retrieval rate (see RAG evals)
- Latency and cost dashboards (p95 search + tokens downstream)
- Backup/restore tested for the vector index and source docs
For napkin token math on the generation side, use Myrafy's AI token estimator.
Common Mistakes
- Picking a vector SaaS before the corpus and ACL model exist
- Storing embeddings without
model_id/ dim metadata - Filtering in the app after retrieve (leaks and wasted recall)
- Re-embedding production on every prompt tweak
- Ignoring hybrid needs for IDs, SKUs, and error codes
FAQ: Vector Databases
Is pgvector "production ready"? Yes for many products, with the same caveats as serious Postgres: indexes, pooling, monitoring, and load tests on your query mix.
Do I need a vector database if I use OpenAI file search / assistants? Managed RAG APIs can ship a demo fast. Own the index when you need portable data, custom ACLs, or multi-model routing.
pgvector or Pinecone for RAG? Default pgvector if Postgres is already your system of record. Move to Pinecone (or peers) when measured latency, scale, or ops cost says so - not when a slide deck does.
The Bottom Line
Vector databases are infrastructure for similarity + filters. Start with pgvector beside your relational data; graduate to Pinecone or an open stack when scale and ops demand it. Pair the choice with solid chunking and evals - the store cannot fix a weak RAG pipeline.
Designing retrieval for a product or internal assistant? See Myrafy AI and engineering services or contact us for an estimate.