Batch Upsert Patterns for pgvector

Row-at-a-time ingestion is the default that quietly caps a pgvector pipeline. Every autocommitted INSERT flushes WAL, re-descends the table’s indexes, and pays a full network round-trip, so a single writer stalls near a few hundred rows per second while the corpus grows into the millions. Reach for the obvious fix — one giant COPY — and you gain raw throughput but lose the one property an ingestion pipeline cannot live without: idempotency. Re-run yesterday’s batch after a partial failure and COPY happily duplicates every chunk, because it has no conflict handling at all. The engineering problem for bulk vector writes is reconciling three things that pull against each other: throughput, idempotent upsert semantics, and WAL/lock pressure on a shared cluster. This is a core write-side concern within Embedding Ingestion Pipeline Engineering, and it has a clean answer — stage with COPY, then merge with ON CONFLICT — that this page builds end to end with runnable SQL and both psycopg3 and asyncpg drivers.

Up: Embedding Ingestion Pipeline Engineering

Architectural Divergence & Trade-offs #

Three write strategies dominate bulk vector ingestion, and they occupy genuinely different points on the throughput/idempotency/WAL curve. Picking one is a decision about which property you are willing to trade.

Binary COPY is the fastest way to get vectors into PostgreSQL. COPY doc_embeddings FROM STDIN WITH (FORMAT BINARY) streams rows through a single dedicated protocol path, skips per-statement planning, and — critically — encodes each vector in its native binary layout instead of parsing a text literal like '[0.1,0.2,...]'. For a 1536-dimensional embedding that is the difference between shipping ~6 KB of raw float4 and parsing a ~20 KB ASCII string per row. The catch is total: COPY has no ON CONFLICT clause. It is an append. Feed it a doc_id/chunk_index that already exists and it either duplicates the row (no unique constraint) or aborts the whole batch on the first violation (unique constraint present). It is the right tool only for a first load into an empty or partition-fresh table where every key is guaranteed new.

Multi-row INSERT … ON CONFLICT DO UPDATE is the idempotent workhorse. A single statement carrying several hundred VALUES tuples with a conflict target of (doc_id, chunk_index) upserts cleanly: new keys insert, existing keys update in place, and a replay of the same batch is a no-op if you add a content_hash guard. The price is throughput. Every row is planned as an index probe against the arbiter constraint, and each affected row maintains all indexes on the table — including the HNSW or IVFFlat vector index, whose insert path is far heavier than a b-tree’s. Under churn this also generates dead tuples that autovacuum must later reclaim.

COPY into an UNLOGGED staging table, then a single set-based INSERT INTO target SELECT … ON CONFLICT DO UPDATE merge combines the best of both. The bulk landing is a binary COPY at full speed into a throwaway table that carries no indexes and, being UNLOGGED, writes no WAL. The merge is then one large, planned, set-oriented statement the executor can optimize as a whole, rather than N separate upserts. You keep idempotency (the ON CONFLICT lives on the merge) and pay index maintenance only once, in bulk, on the target. This is the default recommendation for any pipeline that re-ingests changed documents.

Two decisions sit above the table. First, normalize vectors before the write, not after: unit-normalizing in the pipeline lets you use the cheaper inner-product operator downstream and keeps the stored representation canonical, as detailed in normalizing embeddings before pgvector insertion. Second, the conflict key is a schema decision, not a write-time one — the (doc_id, chunk_index) identity and any content hash come from your metadata mapping and schema design, and getting it wrong turns every upsert into a silent duplicate.

Parameter Space & Diagnostic Workflow #

Batch writing has one dominant tunable — rows per batch — and several supporting knobs. Batch geometry is a three-way trade: larger batches amortize WAL flushes and round-trips (good), but hold locks longer and buffer more rows in memory (bad), and past a point return diminishing throughput while raising deadlock probability under concurrency.

The diagnostic that matters most for an upsert workload is whether the write actually did what you think. PostgreSQL exposes this through the system column xmax: on an INSERT … ON CONFLICT, a freshly inserted row has xmax = 0, while a row that took the DO UPDATE path has a non-zero xmax. RETURNING (xmax = 0) AS inserted lets the pipeline count inserts versus updates per batch and alert when a supposedly incremental load is rewriting everything:

