An idempotent write can still overwrite fresh content with stale

An idempotent write is one that can be repeated without changing what is stored: run it once or run it five times and the result is the same. Deterministic chunk keys give a pipeline that property for free. A chunk key computed from the document's source, its identifier and the chunk's position — the scheme chapter 2 built — is the same string every time the same document is processed, so an upsert, meaning a write that inserts a row or replaces whatever already sits at that key, turns a second delivery of a document into an overwrite of its own rows with identical content. At-least-once delivery stops being a problem the moment the keys stop moving.

Idempotence is a statement about repetition, and it carries no statement about order. Two deliveries of one document that carry different content also land on the same keys, and the delivery that arrives second wins, whether or not it is the one the source published second. That is the failure this chapter is about, and it is worse than the duplicate it replaced, because a duplicate is visible in the index and a stale overwrite is not. Every component reports success, the keys are stable, the upsert is idempotent, and retrieval now serves text the source has already replaced.

Why a stable key makes a late write destructive#

The step worth slowing down on is why one property produces both outcomes. A deterministic key is a function of the document's identity and the chunk's position, and of nothing else. Neither of those two inputs changes when the document's content changes. So revision 6 and revision 7 of one page compute the same key for their first chunk, the same key for their second, and so on down the document. Writing revision 7 puts revision 7's text at those keys. Writing revision 6 afterwards puts revision 6's text at exactly the same keys. The write path has no information with which to object, because the only thing it compares is the key, and the key is identical in both directions.

That is the shape of the mistake this chapter exists to name, and it is not a slip in reasoning. It is a belief that a defence is working. The defence is idempotence, the evidence for it is that redelivery stopped corrupting the index, and the evidence is real. What makes the belief dangerous is that the quantity being held constant — the chunk key — stays constant precisely while the property that matters varies. The key is the same for a redelivery of revision 7, where sameness is what saves you, and the same for a late delivery of revision 6, where sameness is what destroys you. A reading that never moves while the danger moves is not a guard.

The resulting state is stable too, which is what separates this from an ordinary transient. Nothing in the incremental path will correct it, and the reason runs through four links. The cursor — the high-water mark recording how far a connector has read — has already moved past the moment revision 7 was published, so a later poll asks for changes after that moment and revision 7 is not in the answer. The document has not changed at the source since, so it will not enter a later answer either. If the pipeline keeps a document hash to skip unchanged work, that hash now records revision 6 as the last thing ingested, which means a forced re-poll returning revision 7 would compare hashes, find a difference, and fix it. A forced re-poll, though, only happens if somebody edits the page again. The index will therefore serve superseded text until the next revision, whenever that is, and the only signal available is a human noticing that an answer disagrees with the source.

Order, and the four ways a pipeline loses it#

Chapter 1 arranged for documents to be delivered at least once and chapter 2 arranged for repeated deliveries to be harmless. Neither of them said anything about the order in which deliveries arrive, and there are at least four mechanisms in a normal pipeline that reorder them.

Retries with backoff hold an old payload while newer ones flow past: a failed message waits out its delay in a retry queue, and everything that succeeds during that delay reaches the index ahead of it. Multiple consumers pulling from the same queue process partitions concurrently, so two revisions of one document can be in flight on two pods at once, and which of them commits last is settled by scheduling rather than by publication order. A dead-letter replay, which chapters 5 and 6 build, deliberately reintroduces payloads that are hours or months old. A backfill, meaning a bulk pass that rewrites documents already in the index rather than reacting to a change at the source, runs alongside the incremental connector, reads a snapshot taken at the start of the run, and writes it over documents the connector has since updated.

Separating what is forced from what was chosen makes the guard easier to argue for. Retry is forced: a pipeline that guarantees delivery has to hold a failed payload and try it again, and holding it is what makes it late. The other three are design decisions. Running several consumers buys throughput and admits concurrent revisions of one document. Keeping a dead-letter queue buys a second attempt at failed work and admits payloads of any age. Running a backfill buys a re-embedding or a re-chunking of material already indexed and admits a snapshot writing over live edits. Each of the three is worth having, and each widens the window a guard has to close, which makes the guard a consequence of those decisions rather than an alternative to them.

The write path therefore has to decide, on every write, whether what it is about to store is newer than what is already there. That decision needs a comparable version, and the choice of version signal determines whether the guard actually holds.

Choosing a version signal#

The candidates differ in whether they can be ordered at all: an etag is the opaque string a source returns to stand for the exact bytes of a revision, so two etags can be compared for sameness and never for age, while a version number or a timestamp answers which of two revisions came later.

