Home / Articles

Moving RAG From Pinecone to Postgres pgvector: Six Months Later

Moving RAG from Pinecone to Postgres pgvector, six months on: 9M vectors, real cost, latency and recall numbers, what got worse, and when pgvector is right.

Publisher: REACTAPP.TOP7 min read1690 words

This article is published in English.

Sources: Six months of running RAG on Postgres + pgvector instead of Pinecone

A B2B SaaS team watched its Pinecone invoice cross five figures for a third month in a row and finally did the arithmetic that infrastructure had been dodging. About 9 million vectors were being hosted in Pinecone even though the same data was already written to Postgres for metadata. A second database existed purely to read back what the first one stored.

The move took a long weekend, and that was the easy bit. The six months afterward are what matter: the savings, the surprises nobody mentioned and the two or three spots where Pinecone still looks like the better pick. What follows is production data for anyone weighing pgvector against a dedicated vector database, with no benchmark theater.

The workload

Each customer on the platform gets a private knowledge base, and the RAG pipeline looks roughly like this:

  • Between 8 and 12 million vectors live at any time, spread over around 400 customer tenants.
  • Embeddings come from OpenAI's text-embedding-3-small, at 1536 dimensions.
  • Retrieval takes the top 12 results and reranks them with a Cohere cross-encoder.
  • Search is hybrid, BM25 plus dense vectors, merged by reciprocal rank fusion.
  • Traffic averages about 80 queries per second and peaks near 600 during US business hours.
  • Everything runs in a single AWS region across multiple availability zones.

On Pinecone the setup was the p2.x2 pod class with one replica and two pods for availability. A pod of that class holds roughly 5 million vectors, hence two of them. Add metadata storage on the paid tier and the Standard plan markup. The invoice was fair, since the team really used what it paid for.

Why look at pgvector

Three things pushed the evaluation:

  1. Metadata already lived in Postgres. Every vector carried 12 to 25 payload fields, such as tenant ID, document ID, ACL, source type and timestamps. Searching vectors and then going back to Postgres to apply access rules was slow and wasteful.
  2. Operational surface. Two databases mean two backup strategies, two monitoring stacks, two credential sets and two components that can break at 3am.
  3. Unit economics. Pinecone's per-pod pricing climbs in steps, and passing 10 million vectors would have required a third pod.

The migration in three phases

Every team's migration differs, but the overall shape is worth sharing.

Shadow mode, two weeks. Each embedding was written twice, to Pinecone and to a Postgres 16 instance running pgvector, while reads continued to hit Pinecone. Recall@10 and answer-quality scores from an LLM-as-judge set of 800 real-customer question and answer pairs were compared, and the differences fell within noise.

Internal traffic, two weeks. Staging and the team's own accounts moved over. That exposed pgvector to genuine load patterns: heavy morning sync bursts, spiky customer queries and odd long-tail requests found only in production. Two problems surfaced here, and both come up below.

Production cutover, one weekend. Writes went to both systems for 48 hours as a safety net, then reads switched. The old Pinecone index stayed up for 30 days as a rollback option before being torn down.

The code change was small. What took thought was the infrastructure side: sizing Postgres for a vector workload, configuring PgBouncer and setting up monitoring.

Six months of numbers

These figures come from the team's observability stack.

Database spend fell by roughly 70%, and cost per million vectors dropped by about the same share. The shape matters more than the absolute figure. For a startup, a real cut in monthly cost beats a headline annual number, because monthly operating cost decides runway, hiring pace and how long a wrong technical bet can be tolerated. The other thing recovered was mental overhead. No "vector database" project remains on the roadmap. There's a Postgres cluster.

Latency

Both systems sat behind identical application code, the same reranker and the same client. End-to-end retrieval latency, covering vector search, metadata filter and rerank, was measured at p50, p95 and p99 over a rolling seven-day window in month six.

pgvector is a bit faster in the middle of the distribution, but the real gain sits in the tail. Pinecone's p99 swung between 190ms and 480ms from one week to the next, a side effect of pod rebalancing and compaction running in the background. The p99 on pgvector stays flat and uneventful, exactly what a database should do.

Two caveats apply:

  • The index is HNSW with ef_construction=128 and m=16, built per tenant. That per-tenant strategy is essential to the ACL model.
  • Postgres runs near the app servers, whereas Pinecone was a cross-region network call. In the same region the gap would shrink.

