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.

The "RAG is dead" narrative really urks me particularly because I don't really think any of the points people make are strong.

1. "Context windows keep growing, so eventually we won't need retrieval."

1M tokens is about 3,000 pages. I regularly work on corpuses in the hundreds of billions of documents. There is no plausible future where a context window holds an large org's entire corpus.

And even if it could, you wouldn't want it to. Stuffing the window is completely token-inefficient, and on top of that accuracy degrades as context grows (especially true when there is competing data in the window). Prompt caching may help with token consumption, but this only really works for static corpora.

2. "Grep beats RAG."

Grep is great when you want precision. If you want recall over a large corpus, retrieval wins almost every time when done properly (hybrid + reranking + pruning) it also uses dramatically fewer tokens.

Most of the grep argument comes from coding agents navigating a repo. Repos have structure: file trees, naming conventions, symbols. In most companies data does not have a perfectly clean structure (if any structure at all). So in many scenarios grep has nothing to walk.

3. Pushing work into the database beats pushing it into the model.

Agentic search puts the LLM in the loop on every step. Every step is tokens, every step is a sequential round trip, and every turn re-prefills a growing transcript. Index-time work is paid once and amortized across every query. The more you push out of the LLM, the cheaper and faster the workload.

4. Permissions and freshness.

You can't precompute a cache per user per ACL. Real world retrieval requires query-time filtering, and retrieval also supports far more sophisticated filtering than an agent grepping around: metadata predicates, tenancy boundaries, time ranges, structured conditions composed with the search itself.

And I'm not saying agentic search doesn't have it's place. There are plenty of scenarios where it may be the best choice. But its not killing RAG it's just another option.

When combining similarity search with a metadata filter, the order a db picks decides how many results you get and how good they are. Filter after the search and a narrow filter leaves you short of the ten you asked for; filter during the search and the count holds but quality can quietly drop, because a filter that hides most of an HNSW graph strands the walk in a region with no matching neighbors left.

The four strategies

Post-filter. Search everything on a fixed budget, then throw out hits that fail the filter. Cheap, returns fewer than k, and what survives is not the nearest matching rows.

Post-filter with retry (iterative, batched). Same, but keep resuming the search until k survivors accumulate. This fixes the count. There has to be a cap on how long it runs, and hitting the cap returns a short result anyway.

Pre-filter into exact search. Resolve the filter against a metadata index first, then compare the query against every surviving row. Exact, full k, no quality risk. Only affordable when few rows survive.

Simultaneous. Check the filter inside the search itself, using a bitmap of allowed ids, extra graph edges between rows sharing a value, or an index that says which clusters hold a match before you probe them. Fills k, and quality is what degrades.

Engines that post-filter

FAISS

FAISS filters with an IDSelector, a function that accepts or rejects each candidate id as the search runs. The problem is that the search budget, efSearch for HNSW or nprobe for IVF, is fixed before the filter comes into play. A narrow filter shrinks the pool of acceptable candidates while the amount of searching stays the same, so a restrictive filter can return well under k results. There is no retry, no fallback to an exact comparison, and no estimate of how many rows will match.

Result: correct filtered search is achievable but you build it, by raising efSearch or nprobe until quality holds, or by keeping a separate index per filter value. That is engineering time, not configuration.

pgvector

In Postgres a filtered vector query is a WHERE clause with an ORDER BY on distance. The planner either scans the table and computes distances exactly, which is correct, or uses the HNSW index and applies the WHERE to whatever the index returned. That second path is a post-filter limited by hnsw.ef_search, which defaults to 40 rows, so a filter matching 10% of the table leaves roughly four rows for a LIMIT 10. Version 0.8.0 added iterative scans that keep searching until the limit is met, bounded by hnsw.max_scan_tuples, and they are off by default.

Result: you get a full result set with good quality once you turn on hnsw.iterative_scan and raise hnsw.max_scan_tuples, paying latency that grows as the filter narrows. On defaults every filtered query is quietly short. Fine for moderate filters, weak for narrow ones.

