Vectors that live in the same transaction as the rows they describe

A vector database is a datastore whose main job is to hold embeddings — the lists of several hundred to a few thousand floating-point numbers a model produces for a piece of text, arranged so that nearness in that space corresponds to nearness in meaning — and to answer one question quickly: which of the stored vectors are closest to this one? A B-tree cannot answer it. A B-tree prunes by discarding every key outside a range, and pruning that way needs a key whose order tracks the quantity being searched on. How close a stored vector is to a query is not a property of the stored vector; it changes with every query, so there is nothing to sort by in advance, and 1,536 coordinates supply no single ordering of their own either. The search is done instead with the approximate nearest neighbour (ANN) structures the previous topic covered, which give up a little accuracy in exchange for a great deal of speed. The category exists because the retrieval step behind an assistant, a semantic search box or a recommendation feed has to run that search on every request, across millions of stored chunks, inside a budget of tens of milliseconds.

You meet one in two shapes. The first is a service built for this and nothing else — Pinecone, Qdrant, Weaviate, Milvus — which you run or rent beside the database you already have. The second is an extension to that database, and on Postgres it is pgvector, which adds a vector column type, distance operators and index types to tables sitting next to your users, orders and documents. The choice between those two shapes, for a particular workload, is the subject of this topic, and one of the things that choice decides is what happens to a piece of derived data when the thing it was derived from changes — when the row behind an embedding is edited, or deleted. That is the argument this chapter makes, and it has nothing to do with how fast an approximate nearest-neighbour search runs. From the moment an embedding and its source row are stored in systems with separate durability, separate transactions and separate failure modes, keeping the two agreeing with each other becomes a job somebody owns forever.

What one transaction actually buys#

A transaction is a promise that a set of writes will be applied completely or not at all. The promise is kept with a write-ahead log: the changes are written into the log first, and a commit record at the end of them is what makes them real. A crash before that record is written leaves nothing applied, a crash after it leaves everything applied, and there is no third outcome, because there is one record and it either exists or it does not.

That promise belongs to whatever owns the log, and it can be made about the writes that went into that log and about nothing else. Two systems keep two logs, and no participant owns both. That single sentence is the whole of the split-store problem, and the rest of this section is what follows from it.

Follow it one step at a time. The write to the first system commits, so it is durable and visible to readers. The write to the second system has not happened yet, because it is a separate call to a separate process with its own acknowledgement. Between those two instants the two systems disagree about what exists. That interval is not a defect in either of them: it is what having two commits means. Its length is the second write's latency while everything works, and it has no length at all when the second write fails, because nothing in the first commit was conditional on the second ever being attempted. A reader arriving inside that interval is served a vector for a row that is gone, or misses a row that is there.

Three guarantees are usually named in one breath, and they behave differently here. Atomicity is the one that disappears outright, since all-or-nothing was only ever offered over one log. Isolation fails more quietly: with the vector in a column, a reader's snapshot covers the row and its vector together because one commit made both visible, while across two systems there is no snapshot that spans both, so even when both writes eventually succeed a reader can observe one of them without the other. Durability is the guarantee that is not the problem, and that is worth saying plainly, because durability is what people reach for when they are nervous about a second datastore. Both systems can be perfectly durable, replicated and backed up, and the disagreement happens anyway. It is produced by there being two commits rather than by either commit being unsafe.

The defence usually offered against this is an ordering rule: write the row, then write the vector, and retry the second write until it lands. It is tempting, because it makes the interleaving deterministic and because in a healthy system the window it leaves is a few milliseconds wide, which no test is going to catch it in. It is not a defence, and the reason is that the rule reads exactly the same whether the second write lands in two milliseconds or never lands at all. A rule that does not change when the dangerous condition changes cannot tell you which condition you are in, and cannot be used to rank one deployment as safer than another. Ordering decides which of the two disagreement states you sit in; it does not remove the state. A retry budget shortens the window without closing it, and the budget is a fixed number that stays fixed while the failure becomes permanent.

