Each option hands you a different list of things to build yourself

Choosing between pgvector and a store built only for vectors is usually presented as a comparison of features, and it is better understood as a swap of work lists. Whichever option you take, some capability the other one provides will now be yours to build, and the two resulting lists are not the same length, the same difficulty, or the same kind of thing. A managed vector store here means any system that holds the vectors outside your primary database — Pinecone, Qdrant, Weaviate, Milvus, whether you run it or someone else does — and the defining fact about all of them is that they are a second store, with their own copy of whatever they need to answer a query.

Only one of the two lists is visible while the decision is being made. The things Postgres does not do announce themselves the moment you try to do them: you go looking for a hybrid search function, find no such function, and write a ticket. The things a separate store stops doing are currently being done, correctly and silently, by machinery nobody has had to think about, and a capability nobody thinks about does not get written on a comparison slide.

The category filled a hole that has since moved#

It helps to know why the shape of the argument is what it is. Around 2019 the practical options for similarity search were libraries: FAISS, Annoy, hnswlib. They were fast and they were libraries, which meant persistence, replication, filtering, updates and an API were all yours to build. The vector database category grew out of exactly that gap — Milvus, Weaviate, Qdrant, Pinecone all offered to be the service around the index — and at the time, the comparison against Postgres was not close, because Postgres had nothing. pgvector's first release is from 2021 and offered only IVFFlat with exact-ish scans; there was no HNSW until 0.5.0 in 2023.

Everything people repeat about the trade-off was formed in that window, and the ground has moved underneath it. HNSW arrived in 0.5.0, parallel index builds in 0.6.0, halfvec and sparsevec and binary quantization in 0.7.0, iterative index scans in 0.8.0 to fix the over-filtering behaviour that made filtered queries return short. Each of those closed a specific item on the "things pgvector cannot do" list that a 2022 blog post enumerated correctly and a 2026 comparison still cites. Meanwhile the managed stores have added their own hybrid search and their own filtering improvements, so the lists on both sides are shorter than the folklore. What has not changed is that the two lists are different in kind, and only one of them is visible during an evaluation.

What Postgres makes you assemble#

This list is the one everyone knows, and it is real. Rough effort figures are for a team of two who have not done this before.

Every row there is a consequence of the same fact: Postgres gives you an index type inside a general-purpose database, and a specialist product gives you an index service. An index type is something the planner may choose; an index service is something with its own scheduler, its own metrics and its own operational surface. Read down the column with that in mind and each row stops being an arbitrary gap. There is no hybrid search function because fusing two result lists is a query somebody writes, not a storage feature. There is no recall monitoring because Postgres has no notion of what the right answer was — producing one costs a full scan, which is the ground truth harness of chapter 3. Rebuild automation is yours because Postgres has no online reindex for this index type, so a rebuild is a CONCURRENTLY build, a swap and a capacity check that somebody has to sequence. Partitioning is yours because one Postgres instance is one machine. Per-tenant isolation reads "not solvable without partitioning" for a reason rather than out of pessimism: one index over one table shares one buffer pool, so a tenant's bulk ingest evicts everyone's pages, and the only way to stop that is to stop sharing the index. Precision migrations are yours because moving a column from one vector type to another is a type change, and a type change rewrites the table.

Hybrid search — running the keyword and the vector search side by side and merging the two result lists, so that an exact product code and a paraphrased question both find their document — is the headline item and the one worth seeing concretely, because "pgvector does not have hybrid search" is true in the sense that there is no hybrid_search() function, and misleading in the sense that the assembly is about thirty lines of SQL over machinery Postgres already ships.

-- Reciprocal rank fusion over a tsvector index and an HNSW index.
WITH lexical AS (
  SELECT c.id,
         ROW_NUMBER() OVER (ORDER BY ts_rank_cd(c.tsv, q.query) DESC) AS rank
  FROM document_chunks c, websearch_to_tsquery('english', $1) AS q(query)
  WHERE c.tsv @@ q.query
  LIMIT 100
),
semantic AS (
  SELECT c.id,
         ROW_NUMBER() OVER (ORDER BY c.embedding <=> $2) AS rank
  FROM document_chunks c
  ORDER BY c.embedding <=> $2
  LIMIT 100
)
SELECT COALESCE(l.id, s.id) AS id,
       COALESCE(1.0 / (60 + l.rank), 0) + COALESCE(1.0 / (60 + s.rank), 0) AS score
FROM lexical l
FULL OUTER JOIN semantic s ON s.id = l.id
ORDER BY score DESC
LIMIT 10;

