We Rebuilt Our RAG Pipeline Three Times. The Third One Used Postgres.

A Sunday night in November. We had been on a call for forty minutes with a customer who had found a gap in the product, and the feature meant to close that gap was the RAG pipeline we had spent three weeks building.

The pipeline was not working.

We were watching log output scroll past in a terminal while the customer described, patiently, what they had expected to happen. The error was not dramatic. The pipeline had simply returned nothing. An empty context block. The LLM had filled in the gap with a hallucination so confident it sounded like documentation.

The root cause took another hour to find after the call ended. The vector database we were using had restarted due to a memory spike. When it restarted, the in-memory index had been rebuilt from the wrong snapshot. The snapshot was stale by about six hours, which meant the most recent documents we had ingested were invisible to queries. The customer had been searching for content we had loaded that morning. To our pipeline, that content did not exist.

We had paid about $120 in that month's infrastructure bill to create a system less reliable than a full-text search index on a machine we already owned.

That night we started writing notes for the third rebuild.

The in-memory mistake

The first pipeline was not supposed to be a pipeline at all. A proof of concept we had convinced ourselves counted as shipping.

We were building a question-answering feature. The documents were small, maybe two thousand chunks total. We loaded the embeddings into a JavaScript array at startup, computed cosine similarity in a loop, and returned the top five results. It worked in development. It worked in the first staging test. It worked every time we demonstrated it, because we ran the demo from a fresh process on a machine where we had just seeded the data.

What it did not do was survive a restart.

Every time the Node process restarted, which it did whenever we deployed a change, the in-memory store came back empty. We had wired up a seed script that ran on startup, but the seed script was slow, around four minutes for the full corpus. Those four minutes of warmup meant four minutes of blank results after every deploy.

We added a loading flag. We added a health check that failed during warmup. We added a retry loop on the client. Each fix was a layer on top of a design that was wrong from the start, and we kept adding layers because rebuilding from scratch felt like admitting we had wasted the three weeks.

Eventually the corpus size crossed a threshold where the seed script ran out of memory before it finished loading. That was the moment we could not pretend the first approach was a real thing anymore.

The vector database tax

The second pipeline was the professional choice. We read the benchmarks. We read the comparison posts. We picked a dedicated vector database because that was what people who built RAG systems used. We spent two days setting it up and a week wiring it into the application.

The first thing we noticed was the new bill. The managed cluster cost $80 a month at the size we needed, plus egress charges if the query volume went above a threshold we did not fully understand. We had been paying nothing for the in-memory approach. Now we were paying $80 before a single query ran.

The second thing we noticed was the operational surface. A new set of credentials to manage, a new SDK that released breaking changes on its own schedule, a new dashboard to check when something went wrong, and a new failure mode with no overlap with the rest of our infrastructure.

The vector database was not the problem. Good at what it did. The problem was that it wanted to be its own system. A system of record for vectors. A source of truth that we had to keep synchronized with our actual source of truth, which was a Postgres database we had been running without incident for three years.

Every time we wrote a document to Postgres, we had to write its embedding to the vector database. Every time we deleted a document, we had to delete its vector. Every time we updated a document, we had to re-embed and re-upsert. The synchronization was not hard to write, but relentless to maintain. Two sources of truth that had to agree, and no transaction boundary between them.

The November outage was the version of this that broke in production. The vector database restarted, the snapshot was stale, the two systems disagreed, and the customer saw nothing.

RAG is not a vector search problem. A retrieval problem that happens to use vectors. When we framed it that way, the answer was more obvious than we wanted to admit.

What Postgres got right

We had pgvector sitting unused in our database for most of that year. We had installed it while following a tutorial, gotten distracted, and never removed the extension. When we ran \dx one evening and saw it still listed there, we spent about forty minutes writing the simplest possible replacement for the pipeline we had been paying $80 a month for.

The schema was twelve lines. The ingest path was a function that chunked text, called the embedding API, and inserted rows. The query path was a SQL statement with the <=> cosine distance operator. The HNSW index was one CREATE INDEX line that took about three minutes to build on our existing data.

CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

The thing that changed was not the search quality. Query latency on our dataset, around 80,000 chunks at 1,536 dimensions, came in under 8ms for top-10 results. That was fast enough.

The thing that changed was the synchronization problem. When a document got written to Postgres, its embedding got written in the same transaction. When a document got deleted, the embedding row disappeared with it. When the process restarted, the index was still there, on disk, exactly where we had left it. There was nothing to warm up. The query path was available the moment the connection pool was ready.

Every vector database we had tried wanted to be the system of record. Postgres was already the system of record. That was the whole answer.

The $80 monthly line disappeared. Our staging environment, which had been a stripped-down version of the pipeline to save cost, became identical to production because there was nothing extra to run. Our deployment script got shorter. The only thing left to watch was the same Postgres instance we had been watching for three years.

The honest case for dedicated vector databases

We want to be careful here, because the piece we are not writing is a Pinecone takedown.

At the scale where the synchronization problem we described becomes manageable, something else becomes the constraint. If you have 50 million documents spread across dozens of tenants, each with different access controls and different freshness requirements, a dedicated vector database starts earning its operational complexity. The multi-tenant isolation features, the native hybrid search with BM25 ranking, the ability to swap embedding dimensions without a schema migration: those are real capabilities that Postgres does not match without significant work.

The teams building knowledge bases for enterprise customers with millions of documents, or doing retrieval across fifty different corpora simultaneously, are not over-engineering. They are solving a problem that genuinely requires a system purpose-built for it.

The teams we see reaching for Pinecone at launch are the ones building internal tools for fifty users with ten thousand documents. They are not solving that problem. They are anticipating it, paying $80 a month against a hypothetical scale they have not yet earned, and adding operational complexity on top of a proof of concept that has not proven anything yet.

The guidance we would offer is simple: run the numbers on your actual document count. pgvector returns top-10 results in under 10ms at 10 million rows on a reasonably provisioned Postgres instance. If your row count is below that threshold and your query pattern does not require multi-tenant isolation or real-time filtering at the index level, you probably do not need the extra infrastructure. Wait until the data proves otherwise.

The thing we had to unlearn

The pipeline we had built the first two times reflected what we thought a production RAG system looked like, not what our specific problem required. We had read too many architecture diagrams that showed a distinct vector store box, and we had internalized that box as mandatory.

That is a particular kind of mistake that is easy to make when a category of tooling gets enough marketing attention. The marketing tells you a problem requires a solution of a certain shape. You adopt the shape. The shape does not fit your actual problem, but the marketing said it should, so you add layers until the shape sort of fits.

The third pipeline did not start with an architecture diagram. It started with a constraint: one connection string and zero synchronization logic. Everything else followed from that.

Postgres with pgvector is not the answer for every RAG system. For most RAG systems shipping right now, across teams at the stage where adding new infrastructure costs more than the benefit for months before any scale arrives to justify it, Postgres handles the job.

The Sunday call ended. The customer was patient and professional about it. We shipped the third pipeline on a Thursday, ten days later. It has not failed in production since. Not because we engineered it to be resilient. Because we removed the thing that was breaking.

That turned out to be enough.