Writing an embedding into a column of the same database means the write is subject to the same atomicity rule as everything else in the statement. There is no window in which the document exists and its vector does not, and no window in which the vector exists and the document has been rolled back.

BEGIN;

INSERT INTO documents (id, workspace_id, title, body, visibility, updated_at)
VALUES ($1, $2, $3, $4, 'internal', now());

INSERT INTO document_chunks (document_id, ordinal, content, embedding)
SELECT $1, ordinal, content, embedding
FROM unnest($5::int[], $6::text[], $7::vector(1536)[])
     AS t(ordinal, content, embedding);

COMMIT;

The document_chunks.document_id column carries REFERENCES documents(id) ON DELETE CASCADE, which is why a document's chunks cannot outlive the document in this shape. The admin tool that deletes a document does not need to know that a retrieval feature exists; the constraint knows. The retention job that purges workspaces older than 400 days does not need to know either. Deletes propagate because propagation is a property of the schema rather than a property of every code path that might issue a removal, and code paths are what you fail to enumerate.

That last clause is worth turning into something checkable. A foreign key is enforced by the engine, on every delete, whatever statement issued it and whoever wrote that statement, so its coverage is the set of all deletes exactly, with nothing left over. Instrumentation's coverage is the set of call sites somebody went and found, and that is a set you can only ever lower-bound — finding one more raises the count, and no amount of searching proves there is not another. The two are not the same guarantee at different strengths. One set is closed and the other is open, and the closed one is what licenses stopping: with a cascade in place there is no audit of delete paths left to perform, this quarter or in three years.

There is an obvious objection: embedding a chunk requires an API call that takes 80 to 400 ms, and you do not want that inside a user-facing transaction. The objection is correct, and the usual resolution is to write the rows first with a null embedding, enqueue the chunks, and fill the column in a background worker. That design keeps the transaction short and still keeps the vector in the same database, which means the worker's UPDATE either lands or does not, a partially embedded document is visible as embedding IS NULL rather than as an absence in another system, and the backfill query for "everything that failed to embed" is a WHERE clause rather than a diff between two inventories. The asynchrony is unavoidable; the second durability domain is not.

Two things in that resolution are worth separating, because only one of them moved. The transaction boundary moved, and the failure domain did not — the embedding is written later, and it is still written into the log that already holds the row. The second thing is the more general one. Co-location turns every question of the form "are these two things in agreement?" into a predicate over a single snapshot. The rows matching embedding IS NULL are exactly the chunks still owed a vector, with no residual either way, and they were read at one instant from data that was consistent at that instant. The split-store equivalent is a difference between two inventories, and a difference between two inventories is only ever as good as the least fresh of them. That is the gap between answering the question with a query and answering it with a job.

The same holds for updates. When somebody edits a document, the natural implementation replaces its chunks inside the same transaction that writes the new body, so a reader either sees the old body with the old vectors or the new body with the new vectors. In a split-store design the equivalent guarantee has to be manufactured: you need a version stamp on every vector, a rule for what a reader does when the stamps disagree, and a reconciliation job that finds the pairs which drifted anyway. That machinery is not exotic and plenty of teams run it successfully. It is simply work that Postgres was already doing.

A filter is a predicate over columns you already index#

The second thing that comes for free is the part teams notice last and miss most. Retrieval in a real product is almost never "find me the ten nearest vectors in the corpus". It is "find me the ten nearest vectors among the documents this user's workspace can see, that are not archived, whose language matches the request, and that were published before the effective date on this contract".

SELECT c.id, c.content, d.title
FROM document_chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.workspace_id = $1
  AND d.visibility IN ('internal', 'public')
  AND d.archived_at IS NULL
  AND d.published_at <= $2
ORDER BY c.embedding <=> $3
LIMIT 10;

Every one of those conditions is an ordinary predicate against columns that already have indexes on them because the rest of the application queries them. The join to documents is a join, not a second round trip and a client-side merge. The planner can choose between using the HNSW index and doing an exact scan behind a highly selective B-tree filter, and since pgvector 0.8.0 it can iterate the vector index for more candidates when the filter removes too many of the first batch — the over-filtering problem that made earlier versions return five rows when you asked for ten.