An analogy helps with that distinction, and its limit is worth stating before it is used. Two etags are like two sealed envelopes: you can tell whether you are holding the same one twice, and nothing about which was sealed first. The analogy breaks at the point that matters most here, because a real envelope arrives, and arrival is itself an ordering — which is exactly the fallback the last row below supplies, and exactly why that row orders the wrong thing.

Read that table down the Comparable column rather than across the rows. Three of the five signals answer "which of these two is newer" and two of them do not, and the gap between those two groups is the whole of the guard. A content hash and an etag both detect that something changed, and neither supplies a direction, so a guard built on either can refuse a write that is identical and cannot refuse a write that is old. That is the exact inversion of what is wanted, since the identical write is the harmless one.

The last row is the one worth understanding, because it is what you fall back on when the source gives you nothing better than an etag. An ingest sequence — a monotonically increasing integer from a Postgres sequence, or the broker offset of the message that carried the document — orders the moments at which your pipeline fetched content, not the moments the source changed it. That is exactly the ordering you want for the retry case, because a payload that failed was fetched before the payload that overtook it, whatever order the two of them finish in. A fetch time is fixed at fetch and a retry cannot change the one it is already carrying, so refusing to write a lower fetch sequence over a higher one blocks the stale write. It is the wrong ordering for a concurrent-fetch case where two pods fetch the same document milliseconds apart and the older content happens to be assigned the higher sequence, which is rare enough to accept and worth knowing about.

Name that residual rather than leaving it implied. An ingest sequence orders arrivals, the thing worth ordering is revisions, and the two agree only where fetching is what serialises them. Retry preserves the agreement because the fetch happened once, before the failure. Concurrent fetching breaks it because two fetches of one document are ordered by whichever pod got there first, and that has nothing to do with which revision each pod received.

The signal most teams reach for first is the source updated_at, and it is the one most likely to embarrass you. A wiki served by six application nodes with NTP drift of ±400ms will occasionally stamp a later revision with an earlier timestamp. A source with second-granularity timestamps produces ties on any document edited twice within the same second, and a guard written as > silently drops the second edit while a guard written as >= lets stale writes through. Where updated_at is the only option, pairing it with the content hash as a tiebreak — accept an equal timestamp only if the hash differs, and record which one won — turns a silent wrong answer into a rare and inspectable one.

The two failures in that sentence are not symmetrical, and the whole asymmetry is one character. Under > an equal timestamp loses, so the second edit inside one second is dropped and the index keeps the first: a lost update, permanent, with no error raised anywhere. Under >= an equal timestamp wins, so any redelivery of the earlier of two same-second revisions overwrites the later one, and that can fire again on every redelivery. Whichever way the comparison is written, the timestamp's resolution is the ceiling on what the guard can tell apart, and no care taken over the comparison raises that ceiling.

There is a way to remove one of the four reordering mechanisms rather than defend against it, which is to partition the work by document. Publishing ingestion messages to Kafka with doc_id as the partition key puts every revision of a document on one partition, consumed by one consumer, in offset order, so two revisions of the same page can never be in flight concurrently. That is a real improvement and teams that have it sometimes conclude they do not need a version guard.

The belief has the same shape as the last one. Partition assignment is a function of doc_id, so it holds constant across every revision of a document, and a constant that never moves reads as a guarantee. What varies is whether a payload travelled through that partition at all. Partitioning does not survive the retry case, because a message that fails and is republished to a retry topic re-enters at the tail while the partition has moved on; it does not survive a dead-letter replay, which is a different topic entirely; and it does not survive a backfill process that was never publishing through the broker in the first place. Partitioning narrows the window and the guard closes it, so the two are worth having together and only one of them is sufficient alone.

Where the guard has to live#

Most vector indexes do not offer a conditional upsert. Pinecone, Qdrant and pgvector-as-a-plain-table will happily write whatever you send them, and the ones that do offer optimistic concurrency generally scope it to a single point. The arrangement that follows is forced by that. A conditional write has to be taken inside one transaction that can compare and reject atomically, and a vector store cannot give you one. The practical arrangement is to keep a document-level row in a transactional database that acts as the arbiter, take the decision there, and only touch the vector index once the decision has been made in your favour.

-- documents(doc_id text primary key, source_version bigint, ingest_seq bigint,
--           chunk_count int, generation bigint, content_hash text, updated_at timestamptz)

INSERT INTO documents (doc_id, source_version, ingest_seq, chunk_count, generation, content_hash)
VALUES ($1, $2, nextval('ingest_seq'), $3, nextval('doc_generation'), $4)
ON CONFLICT (doc_id) DO UPDATE
SET source_version = EXCLUDED.source_version,
    ingest_seq     = EXCLUDED.ingest_seq,
    chunk_count    = EXCLUDED.chunk_count,
    generation     = EXCLUDED.generation,
    content_hash   = EXCLUDED.content_hash,
    updated_at     = now()