Chroma

Chroma runs the filter first and turns the result into a bitmap of allowed ids, which it hands to the HNSW search. So the filter is checked during the walk, which is the right design. What is missing is compensation: the number of neighbors requested from the index is exactly the number you asked for, with no margin and no second pass to cover candidates the bitmap rejects along the way. This is why filtered Chroma queries miss the closest matches unless you request far more results than you need.

Result: no setting gets you reliable filtered results. Request several times your real n_results and re-rank in your own code, and look elsewhere if filtered queries are a core access pattern.

Engines that switch strategy based on the filter

Elasticsearch

A filter placed inside the knn clause becomes a bitmap of allowed documents that Elasticsearch checks while walking the graph. It also watches for the two cases where the graph is the wrong tool: if fewer documents match the filter than num_candidates, or if the walk has already visited more nodes than there are matching documents, it abandons the graph and compares the query against every matching document. That path is exact. The catch is that the same filter written outside the knn clause, as a normal query or a post_filter, is a true post-filter and behaves like one.

Result: a full result set, exact on narrow filters and good on wide ones, with no tuning beyond num_candidates, as long as the filter goes inside the knn clause.

OpenSearch

OpenSearch makes the same call as Elasticsearch but from more inputs: index size, how many documents pass the filter, the k you asked for, and a configurable threshold. Few matches means comparing the query against the matching documents directly; many means a graph walk with the filter checked during traversal. On the Faiss engine it adds a check nothing else here has. If the filtered graph search returns fewer than k results even though more than k documents matched, it throws that result away and redoes the search exactly.

Result: a full result set, and the one engine that notices when it came up short and fixes it. Use the faiss or lucene engine; nmslib has no filtering support worth using.

Qdrant

Qdrant indexes your metadata fields and uses those indexes for two jobs: resolving the filter, and estimating how many points will match before it decides how to search. A small estimate means skipping the graph and scoring the matching points directly. A large one means walking the graph and checking the filter at each step. For the awkward middle, where the graph is still worth using but many nodes are excluded, Qdrant adds extra edges at build time between points that share a metadata value, so a filtered subgraph stays connected. Those edges only get built if the metadata index already existed.

Result: a full result set with good quality and speed across the whole range of filter sizes, provided you create metadata indexes before loading data. Adding one later means reindexing.

Weaviate

Weaviate resolves the filter into a list of allowed objects first. If that list is under 40,000 objects it compares the query against all of them, which is exact. Above that it walks HNSW one of two ways. Sweeping walks normally and ignores non-matching nodes, which fails when the excluded nodes are the ones nearest your query, because the walk runs out of anywhere to go. ACORN, the default for collections created from 1.34 onward, keeps every graph edge intact and looks two hops out, so it steps over excluded nodes instead of dead ending on them.

Result: a full result set, exact under 40,000 matches and fast above it. Keep ACORN, or set filterStrategy on collections created before 1.34, since sweeping is where quality is lost.

Milvus

Milvus turns the filter into a bitset, one bit per entity, and consults it during the search. That works well until the filter expression itself is expensive, at which point evaluating it across the whole collection costs more than the search does. For that case Milvus 2.5 added an iterative mode behind a hint: it runs the vector search as an iterator, applies the filter to each batch of results, and keeps pulling batches until it has k survivors. It checks entities one at a time, so it slows down when many rows need filtering.

Result: a full result set either way, so the choice is only about latency. Use the default bitset for cheap filter expressions and the iterative mode for expensive ones. Choosing wrong costs time, not results.

Engines that pre-filter or filter inline

Pinecone

Pinecone merges the metadata and vector indexes so the filter is applied while the candidate list is being built, rather than before or after it. On serverless this happens inside the retrieval path over immutable slabs of vectors, and Pinecone switches internally between evaluating the filter on the fly and using precomputed representations of it depending on how selective it looks. You get exactly the number of matching nearest neighbors you asked for.