WITH up AS (
  INSERT INTO doc_embeddings (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_embeddings.content_hash IS DISTINCT FROM EXCLUDED.content_hash
  RETURNING (xmax = 0) AS inserted
)
SELECT count(*) FILTER (WHERE inserted)      AS inserts,
       count(*) FILTER (WHERE NOT inserted)  AS updates
FROM up;

To see whether a running bulk load is generating pathological WAL or waiting on locks, watch the ingestion role in pg_stat_activity and the table’s churn in pg_stat_user_tables:

SELECT n_tup_ins, n_tup_upd, n_dead_tup,
       n_dead_tup::float / nullif(n_live_tup, 0) AS dead_ratio
FROM pg_stat_user_tables
WHERE relname = 'doc_embeddings';

A dead_ratio climbing above ~0.2 during ingestion means DO UPDATE churn is outrunning autovacuum — the source of index bloat discussed under Failure Modes.

Step-by-Step Implementation #

The following builds the recommended path — binary COPY into an UNLOGGED staging table, then a set-based merge — and shows the driver-native binary COPY calls for both psycopg3 and asyncpg.

Step 1 — Define the target with a conflict key and a content hash. Idempotency is a schema property. The primary key is the conflict arbiter; content_hash lets the merge skip unchanged chunks.

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

Step 2 — Binary COPY a batch with psycopg3. Register the vector adapter on the connection so pgvector.psycopg.register_vector teaches psycopg3 to encode a numpy array or list directly into the binary vector wire format. The copy() context manager streams rows without building one giant string.

import numpy as np
import psycopg
from pgvector.psycopg import register_vector

def copy_batch(conn: psycopg.Connection, rows: list[dict]) -> None:
    register_vector(conn)                       # binary vector adaptation
    with conn.cursor() as cur:
        with cur.copy(
            "COPY staging (doc_id, chunk_index, content_hash, embedding) "
            "FROM STDIN WITH (FORMAT BINARY)"
        ) as copy:
            copy.set_types(["text", "int4", "text", "vector"])
            for r in rows:
                copy.write_row((
                    r["doc_id"], r["chunk_index"], r["content_hash"],
                    np.asarray(r["embedding"], dtype=np.float32),
                ))

Step 3 — Or binary-load with asyncpg. asyncpg’s copy_records_to_table runs the binary COPY protocol natively. Register the vector type once per physical connection so the codec is installed, then hand it an iterable of tuples.

import asyncpg
from pgvector.asyncpg import register_vector

async def copy_batch_async(conn: asyncpg.Connection, rows: list[dict]) -> None:
    await register_vector(conn)                 # installs the vector codec
    await conn.copy_records_to_table(
        "staging",
        columns=["doc_id", "chunk_index", "content_hash", "embedding"],
        records=[
            (r["doc_id"], r["chunk_index"], r["content_hash"], r["embedding"])
            for r in rows
        ],
    )

Step 4 — Land into an UNLOGGED staging table, then merge. Create the staging table as UNLOGGED (or TEMP) with no indexes so the COPY in Step 2/3 pays neither WAL nor index cost. Then a single set-based statement moves everything into the target with conflict resolution.

CREATE UNLOGGED TABLE IF NOT EXISTS staging (
    doc_id       text         NOT NULL,
    chunk_index  int          NOT NULL,
    content_hash text         NOT NULL,
    embedding    vector(1536) NOT NULL
);
TRUNCATE staging;                       -- reuse across batches

-- ... COPY the batch into staging (Step 2 or 3) ...

INSERT INTO doc_embeddings AS t
      (doc_id, chunk_index, content_hash, embedding, updated_at)
SELECT doc_id, chunk_index, content_hash, embedding, now()
FROM   staging
ORDER  BY doc_id, chunk_index            -- deterministic lock order
ON CONFLICT (doc_id, chunk_index) DO UPDATE
   SET embedding    = EXCLUDED.embedding,
       content_hash = EXCLUDED.content_hash,
       updated_at   = now()
   WHERE t.content_hash IS DISTINCT FROM EXCLUDED.content_hash;

The ORDER BY inside the SELECT gives every concurrent merge the same lock-acquisition order, which is the single most effective deadlock defense (see Failure Modes). The WHERE t.content_hash IS DISTINCT FROM EXCLUDED.content_hash guard turns re-ingestion of unchanged chunks into a genuine no-op — no row version, no WAL, no index churn.

Step 5 — Session-level tuning for the bulk window. For a large one-shot load, widen the WAL and index-build budgets and relax commit durability for the ingest session only. These are safe precisely because the batches are replayable.

SET synchronous_commit = off;           -- no per-commit fsync stall
SET maintenance_work_mem = '1GB';       -- faster one-time index maintenance
-- max_wal_size is a server GUC; raise it in postgresql.conf before big loads

Batch geometry is chosen here: pick a rows-per-batch that keeps in-flight memory bounded (≈ rows × 6 KB for 1536-dim vectors) while still amortizing round-trips. One to five thousand rows is a sound starting point; the index build itself, if you defer it until after a first load, is governed by asynchronous index build strategies, which is nearly always cheaper than maintaining the vector index row-by-row during ingest. When the writer runs inside a concurrent async consumer pool, keep the transaction short and never straddle an embedding-API call, per async processing with Python asyncio.

Validation & Recall Testing #

A batch upsert can succeed at the protocol level and still be wrong — duplicated rows under a missing constraint, silent no-ops from a mis-scoped conflict target, or an update that rewrote every row because the hash guard was bypassed. Validate on three axes.

Insert-vs-update accounting. Use the xmax trick from the diagnostic section as a first-class metric per batch. On a steady incremental load you expect mostly updates for existing documents and a trickle of inserts; a batch that reports 100% inserts on a re-run means the conflict target is not matching (wrong columns, or a constraint that does not exist), and you are accumulating duplicates.

Row-count completeness. Every staged row must resolve to exactly one target row per key. Detect duplicates that would only exist if COPY had been used without a constraint:

SELECT doc_id, chunk_index, count(*)
FROM   doc_embeddings
GROUP  BY doc_id, chunk_index
HAVING count(*) > 1;

Any rows returned mean the uniqueness guarantee failed — usually a raw COPY path that skipped the merge.

Idempotency under replay. Run the identical batch twice and assert that the second run moves updated_at only for genuinely changed content. A second run that touches every row proves the content_hash IS DISTINCT FROM guard is not firing and you are burning WAL for nothing.

Plan verification. Confirm the merge itself is set-based and not degenerating into a per-row plan, and that the target’s vector index is used at query time rather than a sequential scan:

EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO doc_embeddings AS t (doc_id, chunk_index, content_hash, embedding, updated_at)
SELECT doc_id, chunk_index, content_hash, embedding, now() FROM staging
ON CONFLICT (doc_id, chunk_index) DO UPDATE
   SET embedding = EXCLUDED.embedding, updated_at = now();

A healthy plan shows a single Insert on doc_embeddings node fed by a Seq Scan on staging with a Conflict Resolution: UPDATE line — one set operation, not thousands. For retrieval quality after ingest, compare the stored vectors against a ground-truth set with a Python recall-at-k check that computes exact nearest neighbors in numpy and measures overlap with the index’s answers; a recall gap points back to the ingest layer (normalization or dimension drift), not the index.

Failure Modes & Gotchas #

• Deadlocks on concurrent overlapping upserts. Two writers that touch the same (doc_id, chunk_index) keys in different orders will deadlock as each holds a row lock the other wants. The fix is deterministic ordering: ORDER BY doc_id, chunk_index in every merge so all writers acquire locks in the same sequence. Partition batches by a hash of doc_id so distinct workers rarely touch the same keys at all.
• WAL and checkpoint storms. Thousands of small autocommitted upserts force frequent checkpoints and can saturate WAL, stalling the whole cluster. Batch writes, use the UNLOGGED staging landing (no WAL), and raise max_wal_size before a bulk window so PostgreSQL is not forced into a checkpoint mid-load.
• Index bloat from DO UPDATE churn. Each ON CONFLICT DO UPDATE creates a dead tuple; on a high-churn table the HNSW/IVFFlat index accumulates dead entries faster than default autovacuum reclaims them, inflating index size and slowing scans. Lower autovacuum_vacuum_scale_factor on the table and monitor n_dead_tup; the content-hash guard is your first line of defense because it prevents the update entirely when nothing changed.
• TOAST overhead on the write path. A 1536-dim float4 vector is ~6 KB — above the ~2 KB threshold at which PostgreSQL pushes values out of line into TOAST. High-dimensional vectors mean every insert also writes a TOAST tuple, roughly doubling the write amplification. COPY’s binary path minimizes the client-side cost, but the storage write remains; halving dimension width with halfvec where model quality permits is the durable mitigation.
• COPY with no constraint silently duplicates. COPY into a table lacking a unique constraint on the conflict key appends duplicates without error. Always define the primary key first; never use bare COPY as an incremental upsert.
• Registering the vector type on the wrong connection. Under a connection pool, register_vector must run on each physical connection; a pooled connection that never had it registered raises an adaptation error or writes a text literal that fails to parse. Wire registration into the pool’s connection-init hook rather than calling it once at startup.

Monitoring & Alerting Hooks #

Instrument both the write outcome and the database’s response to it. The most actionable application-side metric is the insert/update ratio per batch, exported straight from the RETURNING (xmax = 0) accounting so a load that flips to all-inserts (broken idempotency) or all-updates (churning unchanged rows) pages immediately.

On the database side, watch dead-tuple accumulation and WAL generation from the ingestion role:

SELECT relname,
       n_tup_ins, n_tup_upd, n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup, 0), 3) AS dead_ratio,
       last_autovacuum
FROM   pg_stat_user_tables
WHERE  relname IN ('doc_embeddings');

Alert when dead_ratio exceeds ~0.2 (autovacuum is losing to churn) and when last_autovacuum falls far behind a heavy load window. Track WAL pressure with the delta of pg_current_wal_lsn() over time, and lock contention with a standing query against pg_stat_activity filtered to wait_event_type = 'Lock' for the ingest application_name — a rising count there is the early signal of the deadlock/serialization pattern above. Finally, chart throughput as the delta of pg_stat_user_tables.n_tup_ins + n_tup_upd; a flat line while batches are still submitting means writes are blocked on locks or checkpoints, not merely slow.

FAQ #

Related #

• Async Processing with Python AsyncIO — the consumer pool whose write step these batch patterns implement
• Type Casting & Vector Normalization — canonicalizing vectors before they reach the upsert
• Metadata Mapping & Schema Design — where the conflict key and content hash are defined
• Asynchronous Index Build Strategies — deferring vector-index build cost off the ingest path
• Up: Embedding Ingestion Pipeline Engineering