Reciprocal rank fusion scores each document by one divided by its position in each list and adds the two together, which means a document has to place well somewhere to survive and needs no calibration between two scoring scales that have nothing in common. That last property is the whole reason the method is built on positions rather than on the underlying scores: ts_rank_cd returns a lexical relevance figure and <=> returns a cosine distance, and there is no conversion factor between them, because they are not measuring in the same units or over the same range. Ranks discard the units and keep the ordering, which is the only part of each list both halves agree about. The constant 60 is the standard smoothing term from the original TREC work, the long-running information-retrieval evaluations where the method was measured, and it is there to stop the top-ranked result of either list from dominating. Adding a constant to the rank makes first and second place score similarly instead of the first scoring twice the second, so a document that placed first in one list and nowhere in the other cannot outrank a document that placed respectably in both.

This query works, it uses a GIN index — Postgres's inverted index over the tsvector column that full-text search reads — for the lexical half and HNSW for the semantic half, and it costs a few milliseconds more than either half alone. What you own afterwards is the tuning: whether 100 candidates per branch is enough, whether the two branches should be weighted equally for your corpus, and what happens when the lexical branch returns nothing because the query was in a language your text search configuration does not handle. A managed store that ships hybrid search owns those decisions for you, which is a genuine saving and a genuine loss of control in the same breath.

What the managed store makes you rebuild#

This is the list nobody writes down, because every item on it is something the current system does silently and correctly, and you do not inventory what you are not thinking about.

The first row is the one every migration plan has, because building the pipeline is the migration. It is worth deriving anyway, since the reason there are exactly two standard answers is not obvious. Postgres makes a row and its vector appear together because they are written in one transaction, and a transaction is a property of one write-ahead log — the ordered record of every change, which recovery replays. There is one such log per database, and a second store cannot write into it. So the vector write has to happen outside the commit, which means either before it, where the vector is visible for a row that may never exist, or after it, where the process can die in between and the vector never arrives. An outbox table and CDC both solve this the same way: they make the second write derive from the committed log instead of racing it. Those are the two standard answers because the committed log and a table written inside the same commit are the only two places a durable record of "this happened" exists.

The remaining rows are consequences of that pipeline existing rather than separate projects. Delete and update propagation is yours because a foreign key lives with the data and a copy in another system has no constraint pointing at it, so every code path that mutates a row must remember the copy — and the set of such code paths is not knowable in advance, which is what "a tax on every future feature" means. Drift detection follows from the pipeline being at-least-once and retried: any such pipeline diverges occasionally, and with no constraint to catch it the divergence is only visible by comparison.

The filtering row is the one that is most often stated backwards. The common claim is that pgvector lacks proper metadata filtering and that this is what the dedicated products are for, and the second half of that has it exactly inverted: filtering over structured attributes is what a relational database has spent fifty years being good at. In a vector store, a filter operates on a payload you copied in, using an expression language that is a subset of what you are used to, with semantics — pre-filter, post-filter, or a heuristic switch between them — that determine whether a selective filter costs you recall. The 0.71 recall on the narrow-workspace slice from the previous chapter is that phenomenon, and closing it means over-fetching by a large factor, or building a payload index, or partitioning the collection by tenant, all of which are work. This row is the one item on the list that is a product decision rather than a structural consequence: nothing about holding vectors separately requires post-filtering, and stores have shipped improvements to it, which is why this is the row most likely to be smaller by the time you read it.

The backup row is worth spelling out because it sounds like a checkbox and is not. Postgres point-in-time recovery lets you restore to an arbitrary instant, because the WAL is a totally ordered log of every change. Most vector stores snapshot a collection on a schedule, which gives you a consistent copy of that collection at whatever moments the schedule fired. Restoring after a bad deploy therefore means recovering Postgres to 09:14 and the vector store to whichever snapshot preceded it, and then computing what changed in between — which requires knowing what changed in between, which requires the drift tooling from the row above it. The two rows are one project, and it is a project whose output is only exercised during an incident, so it is also the one most likely to be deferred and then found to be broken when it is needed. Their estimates are therefore not additive, which is worth knowing before the column gets summed.

Analytics is the item that arrives last and never leaves. Retrieval produces a stream of events — this query, these ten chunk IDs, this relevance feedback — and every interesting question about that stream is a join: retrieved chunks against document owners, against account plans, against churn. A join needs both sides inside one query engine, which is why the item has no end date: while both sides live in Postgres those questions are queries somebody writes in an afternoon, and once the vector store holds the identifiers and Postgres holds the entities, each question becomes an export and a merge. The pattern that develops is that the retrieval events get copied back into Postgres so they can be joined, which is a small data pipeline nobody scoped.

Access control is the quietest item. If your documents table carries RLS policies, those policies do not travel. The vector store authenticates a service, not a user, so the tenant scoping that Postgres enforced structurally becomes an application-level filter that has to be correct on every call site. The shift that matters is from one enforcement point to many: a database policy is applied to every query whoever wrote it, and an application filter is applied only where somebody remembered it. That is why a bug here is a cross-tenant data leak rather than a wrong answer.