Result: a full result set at good speed with nothing to configure, which is also the limitation. There is no strategy knob, so if quality disappoints your only levers are changing your metadata schema or splitting data into namespaces.

MongoDB Atlas Vector Search

The filter field inside $vectorSearch removes documents before Mongo walks the HNSW graph, and any field you filter on has to be declared as a filter field in the index definition. The detail that catches people is that this runs per segment, and each segment has its own graph covering only its own vectors. numCandidates, the pool considered before trimming to your limit, is spent within each segment, so a narrow filter can burn through it on a segment that held few matches.

Result: a full result set with good quality if you raise numCandidates well above what an unfiltered query needs, roughly 10 to 20 times your limit on narrow filters. Keep the filter in the filter field, not in a $match stage afterward.

LanceDB

LanceDB pre-filters by default. The where clause is pushed down through scalar indexes into a mask of matching rows, and the vector search only looks at those rows. Setting prefilter=False switches to post-filtering, which is worth doing only when the expression is too complex to index. Because Lance stores data in columnar files on object storage, falling back to scanning the matching rows costs less than it would for an in-memory graph index.

Result: a full result set with good quality on defaults, and good speed once you add a scalar index on the filter column. Check that your client library is not inverting the flag, which has been a real bug.

Infino

Infino keeps SQL, full-text and vector search over one copy of the data as Parquet on object storage and resolves all three in a single pass. A filter on a vector search is a text predicate over a full-text-indexed column, and the kNN ranks only the rows that match it, so every result you get comes from the matching set. Scalar filtering goes through SQL instead. The vector index is IVF, which groups vectors into partitions and probes a subset of them, controlled by nprobe.

Result: a full result set drawn only from matching rows, with quality depending on nprobe, since matching rows spread thinly across many partitions need more of those partitions probed to be found.

TLDR

Correct on defaults:

• OpenSearch. Picks its strategy from the filter's size and redoes the search exactly when it comes up short. The safest of the group. Avoid the nmslib engine.
• Pinecone. Full results with nothing to configure, and nothing to tune if quality disappoints.
• LanceDB. Pre-filters by default. Add a scalar index on the filter column for speed.
• Milvus. Full results in both modes, so the bitset versus iterative choice is about latency alone.

Correct once you do one thing:

• Elasticsearch. Put the filter inside the knn clause rather than at the top level. Then it is exact on narrow filters.
• Qdrant. Create metadata indexes before loading data. Retrofitting means reindexing, and without them the planner is guessing.
• Weaviate. Keep ACORN, or set filterStrategy on collections older than 1.34. Exact under 40,000 matches.
• MongoDB Atlas. Raise numCandidates to roughly 10 to 20 times your limit on narrow filters, and keep the filter out of a trailing $match.
• pgvector. Turn on hnsw.iterative_scan and raise hnsw.max_scan_tuples. On defaults every filtered query is short.
• Infino. Text predicates filter before the ranking, so results come only from matching rows. Raise nprobe when those rows are spread across many partitions.

Cannot be fixed by configuration:

• Chroma. Request several times the results you need and re-rank yourself. A poor fit if filtered queries are central to your workload.
• FAISS. No estimate, no retry, no fallback. Raise efSearch or nprobe yourself, or split the index by filter value. Engineering time, not configuration.

Anyways, hopefully this is useful.

I got 4M chunks of internal docs plus metadata we filter on. Currently BM25 + dense with RRF, which does beat either one alone.

RRF k=60. Why 60? Because the paper said 60 and now everyone says 60. Has anyone swept it on their own data and landed somewhere else?

Score fusion. Go weighted instead of rank-based and you're normalizing two score distributions that have nothing to do with each other. Every normalization choice is another knob nobody evaluates. Is anyone actually tuning this, or is rank-based fusion just the way out?

Metadata filtering. Pre-filter and ANN recall falls apart. Post-filter and you ask for 10 and get 3. Any engine you've found that pushes filters into the ranking layer properly?