Upsert

An upsert is a single write operation that inserts a record if no record with that identifier exists and replaces the existing one if it does.

The word is a contraction of update and insert, and it long predates retrieval systems: it comes from database work, where the same operation appears as a merge statement or an insert-on-conflict clause. Vector stores adopted both the operation and the name.

Its defining property is that the caller does not have to know whether the record is already present. One request covers both cases, and repeating it produces the same final state as issuing it once. That makes an ingestion job safe to re-run after a partial failure, which is why the operation is the default write in most vector-store interfaces rather than one option among several.

The identifier is what makes the operation possible, and supplying it is the caller’s responsibility. Given a genuinely new identifier every time, an upsert is indistinguishable from an insert, and re-ingesting a corpus produces a second copy of it rather than an updated one.

In practice

In a retrieval pipeline an upsert normally carries three things together: the identifier, the vector, and the metadata — commonly including the chunk’s text, its source document, and its position.

What happens to those parts on a repeat write is the operation’s most consequential detail. A replacing upsert discards the stored record and writes the new one, so any metadata field absent from the second write is gone. A merging upsert overlays only the fields supplied. Both behaviours exist under the same name, and the difference is invisible until a field disappears.

The identifier scheme decides what a re-ingestion does. Chunk identifiers derived from the source document and the chunk’s position let a revised document overwrite its own chunks. Randomly generated identifiers cannot, so the old chunks remain, and the index accumulates stale text that is still retrievable and still looks current. This is a frequent and quiet cause of a system answering from a superseded version of a document.

The operation also does not cover deletion. A document that shrinks from twelve chunks to eight leaves four orphans behind, because upserting the eight touches nothing else. Removing them is a separate operation, and in most vector indexes a deletion is recorded as a tombstone rather than performed immediately, with the space reclaimed only when the structure is rebuilt. See vector index.

Whether a write is visible to the next query is a separate matter from whether it was accepted. Indexes frequently acknowledge a write before the structure has been updated, so an upsert followed immediately by a search may not find what was just written.

Commonly confused with

Insert. Fails, or duplicates, when the identifier already exists. An upsert is defined by not failing. Interfaces that offer only upsert make the distinction unavailable, which means an accidental identifier collision silently destroys a record instead of raising an error.

Update. Requires the record to exist and does nothing — or errors — if it does not. The upsert is the union of the two operations, and describing an upsert as “an update” hides the case that actually matters.

Indexing. Often used for the whole ingestion process: loading, chunking, embedding, and writing. The upsert is only the final write. A statement that a document “has been indexed” may mean any of those stages completed.

Re-embedding. Producing new vectors because the embedding model changed. That invalidates every stored vector, so it is a full rebuild expressed as many upserts, not an incremental update. See embedding.

Idempotent. A property, not a synonym. An upsert is idempotent in its final state — issuing it twice leaves the same record. Whether the surrounding pipeline is idempotent depends on whether it generates the same identifiers on a second run.

Usage notes

Merge-versus-replace semantics differ between products and are easy to miss. The same call name can overlay fields in one system and replace the record in another. What a partial write does to unmentioned fields is worth confirming rather than assuming, since the failure surfaces as missing metadata rather than as an error.

Batch upserts are not necessarily atomic. A batch may partially succeed, leaving some records written and some not. Because the operation is idempotent, re-sending the whole batch is usually the resolution.

The word is pronounced and written inconsistently — upsert, up-sert, occasionally UPSERT after the SQL convention. All refer to the same operation.

“Upsert” is sometimes used for the whole ingestion call in client libraries that accept raw text, embed it internally, and write the result. That call performs chunking and embedding as well, so the word covers more of the pipeline than the database sense implies. See corpus.

See also

Vector index · Corpus · Chunk · Embedding