Idempotent Vector Upserts with ON CONFLICT

This page makes an embedding ingestion batch safe to re-run: applying the same batch twice must leave the table byte-for-byte identical, with no duplicate rows and no needless index churn. It covers the schema constraint that anchors idempotency, the ON CONFLICT DO UPDATE clause that upserts on it, a content_hash guard that skips unchanged chunks, and the xmax trick that tells inserts from updates in the same round-trip.

Up: Batch Upsert Patterns for pgvector

Idempotency is not a property of your Python loop — it is a property of a UNIQUE constraint plus a conflict-aware write. Without a unique key on the logical identity of a chunk, a retried batch (a Celery redelivery, a resumed backfill, a webhook fired twice) appends a second embedding for the same document chunk, and your search index quietly accumulates duplicates that split recall and inflate storage. The fix is a stable conflict target — UNIQUE (doc_id, chunk_index) — and an INSERT ... ON CONFLICT ... DO UPDATE that converges every re-run onto the same row. Layer a content_hash comparison on top and you also stop re-embedding and re-indexing chunks whose text never changed, which is where most of the wasted write cost hides.

Prerequisites #

• PostgreSQL 15+ with pgvector 0.5+ (CREATE EXTENSION vector;).
• Embeddings normalized and cast at the boundary per normalizing embeddings before pgvector insertion, so an update writes a comparable vector.
• A stable logical identity for each chunk — here (doc_id, chunk_index). Choosing that key well is a schema decision covered in metadata mapping and schema design.
• A content hash available at ingestion (any deterministic digest of the chunk’s source text).
• For high-throughput bulk loads, pair this with the strategy comparison in batch upsert with COPY vs INSERT ON CONFLICT.

Step-by-step #

1. Anchor idempotency with a UNIQUE constraint #

The conflict target must be a real unique key. Declaring (doc_id, chunk_index) as the primary key (or a UNIQUE constraint) is what makes ON CONFLICT legal against those columns and what physically prevents a duplicate chunk from ever existing.

CREATE TABLE IF NOT EXISTS doc_chunks (
    doc_id       text         NOT NULL,
    chunk_index  int          NOT NULL,
    content_hash bytea        NOT NULL,
    embedding    vector(1536) NOT NULL,
    updated_at   timestamptz  NOT NULL DEFAULT now(),
    PRIMARY KEY (doc_id, chunk_index)
);

2. Upsert on the conflict target #

INSERT ... ON CONFLICT (doc_id, chunk_index) DO UPDATE converges a retried batch onto the same row. Reference the incoming values through the special EXCLUDED alias, which holds the row that would have been inserted.

INSERT INTO doc_chunks (doc_id, chunk_index, content_hash, embedding)
VALUES ($1, $2, $3, $4)
ON CONFLICT (doc_id, chunk_index)
DO UPDATE SET
    embedding    = EXCLUDED.embedding,
    content_hash = EXCLUDED.content_hash,
    updated_at   = now();

Run this twice with the same parameters and the table is identical after each run — that is the idempotency contract.

3. Skip unchanged chunks with a content_hash guard #

Re-embedding text that never changed wastes an embedding-API call and, worse, dirties an HNSW index for no semantic gain. Add a WHERE clause to the DO UPDATE so the update fires only when the incoming hash differs from the stored one. A matching hash leaves the existing row untouched — no write, no index maintenance, no updated_at bump.

INSERT INTO doc_chunks (doc_id, chunk_index, content_hash, embedding)
VALUES ($1, $2, $3, $4)
ON CONFLICT (doc_id, chunk_index)
DO UPDATE SET
    embedding    = EXCLUDED.embedding,
    content_hash = EXCLUDED.content_hash,
    updated_at   = now()
WHERE doc_chunks.content_hash <> EXCLUDED.content_hash;

Compute the hash from the source text before you spend money on the embedding, so unchanged chunks can be filtered even earlier in Python.

import hashlib

def content_hash(text: str) -> bytes:
    return hashlib.sha256(text.encode("utf-8")).digest()

4. Distinguish inserts from updates with xmax #

Every row carries a hidden xmax system column: it is 0 for a freshly inserted tuple and non-zero once a transaction has updated it. Returning (xmax <> 0) tells you, per row and in the same round-trip, whether the upsert was an insert or an update — invaluable for ingestion metrics without a second query.

