pgvector vs Pinecone: which vector store, and when

The default answer is pgvector, and it stays the right answer far longer than most comparisons imply. If you already run PostgreSQL, pgvector adds vector search to a database you are already backing up, monitoring and paying for.

That is not a claim that Pinecone is worse. It is a claim about sequencing: adopting a second datastore on day one buys operational overhead before you know whether you need the capability it brings.

The comparison

The third and fourth rows are the ones people underrate. In a RAG system you almost always need to filter by identity before retrieving — this user may see these documents. With pgvector that is a WHERE clause in the same transaction as the rest of your query. With a separate vector store it is a metadata filter in one system joined to permissions in another, and keeping those two in agreement is a real source of bugs, some of which leak data.

Making pgvector fast

Most "pgvector does not scale" stories are missing an index. A sequential scan over a few hundred thousand embeddings is slow, and that is a configuration problem rather than a property of the extension.

• Build an HNSW index. It is slower to build and larger than IVFFlat, and it gives better recall at speed for most workloads.
• Match the operator class to your distance metric. An index built for L2 does not serve cosine queries, and the planner will quietly fall back to a scan.
• Reduce dimensions if your model supports it. Storage and search time scale with dimensionality, and shorter embeddings are often no worse for retrieval.
• Check the plan with EXPLAIN. Postgres index documentation applies here like anywhere else — if you see a sequential scan, that is your answer.
• Keep vectors in their own table, referenced by id, so a wide content row does not get dragged through every search.

What should actually make you move

Not volume alone. These are the specific conditions where a managed vector database starts to earn its cost:

1. You are past roughly ten million vectors and index rebuilds have become an operational event you have to plan around.
2. Query volume is high enough that vector search competes with transactional load on the same instance, and separating them is cheaper than scaling Postgres.
3. You need index updates visible in milliseconds at high write rates — a live feed rather than a document corpus.
4. You have no one to own Postgres tuning, and a managed service is genuinely cheaper than the hire.

That last one is a real reason and gets dismissed too readily. Choosing a managed service because you do not want to operate the alternative is a legitimate engineering decision, not a failure of nerve.

The migration is not the scary part

The reason to start with pgvector is that moving later is cheap if you build for it. Keep retrieval behind one interface — a function that takes a query and filters and returns passages with ids — and the store becomes an implementation detail.

Re-embedding is the part people fear, and it is usually a few dollars and an afternoon. Embedding models are far cheaper than generation models, which is why RAG costs are dominated by inference rather than by storage.

What we do

We start every RAG build on pgvector unless there is a concrete reason from the list above, and in practice most products never hit one. When a client arrives already running a managed vector database we usually leave it — migrating a working system to save a modest bill is rarely the highest-value work available.

The decision that actually affects answer quality is not where the vectors live. It is what you retrieve and how you evaluate it, which is where the engineering time should go.

Frequently asked questions

References

1. pgvector — GitHub
2. Pinecone — Pinecone
3. Indexes — PostgreSQL Documentation
4. Efficient and robust approximate nearest neighbor search using HNSW graphs — arXiv
5. Embeddings guide — OpenAI