A naive dual-write replicates inserts and quietly loses deletes

Moving a corpus of vectors out of one store and into another is done with two mechanisms running at the same time. A backfill reads the rows that already exist and copies them across. A dual write, also called a shadow write, attaches a rule to the application's write path so that changes made while the backfill is running land in both stores instead of only the old one. When the backfill has finished and the two sides agree, the read path is switched over, and that switch is the cutover. The skeleton is right, and nearly every migration of this kind is built out of those three parts.

What the skeleton does not say is which changes a dual write can see. A chunk here is a passage of a few hundred words cut out of a document and stored with its embedding, so a chunk exists in two places at once: as a row in Postgres, and as an entry in the vector store that has to be removed when the row goes. Keeping the two in step means replicating every event that creates, changes or removes a row. The two mechanisms above have different notions of what "every" covers, and the gap between them is not spread evenly over the operations — it falls almost entirely on removals.

Insert paths are few and mutation paths are many#

The asymmetry that makes this failure so reliable is structural, and the first step of it is a fact about what a dual write is attached to. A dual write is a rule sitting on a piece of code: when this function writes a row, write to the other store as well. The unit being replicated is therefore the code path, and the coverage of the mechanism is the set of code paths somebody remembered to instrument. That set is a property of the team's attention rather than of the data.

Creation is a feature, and features have owners. There is usually one path that creates a chunk, because two different ways of creating the same row is the kind of thing a codebase treats as a defect and removes. Enumerating the creation paths is a small, finite job that stays done once it has been done.

An insert also hands the hook everything it needs. At the moment the rule fires, the new row is in hand: its identifier, the document it belongs to, its payload. Forwarding it to the second store is a matter of copying values that are already sitting in local variables. Nothing has to be reconstructed and nothing has to be looked up, which is why the insert half of a dual write works on the first day and keeps working.

Removal has neither property. Rows leave and change through a long tail: user actions, cascades from parents, retention and GDPR jobs, admin tooling, backfills, deduplication scripts, partition drops, and the occasional manual statement run during an incident. Each of those is a separate path, several of them are not application code at all, and the set grows every time somebody adds a feature that can archive something. When you instrument "the write path", you find the create and you miss most of the rest.

The second difference matters more than the number of paths, and it is the one that is easy to walk past. Several of those paths never learn which rows they removed. A statement of the form DELETE ... WHERE created_at < now() - interval '90 days' names a predicate, and the rows that matched it are known to the storage engine and were never in the application's hands. A cascade names a parent row, and the child rows disappear as a consequence the application did not spell out. By the time either statement returns, the rows are gone, and their identifiers cannot be recovered from the primary store, because the place you would look them up is exactly the place they were just erased from. So the second store is never told which entries to remove, and it keeps them.

That last step is where the failure turns quiet rather than merely wrong. An orphaned entry — a vector whose row no longer exists — is a perfectly well-formed entry. Nothing about it is malformed, out of range, or inconsistent with anything the vector store is able to check, because the vector store holds no reference back to Postgres and has no way to ask whether the document is still there. The failure is the absence of a write, and an absent write raises nothing: no exception, no error rate, no failed job, no latency spike.

That is worth stating as a rule, because it decides which checks are worth building. Every instrument attached to the replication mechanism can only ever see the events the mechanism processed, so no amount of monitoring the dual write will reveal the mutations it never received. Detecting a missing delete requires comparing the contents of the two stores, and the comparison has to be sensitive to which identifiers are present rather than to how many there are.

What a backfill guarantees and what a dual write guarantees#

The two mechanisms are complete with respect to different things, and stating each property precisely turns the gap between them from a matter of judgement into a subtraction.

A backfill is a snapshot operation. It reads the rows that exist at the moment its batch is read and copies them across, so its guarantee is: everything that existed at that instant. It says nothing whatever about what happens afterwards, and it cannot, because it has already read.

A dual write is a stream operation. It forwards the mutations that pass through the paths it is attached to, so its guarantee is: every mutation on an instrumented path. It says nothing about paths outside that set, and it cannot, because it never runs on them.

Neither guarantee implies the other, and the two together cover the corpus only if the instrumented set is total from the instant the snapshot was taken. Written out, the contents of the new store are the snapshot, plus the inserts the dual write saw, minus the deletes it saw. The contents of Postgres are the same snapshot, plus every insert, minus every delete. Subtracting one from the other leaves the inserts that were missed minus the deletes that were missed, and nothing else — the snapshot term cancels exactly, because both sides started from it, and every seen mutation cancels against itself.

