Bulk Upsert Thousands of Rows with ON CONFLICT in PostgreSQL

Stream rows through COPY or multi-row INSERT with ON CONFLICT DO UPDATE, avoiding per-row round trips and deadlocks from unordered writes.

~30 mindatabasepostgresqlbulk-operations

Task

Replace a per-row UPDATE loop with a set-based upsert that stays deadlock-free and idempotent under re-runs.

When to use this

A nightly sync issues 80,000 single-row UPDATEs. Each run takes 40 minutes, occasionally deadlocks, and restarts the whole batch from zero.

Prerequisites

PostgreSQL 12 or newer
A target table with a unique key or primary key
A data source that can be exported to CSV or generated in memory

Steps

1. Confirm the conflict target is a real unique constraint.

psql -d app -c "SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'products'::regclass AND contype = 'u';"

Expected output: The unique constraint prints, for example products_sku_key UNIQUE (sku), which is the conflict target the upsert will name.

2. Stage the incoming rows in a temporary table via COPY.

psql -d app -c "CREATE TEMP TABLE stage_products (LIKE products INCLUDING DEFAULTS);" && psql -d app -c "\copy stage_products FROM 'sync.csv' CSV HEADER"

Expected output: Thousands of rows load in one command; the temp table lives only for this session and costs no catalog churn.

3. Upsert the whole stage in one statement.

INSERT INTO products (sku, name, price, updated_at)
SELECT sku, name, price, now()
FROM stage_products
ON CONFLICT (sku) DO UPDATE SET
  name    = EXCLUDED.name,
  price   = EXCLUDED.price,
  updated_at = EXCLUDED.updated_at
WHERE products.price IS DISTINCT FROM EXCLUDED.price
   OR products.name IS DISTINCT FROM EXCLUDED.name;

Expected output: One statement writes all rows; the WHERE clause skips no-op updates, so unchanged rows produce no dead tuples and no vacuum pressure.

4. Sort the input to make concurrent upserts deadlock-free.

psql -d app -c "INSERT INTO products (sku, name, price, updated_at) SELECT sku, name, price, now() FROM (SELECT * FROM stage_products ORDER BY sku) s ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price;"

Expected output: With both concurrent jobs locking rows in sku order, the classic AB-BA deadlock disappears even when two syncs overlap.

5. Verify idempotency by running the sync twice and counting changes.

psql -d app -c "INSERT INTO products ... ON CONFLICT ...; " -c "SELECT count(*) FROM products WHERE updated_at > now() - interval '1 minute';"

Expected output: The second run reports zero freshly updated rows beyond the first, proving re-runs are safe and the batch can resume after failure.

Troubleshooting

Problem

ON CONFLICT cannot find the constraint

Solution

The conflict target must match the constraint columns exactly in name and order; check pg_get_constraintdef output and spell the column list identically.

Problem

Deadlocks still appear with concurrent jobs

Solution

One writer is not sorting; ensure every code path orders by the conflict key, and add retry-on-deadlock as a belt-and-braces measure.

Problem

Copy fails on encoding or quoting

Solution

Export with the same encoding the server expects and use CSV with HEADER plus explicit quoting; or fall back to a parameterized multi-row INSERT in chunks of 500.

Limitations

ON CONFLICT DO NOTHING silently drops conflicting rows, which is right for dedupe and wrong for sync; choose per task.
Very large stages can bloat temp files; chunk the COPY if a single run exceeds a gigabyte.

Set-based upsert beats 80,000 round trips

A per-row loop pays connection overhead, round-trip latency, and a separate plan for every statement. Staging rows and upserting them in one command collapses that to a single plan and a single lock sequence, turning a 40-minute sync into seconds.

-- Per-row loop: 80,000 statements, 40 minutes, random deadlocks
-- Set-based: 3 statements, seconds, ordered locks
\copy stage_products FROM 'sync.csv' CSV HEADER

INSERT INTO products (sku, name, price, updated_at)
SELECT sku, name, price, now()
FROM (SELECT * FROM stage_products ORDER BY sku) s
ON CONFLICT (sku) DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  updated_at = EXCLUDED.updated_at
WHERE products.price IS DISTINCT FROM EXCLUDED.price;

Skip the no-op writes

The WHERE clause on DO UPDATE is the difference between rewriting every row and rewriting only changed rows. Unchanged rows skipped this way generate zero dead tuples, which keeps autovacuum quiet and keeps replication lag near zero during nightly syncs.

Idempotent means restartable

Because the statement matches on the conflict key rather than inserting blindly, running it twice produces the same end state. That property lets a failed batch resume instead of restarting, and makes the whole pipeline safe to retry after a flaky network.