Overview
Pick pgvector when the team already runs Postgres, the corpus stays under 10 million vectors, and metadata filters dominate the query shape. Pick Pinecone when the workload is pure vector search at scale, nobody on the team wants to operate a database, or the corpus crosses 50 million vectors. Between 10 and 50 million vectors the choice depends on filter complexity and team skill: pgvector with HNSW handles it; Pinecone removes the operational tax for a price. See rag-vector-databases for the full vector-store rundown.
When pgvector wins
pgvector is the right pick when Postgres is already in the stack and the workload is mixed (filters plus vectors).
- Joins: a single
SELECTcan filter by tenant, status, date range, then order by vector distance. Pinecone’s metadata filters work but cannot do server-side joins. - One operational story: backups, replication, point-in-time recovery, and observability use the same tooling as the rest of the app. See postgres.
- Strong consistency: writes and reads go through the same transaction model; no read-after-write lag.
- HNSW indexes ship with pgvector 0.7+; recall above 95 percent at p99 under 50 ms for corpora under 10 million vectors.
- Cheaper at small scale: a Postgres instance you already run adds vector workload for free; Pinecone charges per index from day one.
When Pinecone wins
Pinecone is the right pick when the workload is mostly vectors, the corpus is large, or the team has zero database-operator capacity.
- Serverless billing: no nodes to size, no autovacuum to tune; the API just answers.
- Hybrid search built in: sparse plus dense vectors with a single query, no separate inverted index.
- Recall at scale: Pinecone’s tuned ANN holds up past 100 million vectors where pgvector starts to need careful HNSW tuning and shard planning.
- Namespaces partition tenants cheaply; useful for multi-tenant SaaS where each customer has its own corpus.
- The bill is predictable: usage-based pricing scales with reads and writes, not with the size of an idle index.
The wrong reason to pick Pinecone: “vectors should live in a vector database.” That is a deployment opinion, not a quality argument. The store choice rarely moves recall; chunking and reranking do.
Trade-offs at a glance
| Dimension | pgvector | Pinecone |
|---|---|---|
| Hosting | Self-host or managed Postgres | Fully managed SaaS |
| Index types | HNSW, IVFFlat | HNSW under the hood; not user-tuned |
| Filter performance | Strong with btree on metadata cols | Strong with namespaces and metadata |
| Joins | Yes, full SQL | No |
| Hybrid (sparse plus dense) | Possible with ts_vector plus vector | Native |
| Sweet spot | Under 10M vectors | 10M to 1B+ vectors |
| Cost at 1M vectors | A few dollars on an existing Postgres | Tens of dollars per month |
| Cost at 100M vectors | Self-host hardware or sharded Postgres | Hundreds to low thousands per month |
| Consistency | Strong (Postgres MVCC) | Eventual within a region |
| Embedding update flow | UPDATE ... SET embedding = ... | Upsert via API |
Migration cost
Moving between the two is cheap because the embeddings themselves are the data; the index is rebuilt.
- pgvector to Pinecone: stream rows out of Postgres, upsert into Pinecone in batches of 100. Plan one engineer-day per 10 million vectors, plus a week to rewrite the query layer to call Pinecone instead of SQL.
- Pinecone to pgvector:
fetchvectors out of Pinecone,COPYinto Postgres, build the HNSW index withCREATE INDEX CONCURRENTLY. Same one-day-per-10-million budget. - The hard part is not the data; it is the query rewrite. If the app uses JSON filters on Pinecone, those translate to SQL
WHEREclauses. If the app uses GraphQL on top of either, the rewrite is mostly resolvers.
Recommendation
- Greenfield RAG app, already on Postgres: pgvector. See postgres and embeddings.
- Greenfield RAG app, no Postgres yet: pgvector if the team has any database-operator capacity; Pinecone otherwise.
- Existing Postgres app, fewer than 10 million chunks: pgvector. Do not add a vendor.
- Pure vector workload, no relational data, no database operator on the team: Pinecone.
- Corpus past 50 million vectors with heavy filters: Pinecone or sharded Qdrant. See rag-vector-databases for the Qdrant comparison.
- Strict residency or on-prem requirement: self-hosted pgvector or Qdrant.