That subtraction is the whole diagnosis. Missed inserts are close to nothing, for the reasons in the section above: creation paths are few, they are the ones instrumented first, and a missed insert announces itself the moment somebody searches for a document they have just uploaded and does not find it. Missed deletes are not close to nothing and announce nothing at all. The residual is therefore lopsided in one direction, and the prediction it makes is specific: the new store holds more than Postgres does, never less.

A one-directional prediction is worth more than it looks, because it tells a reconciliation job what a normal result is. Entries in the vector store with no row in Postgres are the expected drift, and finding them means the delete path is still incomplete. Rows in Postgres with no entry in the vector store are a different fault — a lost insert, a relay that failed and was not retried, a backfill batch that never landed — and they deserve their own investigation rather than being swept up by the same repair script. A reconciliation that reports drift in both directions is reporting two bugs, not one.

Capturing mutations where they all look alike#

Instrumenting at the application layer means enumerating that tail correctly and keeping it enumerated as the codebase grows, which is a commitment nobody can honour. Capturing mutations at the database instead makes the coverage a property of the storage engine rather than of your discipline. The usual shape is the outbox, a table into which every change to the rows you care about is recorded by a trigger, in the same transaction that made the change, so that a worker can read it afterwards and relay each event to the other store.

CREATE TABLE vector_outbox (
  id          bigserial PRIMARY KEY,
  chunk_id    uuid        NOT NULL,
  document_id uuid        NOT NULL,
  op          text        NOT NULL CHECK (op IN ('upsert', 'delete')),
  version     bigint      NOT NULL,   -- monotonic per chunk
  payload     jsonb,                  -- null for deletes
  enqueued_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE FUNCTION chunk_to_outbox() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO vector_outbox (chunk_id, document_id, op, version)
    VALUES (OLD.id, OLD.document_id, 'delete', OLD.version);
    RETURN OLD;
  END IF;
  INSERT INTO vector_outbox (chunk_id, document_id, op, version, payload)
  VALUES (NEW.id, NEW.document_id, 'upsert', NEW.version,
          jsonb_build_object('workspace_id', NEW.workspace_id,
                             'archived', NEW.archived));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER chunk_outbox
AFTER INSERT OR UPDATE OR DELETE ON document_chunks
FOR EACH ROW EXECUTE FUNCTION chunk_to_outbox();

A row trigger fires for cascades, for bulk DELETEs, for the retention job and for the statement somebody ran by hand, because all of them are row deletions as far as the engine is concerned. That sentence is the entire argument for moving the hook down a layer, so it is worth spelling out why it holds. The engine does not distinguish a deletion issued by a feature from one issued by a scheduled task or by a cascade; by the point the trigger runs, all three have been reduced to the same event, a row leaving a table, and the trigger is handed OLD.id for each. The variation that defeated the application-layer hook — how many entry points exist and which of them know the identifiers they are removing — has been eliminated before the hook is reached.

The outbox row is written inside the same transaction as the change, so a rolled-back delete does not emit an event, which is the same coupling argument chapter 1 made about the vectors themselves applied to the change stream. Logical decoding, which reconstructs row-level changes from the write-ahead log that Postgres writes anyway, gives you the same coverage without the trigger overhead and is the better choice at high write rates; the trade is that you now operate a replication slot, the server-side bookmark recording how far your consumer has read, and a slot that stops being consumed will hold WAL until the primary runs out of disk.

The honest gap in both approaches is TRUNCATE and partition drops, which do not fire row-level triggers and appear in logical decoding as a single truncate message rather than as forty million deletes. Both escape for the same reason: neither is implemented as a sequence of row deletions. TRUNCATE discards the table's storage without visiting the rows, and dropping a partition detaches a file, so there are no per-row events for a trigger to fire on and none for the log to carry. That is a design decision in Postgres rather than an accident — the operations are fast precisely because they skip the per-row path — and its consequence is that the cheapest class of removal is the one your replication cannot see. If your retention strategy is to drop a monthly partition — and chapter 2 suggested partitioning as a way to keep index builds small, so it may well be — then partition drops are part of your mutation surface and need an explicit hook that deletes the corresponding range from the other store.

Three ways the corrected plan still fails#

Fixing deletes gets you a plan that replicates every mutation. It does not get you a correct cutover, because three problems remain and each has produced a real incident somewhere.

The first is the backfill racing the live stream. A backfill reads a batch of rows, spends time embedding or transforming, and writes them to the new store some seconds later. If a live update to one of those rows lands in between, the backfill's stale copy arrives afterwards and wins.

The rule being applied at the receiving end is ordinary last-writer-wins, and it goes wrong because the two writers are not racing on equal terms. The live write carries a value that is current when it is sent. The backfill's write carries a value that was current when its batch was read, which is one batch-duration earlier. Last-writer-wins resolves by arrival order, and arrival order is not the order in which the two values were true, so the older value can arrive second and be kept.

The window is however long your backfill batch takes, which is typically 30 to 120 seconds, and over an eleven-hour backfill against a corpus taking roughly 180,000 live mutations in that period, a handful of collisions is close to certain. The fix is either a version stamp the receiving store compares against — write only if version is greater than what is stored — or, when the store has no conditional write, routing both the backfill and the live stream through one queue partitioned by chunk ID so that per-key ordering is preserved by construction. A version repairs the ordering because it travels with the value, so comparing versions at the receiving end resolves by the order the values were true in rather than by the order they happened to arrive in. The version column in the outbox above exists for exactly this and costs nothing until the day it saves you.

The second is an acceptance check that measures the wrong thing. Top-k overlap between the old and new store is the right metric — for a probe query, what fraction of the old store's top ten appears in the new store's top ten — and it is easy to compute it on whichever queries are convenient. Convenient queries are unfiltered, popular, and semantically unambiguous, which is to say they are the ones both stores find easy. Use the stratified probe set from chapter 3 and report per stratum, because an overall overlap of 0.97 can and does contain a stratum sitting at 0.71, and the stratum is the query shape that carries 60% of production traffic.

An average is a defence that can hold still while the thing it is meant to discriminate moves underneath it. The same overall figure is produced by loss spread thinly across every query and by loss concentrated entirely in one stratum, and those two situations have nothing in common operationally. A quantity that does not move when the dangerous property moves cannot be used to decide anything about that property, so the repair is not a stricter threshold on the average. It is reporting the strata separately, which restores a number that does move.

The third is that the old index stops receiving writes the moment reads move. Teams keep the old store around for a fortnight as a rollback plan and simultaneously turn off the write path to it, on the reasonable-sounding grounds that nothing reads it any more. The chain of reasoning is sound one step at a time, and what it leaves out is that the old store's value as a rollback path comes from being current rather than from existing, and currency was exactly what those writes were maintaining. From that moment the rollback is nominal: rolling back on day three means serving from an index that is three days stale, missing every document created since the switch, and still holding everything deleted since. A rollback path that has to be repaired before it can be used is not a rollback path. Keep dual-writing to the old store, and keep it reachable behind a flag, for as long as you intend to be able to go back — and pick that duration deliberately, because it is also the duration for which you are paying for two systems and maintaining two write paths.

Finding the drift you already have#

An orphan population that has already accumulated has to be found before it can be removed, and the method generalises to the reconciliation job every dual-store system ends up running. A naive comparison pulls every identifier out of both systems and diffs them, which at 40 million entries is a 40-million-row temporary table and an export that most vector stores make deliberately awkward, since scanning a collection is not what they are built for. It works, it takes a few hours, and it is the right thing to run once during a migration.

For the steady state, compare in slices with a checksum rather than in full. Group both sides by a coarse partition — tenant, or day of creation — count and aggregate the identifiers into a hash, and compare the aggregates. Slices that match need no further work; slices that differ get the expensive full diff, and there are usually a handful of them. Slicing works because a mismatch is local: a slice whose hash agrees contains no difference at all and can be discarded whole without being read again, and drift arrives through a small number of mechanisms that touch a small number of tenants or days at a time, so most slices agree and the expensive work collapses onto the few that do not. On a corpus of 40 million vectors that turned a four-hour job into a twelve-minute one that could run hourly, which matters because the value of a drift check is roughly inverse to how long the drift has been live. Detecting a retention job's orphans on the night they were created is a bug report; detecting them three weeks later is a compliance finding.

There is a reason the aggregate is a hash of the identifiers and not simply a count of them, and it is the average's defect wearing different clothes. A count is a function of how many entries a slice holds. Orphans do change that number, so a count catches them, as long as nothing else moved in the same slice. Delete one chunk and create a different one in the same tenant on the same day and the count is identical, while the set of identifiers is not, and the orphan is still sitting there. The count has held still while the dangerous property moved. A hash of the identifiers is a function of which entries a slice holds rather than of how many, so it changes whenever the membership changes, and that is the property that makes it usable as a check at all.

Repair has two directions and they are not symmetric. An entry present in the vector store and absent from Postgres is deleted, and that is safe because Postgres is authoritative. An entry present in Postgres and absent from the vector store is re-enqueued through the outbox, which is safe because the outbox is idempotent on version. What you must not do is delete from Postgres to make the sides agree, and the reason it is worth stating is that a repair script written at 02:00 with a generic "sync these two collections" shape can absolutely be pointed the wrong way.

The shape of a cutover that has a way back#

The shadow-read phase is the part most plans omit and the part that produces the evidence. Both stores are queried for real production traffic, the old store's results are served, and the comparison is recorded asynchronously. That gives overlap measured on the actual query mix rather than on a probe set somebody assembled, at production concurrency, without any user seeing a result from the untested system. It costs double query load for the duration, which is a real expense and a cheaper one than discovering the filtered stratum after the flip.

The ramp in that diagram reads better as a set of steps than as four levels. Each stage multiplies the exposed traffic by a smaller factor than the stage before it — roughly ten-fold, then five-fold, then a doubling — so the early steps buy the largest proportional increase in exposure for the smallest absolute number of affected requests. That is where query shapes rare enough to be absent from any probe set get their first real traffic, while the population standing behind them is at its smallest. The later steps add far more absolute traffic for a smaller multiple, and what they are mainly putting under test is whether the new store holds up at a concurrency it has not seen.

Agree the criteria before, because afterwards they are negotiable#

Three numbers should be written down and signed off before the first line of migration code is written, for a reason that has nothing to do with rigour and everything to do with what happens to standards under schedule pressure. A team four weeks into a migration that has already been announced will look at an overlap of 0.91 against a target nobody set and conclude it is fine. The same team looking at 0.91 against a 0.95 they agreed to in week zero will investigate.

The three are a minimum top-k overlap against the old store, stated per stratum; a p95 bound at production concurrency, taken from the SLO in the decision record rather than invented; and a count check. The count check has a trap in it worth naming: a global count comparison passes when 30,000 rows are missing for one tenant and 30,000 orphans exist for another, and both of those are incidents. That is the same cancellation the identifier hash was introduced to survive, appearing here at the coarsest possible grain, and the remedy is the same one — group the comparison so that equal-and-opposite errors land in different buckets. Compare counts grouped by tenant, or by day of creation.

-- Per-tenant count check, run against both stores and diffed.
SELECT d.workspace_id, count(*) AS chunk_count
FROM document_chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.archived_at IS NULL
GROUP BY d.workspace_id
ORDER BY d.workspace_id;

Migrating the store and the model in one window#

None of this addresses schema or model changes made during the migration. Cutting over a store while also switching embedding models means every discrepancy has two possible causes and you cannot attribute any of them, so the two changes belong in separate windows even though doing them together looks like it saves a backfill. Attribution collapses because both changes move the same measurement. A top-k overlap between the old and the new store is only interpretable while the vectors on the two sides are supposed to be identical; change the model and they are supposed to differ, by an amount nobody has measured. An overlap under target then has two readings — the new store is losing neighbours, or the new model ranks differently — and no experiment run afterwards separates them, because the only clean control is the configuration you have just stopped running.

Drift after the cutover#

It is also not a data-loss guarantee. Everything above reduces the probability of drift and gives you the means to detect it; none of it makes the two systems consistent in any formal sense, because they have separate durability and there is no transaction spanning them. The absence of that spanning transaction is the whole of it. A transaction gives you atomicity over the things inside it, and the vector store is outside; the outbox restores coverage and ordering, and neither of those is atomicity, since the relay can succeed on one side, crash, and retry. Idempotent version-guarded writes make the retry safe, and safe is a weaker property than consistent — it says a repeated event does no harm, not that every event arrives.

The steady state after a successful cutover therefore still includes a reconciliation job, because drift accumulates from partial failures, retries, and the occasional bug, and the migration is where you build the tooling that will keep finding it for the next several years. Budget for it as permanent infrastructure rather than as migration scaffolding, since the alternative is deleting the only instrument that can see the failure this chapter is about.

The plan on the slide was incomplete in one direction, and the direction was predictable from the shape of the mechanism. A dual write replicates paths; creation has few and removal has many; most of the many never name what they took away; and the entry left behind is well-formed, so nothing raises. Moving the hook to the database makes coverage a property of the engine instead of a property of somebody's memory. A version stamp makes the backfill and the live stream agree about order rather than about arrival. A per-stratum overlap and a per-tenant identifier check replace two averages that could hold still while the thing underneath them moved. Keeping the old store written to is what keeps the rollback real.

And a clean cutover is not the same as being ready to run the thing. The plan above ends with a system serving all production reads from a datastore that the team has now used, in anger, for about three weeks — under normal conditions, with nothing broken.