Recall and quality

This part caused the most worry, and the numbers here get the most confidence, since evals run constantly.

Recall slipped by 0.5 percentage points, which sits inside the error bars. It never appeared in user-facing quality, and the LLM-as-judge scores can't be told apart statistically. A bigger gap had been expected.

One change mattered most: setting hnsw.ef_search=200 at query time instead of the default 40. If a single tuning detail is worth keeping, it's that one, because the Postgres default favors indexing speed over retrieval quality.

Reliability

  • Pinecone, in the six months before the move: two degraded-performance incidents with pods stuck scaling, one region-wide latency event and no data loss. Its reliability has been good.
  • pgvector, over the half year following: nothing traced to vector search. Postgres did have two incidents, neither related: pool exhaustion caused by a faulty deploy, and one burst of replica lag. Remaining on Pinecone would have changed neither.

What got better

"Postgres is great" says nothing, so here are specifics.

  1. One query for filtering. Filtering by tenant_id in Pinecone relies on a server-side metadata filter. Anything beyond a simple equality test runs into the 40KB metadata limit or a query timeout. In Postgres it's a WHERE clause, which Postgres handles superbly. A chunk of application code that existed only for pre-filtering was deleted.
  2. ACID on the data. There used to be a soft-consistency window where a document deleted in Postgres stayed searchable in Pinecone for up to five minutes. Now it's one transaction: write the document, write the embedding, update the index, commit. Boring and wonderful.
  3. Schema changes without reindexing. A new metadata field arrived recently as a plain column. Nothing had to be rebuilt, no dual-write period was needed and there was no 18-hour indexing job.
  4. Backups cover vectors. The point-in-time recovery window includes everything, whereas Pinecone needed its own snapshot schedule.
  5. A single connection string. Seriously: through psycopg, new engineers learn one database and not two.

What got worse

The migration wasn't free. The real costs:

  1. Low-selectivity filtered queries are slower. Pinecone's pod design is truly tuned for brute-force vector scans combined with metadata filters. For a pattern like "search 10 million vectors and return 5,000", typical of recommendation workloads, pgvector is the wrong tool. This team runs search and rerank, which is why it worked. A recommendation system would see a regression.
  2. No native index-level multi-tenancy. Pinecone namespaces gave a hard tenant boundary. With pgvector it's enforced in application code. It is enforced, but it's the team's contract and not the database's.
  3. No managed sparse and dense hybrid. BM25 was wired up by hand, which took a week. Not a deal-breaker, but Pinecone would have saved that week.
  4. HNSW rebuilds are unpleasant. Switching the embedding model last month meant a 14-hour rebuild, and Pinecone handles a comparable rebuild at scale faster.
  5. The team becomes a Postgres shop with a vector workload. A small Postgres team now has to understand HNSW parameters, ef_search, quantization and pooling for a workload that behaves nothing like OLTP. This team had the people. Not every team does.
  6. Predictable cost has a limit. Costs stay predictable up to about 30 million vectors, then things get interesting again, with quantization, sharding or a specialized engine entering the picture. At 9 million vectors Postgres fits well. At 90 million, it's an open question that'll be answered next year.

A decision guide

After six months, this is the tree to follow.

Lean toward pgvector when:

  • Postgres is already in use, or something compatible such as Aurora.
  • Production holds under about 20 million vectors, the current sweet spot.
  • The workload is RAG or search with reranking, and not top-k recommendation.
  • A Postgres team exists that can own vector tuning.
  • Complex metadata filters are painful in the current vector database.

Stay with a dedicated vector database when:

  • Volume is past 30 to 50 million vectors and growing fast.
  • The workload is recommendation-style, with high k and low-precision retrieval.
  • Sparse plus dense hybrid is required and building it isn't wanted.
  • There's no strong Postgres operations practice.

Follow this team's path when:

  • The first list fits and the Pinecone bill makes you wince.

What's next

The question that remains is what happens at 50 million vectors. Reaching it means either tuning quantization on pgvector or a serious look at Qdrant and LanceDB. Postgres is adding vector features at speed, such as disk-based HNSW, better quantization and quicker builds, so the gap could narrow further by then.

Public post-mortems at this level of detail are rare, and anyone who has done a similar migration with different numbers, better, worse or just strange, would add value by publishing them.