The planner's second option is worth explaining rather than listing, because it is the one that sounds wrong. Ignoring the vector index and scanning can win whenever the B-tree predicates leave few enough rows: reading those rows and computing an exact distance for each of them is a small fixed amount of work per surviving row, while traversing a graph built across the whole corpus costs roughly the same regardless of how few rows will survive. Below some number of survivors the first is cheaper, and the crossover is a cost estimate the planner already makes for every other index it owns.

Both the planner's choice and the over-filtering problem come off one property, and it is worth stating on its own: an ANN traversal ranks candidates by distance and knows nothing about your predicate. That is why an exact scan can beat it, and it is also what makes filtered recall a different measurement from unfiltered recall. A store that applies the predicate afterwards takes the candidates the traversal produced, drops the ones that fail, and returns what survives, so a selective predicate can leave fewer survivors than the ten that were asked for — and the more selective it is, the larger the shortfall. A store that applies the predicate first has to know the attribute before the traversal starts, which is what forces those attributes to be copied in. Which of the two a product does is a design decision rather than a consequence of how ANN works, and it is why a recall figure measured without a filter does not carry over to a query that has one.

The split-store version of this query has to answer a harder question before it can run at all. Whatever attributes you want to filter on must be copied into the vector store as metadata at write time, which means every attribute you might filter on in the future must be decided now, and every change to one of them — a workspace being renamed, a document's visibility flipping from internal to public, a retention policy backdating archived_at — becomes a second write to a second system. Filtering semantics differ between products, too: some apply the filter before the graph traversal, some after, some approximately, and the recall you measured with no filter is not the recall you get with one. None of that is a defect in those products. It is the cost of the vectors not living next to the columns.

Read that table down its left column rather than across it. Every row names a guarantee the database already provides — all-or-nothing writes, referential integrity, the ability to query any attribute, a correct answer under a predicate, a consistent restore point, a replication story, and somebody who knows how to diagnose the thing at three in the morning. Every cell on the right is the software you write to replace that one guarantee. The rows close on the guarantees exactly, with no leftover row that is a cost appearing from nowhere, and one axis is conspicuously absent: none of the seven is about speed, which is the axis every published comparison is made on. That closure is what licenses extending the table yourself — name any other guarantee you currently take from Postgres and would still need, and it is another row, with the same two columns.

The row about restore deserves more than a table cell. Restoring Postgres to 09:14 last Thursday is a solved, rehearsed operation on most teams. Restoring Postgres to 09:14 and a vector store to the same instant is two restores whose snapshots were taken on different schedules by different mechanisms, and the difference between them is a set of documents whose vectors are missing, stale, or orphaned. The reconciliation is a script somebody has to write, and it will be written under time pressure during an incident unless it was written before one.

Eight milliseconds against a three-second answer#

The reason this argument gets lost is that vector store comparisons are published as latency charts, and pgvector does not win latency charts against systems engineered for one job. On a corpus of three million chunks at 1536 dimensions, a well-tuned HNSW index in Postgres returns ten neighbours at a p95 in the region of 12 ms with recall@10 around 0.94; a dedicated store on the same corpus and hardware might do it in 4 ms at the same recall. That is a real 8 ms. In a retrieval-augmented pipeline — one that fetches chunks and pastes them into a model's prompt before it answers — the generation call takes 900 ms to 3 s, so eight milliseconds is under one percent of what the user waits at the fast end of that range and a fraction of one percent at the slow end. The eight milliseconds are what appears in the benchmark. The consistency window in the split design does not appear in any benchmark, because it is not a property anyone measures on a chart: it runs at a median of about 900 ms while the ingest queue is healthy, stretches to tens of minutes — forty of them, on one team's worst afternoon — while the queue is backed up, and never closes at all where a delete path was not wired up.

