Why we picked pgvector over Pinecone

The default answer you hear at AI conferences right now is “use Pinecone, or Weaviate, or Qdrant.” Someone says “RAG” and the next five minutes are dedicated vector-database pitches.

We picked pgvector for an enterprise GenAI platform at a global industrial company. It’s the right call for us, and it would be the right call for most enterprise teams reading this. This is the reasoning I went through.

What the decision looks like

When you’re picking a vector store, you’re balancing three things:

1. Search performance at your scale: will the thing be fast enough, now and a year from now?
2. Operational overhead: how many new systems will your ops team have to learn, monitor, back up, secure?
3. Feature richness: hybrid search, metadata filtering, namespaces, auth

RAG content online tends to optimize aggressively for #1 (performance) and #3 (features) and rarely weights #2 (operational overhead) properly, because the people writing the content aren’t the ones who’ll be paged at 2am when the vector store fails over badly.

My scale calibration

pgvector is “good enough” up to a surprisingly high ceiling. These are rough numbers from production experience:

• Up to ~10M vectors at 1536 dimensions (OpenAI text-embedding-3-small): pgvector with HNSW indexing gives p95 query latency under 50ms on a modest Flexible Server. This covers the vast majority of enterprise knowledge bases.
• 10M-100M vectors: pgvector with HNSW still works, but you’ll be tuning index parameters and probably adding a read replica. Dedicated vector DBs start pulling ahead.
• 100M+ vectors: you should probably be on a dedicated vector store. pgvector can handle it with aggressive partitioning but the operational burden starts to approach what a dedicated tool charges you to solve.

For our platform, the corpus was in the millions of vectors, below the 10M mark, and pgvector handles it without breaking a sweat.

What we got for free by staying in Postgres

“Pick a vector DB” articles tend to undersell this part.

1. Transactional consistency with our relational data

Every document in our RAG pipeline has structured metadata: uploaded_by, uploaded_at, organization, access permissions, retention policy. That metadata already lives in Postgres tables. Having the embedding in the same database means a single query joins document metadata to embedding search results, with transactional consistency.

With a dedicated vector DB, you’re managing two stores, synchronizing IDs between them, handling the case where the write to one succeeded but the other failed. Every enterprise team eventually has a bug from this, and it’s an unnecessary one.

2. Existing backups, auth, observability, SRE playbooks

Our Postgres is already backed up on a schedule, has role-based auth and metrics dashboards in Azure Monitor, and has a runbook our ops team knows.

Adding Pinecone means adding a new system with its own backup story, auth model, observability and runbooks. Multiply that across an enterprise’s portfolio of systems and you see why ops teams resist new vendors.

3. Proper SQL for metadata filtering

A huge chunk of RAG quality comes from metadata pre-filtering before similarity search. “Find similar documents, but only where org_id = X and uploaded_at > Y and document_type in ('policy', 'procedure').”

In pgvector you write this in SQL and the query planner handles it:

SELECT id, content, embedding <=> $1 AS distance
FROM documents
WHERE org_id = $2
  AND uploaded_at > $3
  AND document_type = ANY($4)
ORDER BY embedding <=> $1
LIMIT 10;

Dedicated vector DBs all offer “metadata filtering”, but the products differ in how expressive and composable it is and in how their indexes behave. SQL handles all of it, and every engineer already knows it.

4. Standard ACID guarantees

When you delete a document for GDPR compliance, you want the embedding to go away in the same transaction. pgvector gives you that trivially. Separate vector DBs require application-level orchestration and eventual consistency guarantees you have to test.

What we’d lose by picking Pinecone (or similar)

Let me be fair to the dedicated vector DBs.

What we’d gain: higher scale ceiling, sometimes lower p99 latency at very large scales, purpose-built APIs, some nice features like namespaces, built-in re-ranking in some products.

What we’d lose:

• A new vendor relationship (procurement, contract, security review)
• A new SLA to monitor
• Data residency questions (is our data crossing borders?)
• Egress costs to push embeddings back and forth
• A new query language / SDK in the app code
• A new failure mode (what’s the user-facing behavior when the vector DB is up but Postgres is down, or vice versa?)
• An annual cost that, at our scale, exceeds the pgvector incremental infra cost by an order of magnitude

The trade is bad at our scale and only starts to make sense at much larger scale.

The decision heuristic I used

Try pgvector first if:

• You’re already running Postgres
• Your corpus is <50M vectors
• Your team is not specialized in vector databases
• You value operational simplicity over squeezing the last 10% of performance
• You want metadata filtering to be powerful and familiar

Pick a dedicated vector DB if:

• Your corpus is >100M vectors (be honest and count the vectors you have today)
• You need features pgvector doesn’t have (specific index types, built-in re-ranking, multi-tenancy with isolated namespaces at scale)
• You have an ops team comfortable running a new system
• Your latency budget can’t absorb Postgres’s additional overhead

Most enterprise teams fall in the first bucket and should stick with the Postgres they already run.

Explaining the choice to stakeholders

Picking an exciting new tool is a well-trodden career path, and arguing for the database you already run is a harder conversation with stakeholders who want the roadmap to sound innovative.

My case for architectural restraint is that our platform ships to production because we didn’t take on complexity we didn’t need. Every team I’ve seen struggle with enterprise GenAI has a similar story of architectural bloat, with too many systems, integrations and vendors and no clear picture of what’s in production.

pgvector is unexciting and reliable, and for RAG at enterprise scale that’s almost always the right trade.

---

If you’re architecting RAG and suspect you’ve over-engineered it, let’s talk. I do architecture reviews and will tell you where you have more infrastructure than you need.