INSERT INTO doc_chunks (doc_id, chunk_index, content_hash, embedding)
VALUES ($1, $2, $3, $4)
ON CONFLICT (doc_id, chunk_index)
DO UPDATE SET
    embedding    = EXCLUDED.embedding,
    content_hash = EXCLUDED.content_hash,
    updated_at   = now()
RETURNING (xmax <> 0) AS was_update;

Wire it into the ingestion call to tally inserts versus updates:

async def upsert_chunk(conn, doc_id, chunk_index, chash, embedding) -> bool:
    was_update = await conn.fetchval(
        """
        INSERT INTO doc_chunks (doc_id, chunk_index, content_hash, embedding)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (doc_id, chunk_index)
        DO UPDATE SET embedding = EXCLUDED.embedding,
                      content_hash = EXCLUDED.content_hash,
                      updated_at = now()
        WHERE doc_chunks.content_hash <> EXCLUDED.content_hash
        RETURNING (xmax <> 0) AS was_update
        """,
        doc_id, chunk_index, chash, embedding,
    )
    # None => the WHERE guard skipped an unchanged row (no INSERT, no UPDATE)
    return was_update  # True: updated, False: inserted, None: skipped

Note the three-way outcome: False for a new insert, True for a real update, and None when the hash guard skipped the row entirely — because a suppressed DO UPDATE returns no row.

Parameter reference #

Verification #

The defining test of idempotency: apply the same batch twice and confirm the second pass adds no rows and updates nothing.

-- 1. count before, run the batch, count after — totals must match
SELECT count(*) AS total,
       count(*) - count(DISTINCT (doc_id, chunk_index)) AS dup_keys
FROM doc_chunks;

dup_keys must be 0 on every run. To prove the hash guard suppressed writes, re-run the exact batch and check that no updated_at moved:

-- after a no-change re-run, nothing should have been touched recently
SELECT count(*) AS touched_rows
FROM doc_chunks
WHERE updated_at > now() - interval '1 minute';

A touched_rows of 0 after re-running an unchanged batch confirms the content_hash guard turned every conflict into a genuine no-op. In Python, sum the was_update return values: an all-None result on a repeat run is the same proof from the client side.

Troubleshooting #

• there is no unique or exclusion constraint matching the ON CONFLICT specification. Symptom: the upsert errors at plan time. Diagnostic: \d doc_chunks shows no UNIQUE/PRIMARY KEY on the conflict columns. Fix: add PRIMARY KEY (doc_id, chunk_index) or a matching UNIQUE constraint; ON CONFLICT cannot infer an arbitrary index.
• A partial unique index is silently ignored. Symptom: conflicts are not caught even though a unique index exists. Diagnostic: the index has a WHERE predicate. Fix: either restate the same predicate in the statement as ON CONFLICT (doc_id, chunk_index) WHERE <predicate>, or use a full (non-partial) unique constraint as the conflict target.
• NULLs in the conflict key create duplicates. Symptom: rows you consider the same coexist. Diagnostic: chunk_index (or doc_id) is NULL, and standard NULLs never compare equal, so ON CONFLICT sees no conflict. Fix: make both key columns NOT NULL, or on PG15+ declare the constraint UNIQUE NULLS NOT DISTINCT if a nullable key is unavoidable.
• Duplicate chunk_index within one batch aborts the statement. Symptom: ON CONFLICT DO UPDATE command cannot affect row a second time. Diagnostic: the same (doc_id, chunk_index) appears twice in one multi-row statement. Fix: deduplicate the batch in Python (or DISTINCT ON in a staging merge) before the upsert, keeping the last occurrence. Chunk-boundary drift is a common source — track it via handling metadata drift during vector ingestion.
• Every re-run bumps updated_at even for unchanged text. Symptom: index churn and misleading freshness timestamps. Diagnostic: the WHERE content_hash <> EXCLUDED.content_hash guard is missing, or the hash is computed over text that includes volatile metadata. Fix: add the guard and hash only the stable source text, excluding timestamps or run IDs.

Related #

• Batch Upsert Patterns for pgvector — the ingestion stage these upserts belong to
• Batch upsert with COPY vs INSERT ON CONFLICT — throughput of the write path that carries this idempotency contract
• Metadata mapping and schema design — choosing the stable logical key the conflict target relies on
• Normalizing embeddings before pgvector insertion — produce a comparable vector so an update is meaningful
• Up: Batch Upsert Patterns for pgvector