WHERE EXCLUDED.source_version > documents.source_version
RETURNING generation, chunk_count;

Read that statement in the order the database evaluates it. The INSERT supplies a candidate row. ON CONFLICT (doc_id) catches the case where a row for this document already exists, which for anything after the first ingest is every case. DO UPDATE then proposes new values, EXCLUDED being the name Postgres gives to the row that was offered and turned away by the conflict. The WHERE clause on the DO UPDATE is the guard, and it is evaluated last: the update happens only if the proposed source_version is strictly greater than the one already stored.

When revision 6 arrives after revision 7, the statement matches the conflict, evaluates 6 > 7 as false, updates nothing and returns zero rows. RETURNING returns a row only where a row was actually written, so an empty result is the signal to the writer. The writer sees that empty result and skips the index write entirely, which means the stale embedding work is wasted and the stale content never reaches retrieval. Logging that skip with both versions is worth doing, because a steady trickle of skipped writes is normal and a sudden burst of them usually means a backfill is racing the incremental connector.

Two properties hold this together that are easy to take for granted. The comparison and the write are one statement, so no second writer can read the stored version, be told it is stale, and be overtaken in between. And the row being compared is the row being updated, so the lock the database takes in order to update it is the same lock that protects the comparison. Splitting this into a SELECT and a later UPDATE gives up both properties, and the window that opens is precisely the one the guard was written to close.

A document write is a write of a set#

The generation column is the second guard, and it addresses a different failure. A generation here is a number that increases on every accepted write of a document and is stamped onto each chunk that write produces, so the rows belonging to the current revision can be told apart from rows an earlier revision left behind. Topic 8.17 established what happens when a revision produces fewer chunks than the one before it: upserting chunks 0 through 4 over a document that previously occupied 0 through 8 leaves four rows holding the old text, unreferenced by the writer and fully retrievable. From the ingestion side the obligation is that a document write is a write of a set of chunks, not of a list of rows, so the writer has to account for the rows it is no longer writing.

The count of what gets left behind closes exactly, and the arithmetic is worth doing because two different repairs both rest on it. Chunk positions run upwards from zero with no gaps, so a revision occupying nine positions occupies zero through eight, and a revision occupying five occupies zero through four. The five positions the new revision writes are a subset of the nine the old one held, and they are the first five, because both revisions start counting at the same place. Subtract one set from the other and the remainder is the positions from five through eight, with nothing else in it and no residual. The number of orphans is the drop in chunk count, and it is only that.

Two mechanisms delete exactly those rows, and they share no step. The first filters on generation: every position the new revision wrote has just been stamped with the new generation by the upsert, so the rows still carrying an older generation are precisely the ones the new revision did not write. The second deletes by identifier: compute the chunk key for each position from the new count up to the old one, and delete those keys directly. One mechanism reads a metadata field and the other reconstructs a string, and they meet on the same rows. That agreement is what licenses choosing between them on the grounds of what your index supports rather than on the grounds of correctness.

The agreement carries three conditions, all worth checking before relying on it. Positions have to be dense and start at zero, or the identifier form deletes keys that were never written and misses ones that were. The recorded chunk count has to match what is actually stored, since the identifier form has no other source for its upper bound. And the generation form catches one thing the identifier form cannot: rows left behind by a write that crashed partway, which belong to no recorded count at all.

def write_document(conn, doc, chunks):
    row = conn.execute(UPSERT_DOCUMENT, (doc.id, doc.version, len(chunks), doc.hash)).fetchone()
    if row is None:
        metrics.increment("ingest.stale_write_skipped", tags={"doc": doc.id})
        return
    generation, _ = row

    index.upsert([
        {
            "id": chunk_id(doc.source, doc.id, i),
            "values": vectors[i],
            "metadata": {"doc_id": doc.id, "chunk_index": i, "generation": generation},
        }
        for i, _ in enumerate(chunks)
    ])

    # Only now remove what this revision no longer occupies.
    index.delete(filter={"doc_id": doc.id, "generation": {"$lt": generation}})
    conn.commit()

Writing before deleting matters for two separate reasons, and the second one is what makes the generation filter correct rather than merely convenient. Take the ordering the code uses. The upsert runs first, so by the time index.delete evaluates its filter, the positions the new revision wrote already carry the new generation and only the surplus positions are older. The filter therefore matches the surplus. Run the identical filter before the upsert and every row in the document is older, so it matches all of them and the document is deleted. The predicate is the same in both cases. The order is what makes it select four rows rather than everything.

