When You Do Not Need a Vector Database

Search this term and page one is documentation written by the companies selling the thing. That is not a criticism of the documentation, which is generally excellent, but it does mean every result shares a premise: that you are choosing a vector database. The prior question, whether the workload needs one, is answered yes on every page because of who wrote them.

It frequently does need one. It also frequently does not, and the cases are separable in advance rather than by migrating and finding out. What follows is the decision rather than a leaderboard: what you get for free before you add anything, what an index actually buys, the one behaviour that breaks more retrieval demos than any other, and the three specific things that change when a Postgres extension stops being the right answer.

The baseline is better than most comparisons admit

If your data already lives in Postgres, the honest starting point is pgvector, and the thing worth knowing about it is what it does with no index at all. Its documentation is direct about this: pgvector performs exact nearest neighbour search by default, which provides perfect recall.

Perfect recall is a stronger guarantee than anything an approximate system offers, and it is worth sitting with for a moment, because most of the discussion in this space is about recovering recall that was given away in the first place. A brute-force scan over your embeddings returns the actual nearest neighbours. There is no tuning, no training step, no recall target to negotiate, and nothing to measure, because there is no error to measure.

What it costs is a scan proportional to the number of rows. That is the entire trade, and it means the useful question is not which database but at what size and query rate does an exact scan stop being affordable for my latency budget. That is a question with a number in it that only your workload can supply, and it is answerable in an afternoon with the corpus you already have.

What an index buys, and the thing it changes quietly

Adding an index turns the search approximate, trading some recall for speed. The pgvector documentation flags the consequence plainly: you will see different results for queries after adding an approximate index.

That sentence deserves more attention than it usually gets, because it is the point at which retrieval stops being deterministic and starts being a quality setting. Before the index, the system returned the right answer. After it, the system returns a good answer most of the time, and how often it is wrong is now a parameter you own. Nothing warns you when it is wrong. The query succeeds, the results look plausible, and the document you needed is simply not in them.

This is the moment evaluation stops being optional, and it is the same argument we make about building an evaluation set out of your own failures rather than out of a metric catalogue. An approximate index introduces a failure mode that is invisible from the outside and only shows up as a model answering worse than it should. If you cannot tell a retrieval miss from a generation problem, you cannot debug either.

The behaviour that breaks retrieval demos

Here is the one to know before you build anything, because it accounts for a disproportionate share of systems that work in a notebook and fail in production.

Real retrieval is almost never a bare similarity search. It is a similarity search with conditions attached: this customer's documents, this date range, this language, not the archived ones. The natural assumption is that the filter narrows the candidates and then the search runs over what is left. pgvector documents the opposite order: filtering is applied after the index is scanned, which means an approximate index can return fewer matching rows than you asked for.

Think about what that produces. You request ten results for one tenant, the index returns its best candidates across the whole table, the filter removes almost all of them because most belong to other tenants, and you get two documents back. Not an error. Two documents, and a model that answers confidently from them. The tighter your filter, the worse it gets, which is exactly backwards from the intuition that a narrower query is an easier query.

The documentation gives the two outs. Exact indexes work well for conditions that match a low percentage of rows, and for broader filters there are iterative index scans, which keep scanning more of the index until enough results are found. Both are real fixes. The point is that you have to know the problem exists, and a filtered multi-tenant workload is precisely the shape that makes it bite hardest.

HNSW or IVFFlat, as a decision rather than a ranking

Two index types, and the tradeoff between them is documented rather than mysterious, which makes this one of the easier choices in the stack.

HNSW has better query performance than IVFFlat on the speed and recall tradeoff, and pays for it with slower build times and more memory. It also needs no training step, so it can be created on an empty table, which matters more than it sounds: it means the index exists before the data arrives and you are not managing a rebuild as a deployment step.

IVFFlat has faster build times and uses less memory, with lower query performance. It has to be created after the table has data, because it works by dividing vectors into lists and searching the nearest ones, and it cannot choose those divisions without seeing the distribution. Its list count is a function of table size, with the documentation suggesting rows divided by 1000 up to around a million rows and the square root of the row count beyond that.

So the decision is mostly about which resource is scarce. If query latency is the constraint and you can afford the memory, HNSW. If memory or build time is the constraint, or the table is rebuilt often, IVFFlat. Neither is an architectural commitment in the way adopting a separate service is, which is the real argument for making this choice before the bigger one.

Three things that change when Postgres stops being the answer

A dedicated vector store earns its place when one of these becomes true. Not when the corpus feels large, and not when the architecture diagram would look more serious.

The scan no longer fits the latency budget, measured rather than assumed. This is the straightforward case and the only one most comparison articles discuss. It arrives later than people expect, because an exact scan over a few hundred thousand embeddings on reasonable hardware is often fast enough for an interactive product, and a database you already operate is worth a great deal of latency.

The index no longer fits in memory alongside everything else the database is doing. This is the case that actually forces most migrations, and it is about contention rather than about vectors. A graph index wants memory, and it is competing with the working set of the transactional workload sharing that instance. Retrieval then degrades under exactly the load that makes it matter. A separate store is partly a correctness decision and mostly an isolation decision.

The retrieval shape stops being nearest-neighbour search. Hybrid scoring against a keyword index, reranking stages, sharding by tenant, filtered search where the filter does most of the selection, live updates at a rate that keeps the index perpetually rebuilding. Each is possible to assemble, and at some point assembling them is the project rather than a detail of it, which is the honest signal that the specialised tool is specialised for a reason.

If none of the three is true, a migration buys you an operational dependency, a second consistency story, and a sync pipeline between your records and their embeddings. That pipeline is where we see the most production incidents in this area: not bad recall, but embeddings that silently no longer correspond to the rows they are supposed to describe.

What to measure before you move

The decision above needs four numbers and all of them come from your own system, which is why no comparison article can hand them to you.

Measure the corpus size you actually have and the size you will have in a year, not the one in the funding deck. Measure exact-search latency at your real query concurrency, with a filter attached, because the filtered path is the one that will run. Measure recall against exact search once you add an index, using queries drawn from real traffic rather than from a benchmark, since recall on your distribution is the only recall that affects your users. And measure how often the corpus changes, because update rate decides more about index choice than size does.

Then make the call. The structure of the decision is stable even though the products in it are not, and a system built on a decision tree can be re-decided when one of the inputs changes. A system built on a leaderboard has to be rebuilt when the leaderboard moves.

This is the same approach we take to integration work generally: the defensible choice is the one whose reasoning survives the next release, and the reasoning here is about your corpus and your query shape rather than about anybody's feature matrix. If you are earlier than this and still deciding what the retrieval layer is for, custom build work starts with that question rather than with the store.