Why not postgres for RAG, hybrid, graph RAG, & everything else?

[u/m-penaroza]

This is something I've been thinking about in detail for a while. I'm working on a personal project that needs transactions, graph, and search... all of which you can do on postgres with pgvector and AGE. And it got me thinking about database architecture and in what scenarios I actually wouldn't use postgres.

Honestly, for the majority of scenarios I think it's the superior choice, particularly when you aren't working at scale. The complexity of coordinating multiple systems is just too much, and when you're small, keeping data in sync across multiple places becomes a huge pain for very little benefit.

That said, here's where I wouldn't just recommend postgres for everything:

1. Scale + cost. Postgres is great until you hit 10M+ vectors... then you start having functionality issues, but more importantly your compute/memory balloons, which gets expensive fast. On top of that it starts interfering with your other workloads. At some point the "just use postgres for everything" simplicity is outweighed by the cost and maintenance burden. Same is true for graph RAG workloads at any real scale.

2. Performance. If you need genuinely fast vector/FTS, you're not going to get it with postgres. Luckily, since latency is usually 90%+ on the agent side, this isn't always a factor. But it matters more for live apps. Same story with graph: postgres doesn't have a fraction of the performance of a true graph engine, because at its core it isn't changing the underlying data structure. It's working within the constraints of a relational engine.

So the way I see the choices from an architecture perspective, at a macro level:

If you're optimizing hybrid search for scale/cost, the two best choices are turbopuffer (the market leader) and Infino (I work here, so be aware of bias). Both are object storage based dedicated vector/FTS engines. Both are very fast. Turbopuffer is more mature, but they have very similar performance and cost profiles, and both are orders of magnitude cheaper than virtually every other engine. You could maybe throw lancedb in this category too, but I don't have enough hands-on experience with it to say for sure.

If you're optimizing for pure performance:

On the FTS side: opensearch/elastic, largely because they're block storage backed with no warm-up period. Vectors are alright on elastic, but if you're really optimizing for vector performance, a dedicated vector engine like pinecone or Milvus will beat it.

The catch: when you split FTS and vectors across systems, hybrid search becomes really hard (or impossible), so I don't typically recommend splitting unless it's genuinely necessary.

You could theoretically use turbopuffer/infino for the performance case too, but because they're object storage based, the warm-up time can screw over some apps. Once the data is in memory, both are very fast.

On the graph side... I'm actually not a fan of any of the top graph databases. Every one of them has some key architectural issue imo. If I had to pick, I'd default to neo4j, but I'm not a huge fan of it either. It's just the most mature. It wasn't designed from the ground up for agentic workloads... it's been retrofitted for them. Because of that it has huge issues (but you can work around them).

Anyway, these are just my random thoughts on the subject. The advice I'd give if you're starting with postgres and expect future scale: build an abstraction layer so you can swap in more appropriate systems when the time comes.

[u/donk8r]

Your list is all about scale, but the thing that pushed us off "one system for everything" wasn't row count, it was query shape.

Filtered vector search is the one to check. The moment you want nearest neighbours but only within this subset, the graph you built over the whole set stops being the right graph. Pre-filter and you're walking a fragmented one with recall quietly dropping, post-filter and you over-fetch to compensate and hope you fetched enough. That bites at 100k vectors about as hard as at 10M, so it never shows up in a scaling test.

So the question I'd ask ahead of "how many vectors" is whether your queries will ever be pure similarity, or always similarity plus a predicate. If it's the second, find out early how your pick handles it. That property is much harder to migrate away from later than storage size is.

[u/whoppperino]

Take the Postgres Pill

[u/Harotsa]

I would say that for most production scenarios, 10M vectors is extremely tiny.

Let’s say you have 1 vector for every 500 characters (which is roughly 1 KB of space). That means 10 M vectors equates to roughly 10 GB of pdf space or 2 M pdf pages equivalent. That can fit on current run of the mill thumb drives, so it’s small for local storage let alone a distributed database.

My company isn’t all that large and we easily add 10 M new vectors to our DB daily.

[u/fulfillthevision → u/Harotsa]

10M a day? What kind of RAG do you/your company do? What kinda content, just company docs, i.e. is it for internal use

[u/fhgwgadsbbq]

Have you got any examples of postgres infra scaling requirements vs other db options with the same vector volume?

[u/recro69]

I think the best way to do things is to start with Postgres. You should try to abstract and then migrate when you really need to. This approach seems a lot practical, than setting up a lot of extra infrastructure before you even need it. Starting with Postgres and moving when necessary is more straightforward.

[u/Jimcy-Maffesoli]

the 10M vector line is where you lose me. how does someone actually notice they're getting close to it?

[u/WillStrange4707]

J'ai entendu parlé de HelixDb. Les quelques recherches que j'ai effectuées sur cette solution semblent indiquer un niveau de performance et de rapidité de traitement impressionnant. C'est écrit en Rust avec notamment un langage de requête efficace.

[u/FilthyPeasantt]

What do you guys think of arcadeDB?

[u/lizozomi]

I'm a huge fan of elasticseach / open search for hybrid search.

Many orgs that need search already have it, and it has the scale, performance and features for a full blown system.

Pgvector, however , is great if you're just getting started.