The first reason is the one that shows up in latency graphs. The reverse order — delete every chunk for the document, then write the new set — opens a window of a few hundred milliseconds during which the document is entirely absent from the index, and a query arriving in that window returns nothing at all for a document that exists. At 400 queries per minute and a 300ms window, a corpus being rewritten at 50 documents per second will land roughly one query per second inside somebody's deletion window. Writing first means the worst a query can see is a document whose chunks belong to two adjacent generations, which is a mixed but complete answer rather than an empty one.

The shape of that exposure is worth holding separately from any particular set of numbers put into it. Multiply the rate at which documents are being rewritten by the length of the window each rewrite opens, and the units cancel to a pure count: documents per second times seconds is documents, which is how many documents are sitting in a gap at any instant. Both terms are yours to set. The rewrite rate is a throughput decision, and the window length is a property of how the two calls are ordered — so reversing the order takes the second term to zero, which takes the product to zero whatever the first term is. That is why ordering the calls beats tuning either of them.

Where the index does not support a metadata filter on delete, the equivalent is an explicit range delete of chunk_id(source, doc_id, i) for i in [len(chunks), previous_chunk_count), which the chunk_count column exists to tell you. That form is cheaper — it deletes by ID rather than by scan — and it is fragile in exactly one way: if chunk_count in the documents table ever disagrees with reality, the orphans it fails to delete are invisible until someone runs a reconciliation.

Reading the skip counter#

The skip counter in that snippet is the only instrumentation this failure has, and it is worth wiring before you need it. A pipeline running the guard for the first time on an existing corpus typically reports a handful of skipped writes per thousand documents under normal operation — retries, mostly — and the number is stable enough that a tenfold jump is a meaningful alert. Tagging the counter with the reason the write lost, distinguishing "incoming version older" from "incoming version equal", separates ordinary redelivery from genuine reordering, and the equal case is the one that tells you whether your version signal has enough resolution to be doing any work at all. A guard on a second-granularity timestamp that reports 95% equal-version skips is not guarding anything; it is rejecting writes at random.

The reasoning under that last sentence is worth making explicit, because the counter looks busiest in exactly the wrong case. A skip logged as older means the guard compared two distinguishable versions and picked the right one. A skip logged as equal means the guard could not tell them apart and fell back on whichever way its tie-breaking rule points. The ratio between those two counts is therefore a measurement of the version signal's resolution rather than of the pipeline's health, and a signal with no resolution left produces a counter that is busy and a guard that is blind.

Ordering across documents, and across two stores#

A version guard does not order writes across documents, and nothing here attempts to. If a policy page and its addendum are edited together and land twenty minutes apart, retrieval will see an inconsistent pair in between, and the only fixes are transactional ingestion of a document group, which almost no source supports, or accepting the window.

The reason the guard cannot reach that far is that its comparison is scoped to one doc_id row, and no row represents the pair. Extending it would mean a version per group, which needs the source to have a notion of a group and to version the group as a unit — and a wiki that lets somebody edit two pages in one sitting usually has neither.

The guard also does not make ingestion transactional across the two stores: a crash between index.upsert and conn.commit leaves new chunks in the index under a generation the documents table does not know about, which the next successful write of that document cleans up, and which a periodic sweep for chunk generations exceeding the recorded one catches if that write never comes.

That sweep's direction can be read straight off the code, and it is the sort of detail that looks arbitrary until you do. The index write happens first and the database commit second, so a crash in between rolls the row back and leaves chunks carrying a generation higher than the one recorded — which is why the sweep looks for generations above the recorded number. Commit first and write the index second and the surviving damage would be the mirror image, a committed row claiming a generation no chunk carries, and a sweep pointed the same way would see nothing wrong at all.

The guards are also what make replay safe as an operation. Any procedure that reintroduces old payloads — draining a dead-letter queue, re-running a connector from an earlier cursor, restoring from a snapshot — is by construction a stale-write generator, and without a version guard those procedures are as likely to damage the index as to repair it. That is the difference between a replay you can run on a Friday afternoon and one that needs a maintenance window. Chapters 5 and 6 lean on this directly.

Two guards, then, and a stable chunk key supplies neither of them. Freshness is a comparison the write path has to make deliberately, against a signal that can say which of two revisions is newer, taken inside the same statement that performs the write. Completeness is a second comparison, between the set of chunks a revision produces and the set the previous revision left, resolved by writing the new set first and removing the remainder afterwards. A pipeline with deterministic keys and neither guard is not safe. It is quiet.

What none of this touches is the document that disappears. Every guard so far arbitrates between two candidate writes for a document that still exists at the source, and the incremental path only ever produces writes. A source that deletes a page produces no payload, no version, no hash and no arrival for the guard to arbitrate.