Where both lists come from#

Read the two tables against each other and a single property generates each one. Every row on the Postgres list exists because an index inside a general-purpose database is not an index product. Every row on the managed-store list exists because the vectors no longer sit in the same transactional store as the rows they describe — consistency, delete propagation, filter semantics, the shared restore point, drift, joins and row-level policies are all things that were free only while there was one store, and they are the whole of that list with nothing left over.

That the second list closes exactly on one cause is what makes it usable on a store that is not in the table. Given any candidate, the question that predicts its list is not which features it ships. It is whether its copy of your data sits inside your transaction boundary or outside it. A store outside it inherits all seven rows regardless of what its comparison page says, and a store inside it — pgvector, or any future extension that keeps vectors in the primary — inherits none of them and picks up the other list instead.

The two causes behave differently over time, which is what the word "kind" was standing in for earlier. A missing product feature is a decision somebody can reverse, and the release history above is a list of exactly that happening: hybrid search, iterative scans, quantization. A guarantee lost by splitting the data cannot be restored by a release, because no version of a separate store can put itself inside your write-ahead log. So the first list shrinks on somebody else's schedule and the second list does not shrink at all. The one exception is the filtering row, which is a product decision sitting on the structural list, and it behaves like a first-list item: it has been getting smaller.

The effort column rewards the same kind of reading. Summed as durations the two lists look comparable, a few weeks against a few weeks. Read instead for which entries terminate, and the Postgres column is almost entirely projects with an end, while the managed-store column carries "then permanent", "a tax on every future feature" and "Ongoing" — entries that are not durations at all and cannot be added to the others.

Why the second list is the one that gets underestimated#

Both lists are real and both have items on them that a given team will find easy. The asymmetry is in how they become visible. Everything on the first list announces itself during evaluation: you go to write hybrid search, discover there is no function for it, and file a ticket. Everything on the second list is currently working, which means it is invisible in exactly the way that a load bearing wall is invisible. Nobody writes "we currently get delete propagation from a foreign key" on a comparison slide, because it has never once been a problem.

The defence teams reach for here is a feature matrix, and a feature matrix is the wrong instrument for the same reason a p95 was the wrong instrument in chapter 3: it does not move when the property that matters moves. A missing product feature and a lost structural guarantee render as the same mark in the same column, and shipping a release can turn the first mark green and can never turn the second one green. Ordering two options by a checklist therefore orders them by something that is blind to permanence, which is the only axis on which these two lists genuinely differ.

There is a rough test for whether an evaluation has taken the second list seriously: ask which of the current system's guarantees the proposal is planning to give up, and see whether the answer is a list or a blank. If a team can name the guarantee, quantify the window it is being replaced by, and say what the repair path is when the window is exceeded, they have done the work. If the answer is that consistency will be handled by the ingest pipeline, they have named the place where the work will appear and not the work.

The interview version of this asks for three items from each side, and a strong answer does more than enumerate. It says which of the six the team can actually absorb: a team that has run CDC pipelines before will find the outbox item routine and might find partitioning hard; a team with deep Postgres operational skill and no distributed-systems experience will find the reverse. The lists are not scored in the abstract. They are scored against the seven people who will be maintaining the result.

The workloads that reverse the answer#

It would be a misreading of this chapter to conclude that the second list is longer and therefore Postgres wins. Both lists are finite, both are tractable, and there are workloads — a billion vectors, a hundred thousand queries per second, a corpus with no relational counterpart at all — where the first list is impossible to complete and the second is merely tedious. The last of those is the cleanest case: when the vectors describe nothing that lives in your database, the property generating the second list is absent, and the list largely evaporates with it. The point is that the comparison is only meaningful when both columns are filled in, and the usual evaluation fills in one.

It is also not a claim that these lists are static. They are the state of two ecosystems that are both moving, and half of the first column was written by pgvector releases that did not exist three years ago. An evaluation that cites a comparison older than about eighteen months is describing a different product on at least one side. The asymmetry in how the two lists move is itself a reason to date every claim: a stale citation on the Postgres side overstates the work, and a stale citation on the managed-store side is likely still accurate, since those rows do not close.

The decision, then, is not which system is better but which list your team would rather own for the next several years. Ask of each candidate whether its copy of your data sits inside your transaction boundary, and the list follows from the answer without anyone having to read a feature matrix. Inside it, you assemble the things an index product would have shipped, and those items get cheaper as somebody else's releases land. Outside it, you rebuild the guarantees that came from having one store, and those items stay exactly as expensive as they are the day you take them on.

What neither list settles is how you get from one store to the other if you decide to. That turns out to have its own set of failure modes, and the most expensive of them is not in either column, because it only appears while both systems are live at once.