The wrong version of this comparison is tempting because latency is easy to measure and coherence is not. You can produce a graph of the first in an afternoon, from a load generator, without anything having to go wrong. The second only shows up as an incident report, months later, written by somebody who was not in the room when the choice was made — because the only way to obtain the number is to wait for a failure and then measure how long the disagreement lasted. Two quantities gathered that differently do not belong on the same axis, and a decision that weighs them as though they were comparable has already been settled by whichever one was cheaper to collect.

Six things the decision actually turns on#

The choice deserves a written record, and the record is worth structuring around the dimensions that can actually flip it rather than around whichever store somebody enjoyed using at a previous job. Six of them carry the weight:

• Corpus size and growth rate. Not just three million chunks, but three million growing at four hundred thousand a month, because the second number is what decides when you revisit.
• Query rate and shape. Forty queries per second at peak is a different problem from four, and both are different from a nightly batch that issues two million similarity searches in an hour.
• Filter selectivity. If the typical query is scoped to one workspace holding 0.02% of the corpus, the vector index may barely matter; if queries run unfiltered across everything, it is the whole game.
• SLOs. The service level objectives you have committed to internally: the retrieval budget inside the end-to-end latency target, and the recall floor below which answer quality is unacceptable.
• Staffing. How many engineers can debug the thing when it misbehaves, and whether that number is greater than one.
• Failure behaviour. What the product does when the store is slow, unreachable, or returning stale results, and whether that degradation is acceptable to the business.

What those six have in common is that each of them can be measured, and each can flip the answer on its own without any of the other five moving. A preference for a particular product has neither property, which is why it is not on the list: no measurement can move it, and it does not change when the workload does.

Those six are the skeleton of the decision record, and chapter 3 is where it gets filled in with measurements rather than assertions.

When an engineer proposes bringing in a dedicated vector database for a three-million-chunk corpus, these are the axes to ask about, and the shape of the answer matters more than the conclusion. "pgvector does not scale past a million vectors" is not an answer, because there is no such threshold in the code or in the published operational reports; people run pgvector well past that, and people have trouble with it well below that, depending on dimensionality, memory and write rate. An answer that would move the decision names a measurement: what the index build costs today and what it will cost at the projected corpus, what the peak resident memory during that build does to the OLTP workload sharing the instance — online transaction processing, meaning the short reads and writes the application serves to users — and how often the index has to be rebuilt. Those are the terms in which the real threshold is expressed.

What a specialist store is better at, and what co-location costs#

Everything above is an argument for a default, not a proof of superiority. Postgres is a general-purpose transactional database that has been taught to store and search vectors, and a specialist system that does nothing else will beat it on the axes it specialises in — memory efficiency per vector, build throughput, multi-tenant isolation of index resources, distributed sharding, and operational features like online reindexing that Postgres does not have for this index type.

Nor is the coherence argument free of caveats. Putting vectors in the primary database means vector work and transactional work compete for the same buffer cache, the same CPU and the same maintenance windows, which is precisely the mechanism by which the default eventually stops holding. The chain is short and each link is checkable: the buffer cache is a fixed pool, an index build or a large vector scan pulls pages into it, pages pulled in evict pages already there, the evicted pages are the ones the transactional workload was relying on being resident, and a transactional read that misses becomes a disk read. Nothing in that sequence is about vectors specifically. Vectors reach it sooner because each one is large.

And it does not help at all if your embeddings are not derived from data in that database. If the corpus is a third-party document collection you never store rows for, there is no source row for a vector to be co-located with, so most of the coupling argument evaporates and the decision turns on the six dimensions above instead.

The default is justified because the coupling is worth more than the milliseconds, right up to the point where the coupling starts costing you the primary database's availability. Deletes that propagate without anybody wiring them up, filters that are ordinary predicates over ordinary indexes, a restore that lands both halves at the same instant, and one rotation of people who already know how to read the logs: that is what the eight milliseconds are buying, and on a stack that already runs Postgres it is not a close trade.

What makes it a default rather than a rule is that the trade does eventually reverse. Vector work and transactional work share one machine, and there is a corpus size, a dimensionality and a rebuild cadence at which the sharing costs more than the coherence is worth. That point is real, it arrives, and it is not announced by a vector count — which is why the quantity to watch is not how many vectors you have.