INSERT ON CONFLICT and MERGE Plans #

An ingestion service upserts 50,000 device readings per batch with INSERT … ON CONFLICT (device_id, ts) DO UPDATE. Batches that are mostly new rows take 900 ms; batches that are mostly duplicates of previous deliveries take 6 seconds, even though “nothing changes”. A reconciliation job written with MERGE fails outright with an error about a row being modified twice. Both behaviours are visible in the plans once you know which lines to read.

The general anatomy of write plans is in data modification query plans. This page covers the two statements that combine inserting and updating.

The Planner Condition #

INSERT … ON CONFLICT attempts each insert and uses a unique index or exclusion constraint — the arbiter — to detect a conflicting existing row:

MERGE (PostgreSQL 15+) is planned as a join between the source and the target on the ON condition. Each joined row is classified MATCHED or NOT MATCHED (and, from PostgreSQL 17, NOT MATCHED BY SOURCE), then routed to the first WHEN clause whose condition passes. The join is optimised like any other — hash, merge or nested loop — so source and target estimates drive the plan. A target row matched by more than one source row raises an error.

Upsert or merge? A decision based on the job. For concurrent inserts of individual rows or small batches keyed by a unique index, INSERT ON CONFLICT is safe under concurrency. For synchronising a whole source set against a target with inserts, updates and deletes, MERGE expresses all actions in one join. For deleting target rows missing from the source, MERGE with NOT MATCHED BY SOURCE requires PostgreSQL 17. What kind of write is it? concurrent row upserts INSERT … ON CONFLICT race-safe via arbiter index set sync: insert + update MERGE one join, many actions also delete missing rows MERGE … BY SOURCE PostgreSQL 17+ MERGE is planned as a join; ON CONFLICT checks an index per row

Annotated EXPLAIN Evidence #

BEGIN;
EXPLAIN (ANALYZE, WAL)
INSERT INTO readings (device_id, ts, value)
SELECT device_id, ts, value FROM incoming_batch
ON CONFLICT (device_id, ts) DO UPDATE SET value = EXCLUDED.value;
ROLLBACK;
Insert on readings  (actual time=6102.4..6102.4 rows=0 loops=1)
  Conflict Resolution: UPDATE
  Conflict Arbiter Indexes: readings_device_ts_key
  Tuples Inserted: 2104
  Conflicting Tuples: 47896
  WAL: records=241220 fpi=18204 bytes=31204880
  ->  Seq Scan on incoming_batch  (actual rows=50000 loops=1)
Execution Time: 6110.8 ms
-- 47,896 of 50,000 rows already existed and were updated to (mostly) the same value
-- each conflict: arbiter lookup + new row version + index entries + WAL

With a guard that skips unchanged values:

... ON CONFLICT (device_id, ts) DO UPDATE SET value = EXCLUDED.value
    WHERE readings.value IS DISTINCT FROM EXCLUDED.value;
Insert on readings  (actual time=842.6..842.6 rows=0 loops=1)
  Conflict Resolution: UPDATE
  Conflict Arbiter Indexes: readings_device_ts_key
  Conflict Filter: (readings.value IS DISTINCT FROM excluded.value)
  Rows Removed by Conflict Filter: 47102
  Tuples Inserted: 2104
  Conflicting Tuples: 794
  WAL: records=14410 fpi=2102 bytes=2804410
Execution Time: 848.3 ms
-- only 794 real changes written; WAL down by 91%

A MERGE:

Merge on accounts a  (actual time=4102.6..4102.6 rows=0 loops=1)
  Tuples: inserted=1204  updated=18402  deleted=312  skipped=180082
  ->  Hash Right Join  (actual rows=200000 loops=1)
        Hash Cond: (a.external_id = s.external_id)
        ->  Seq Scan on accounts a
        ->  Hash  ->  Seq Scan on account_feed s
-- skipped: joined rows with no WHEN clause applying (unchanged)
Lines specific to upserts and MERGE Four lines are annotated. Conflict Arbiter Indexes names the unique index used. Conflicting Tuples counts rows that hit an existing row. Rows Removed by Conflict Filter counts conflicts skipped by the DO UPDATE WHERE clause. The MERGE Tuples line counts rows per action, including skipped rows. Conflict Arbiter Indexes: readings_device_ts_key unique index detecting conflicts Conflicting Tuples: 47896 existing rows updated Rows Removed by Conflict Filter: 47102 unchanged conflicts skipped Tuples: inserted=1204 updated=18402 … MERGE action counts conflicts that rewrite identical values are pure overhead

Step-by-Step Resolution #

  1. Read the conflict ratio. Conflicting Tuples ÷ (Tuples Inserted + Conflicting Tuples). A high ratio with identical values means most work is rewriting unchanged rows.

  2. Add a conflict filter so identical rows are skipped:

    ON CONFLICT (device_id, ts) DO UPDATE SET value = EXCLUDED.value
WHERE readings.value IS DISTINCT FROM EXCLUDED.value

  3. Deduplicate the source batch. Duplicate keys within one statement raise “cannot affect row a second time” for ON CONFLICT DO UPDATE and “MERGE command cannot affect row a second time” for MERGE:

    SELECT DISTINCT ON (device_id, ts) device_id, ts, value
FROM incoming_batch ORDER BY device_id, ts, received_at DESC;

  4. Confirm the arbiter index is the one you expect — especially with several unique indexes or partial unique indexes on the table.

  5. For MERGE, tune the join. Analyze the source table, make sure the ON columns are indexed on the target when the source is small, and add AND conditions to WHEN MATCHED clauses so unchanged rows are skipped:

    MERGE INTO accounts a
USING account_feed s ON a.external_id = s.external_id
WHEN MATCHED AND (a.name, a.plan) IS DISTINCT FROM (s.name, s.plan) THEN
  UPDATE SET name = s.name, plan = s.plan
WHEN NOT MATCHED THEN
  INSERT (external_id, name, plan) VALUES (s.external_id, s.name, s.plan);

  6. Use ON CONFLICT for concurrent writers. MERGE does not use speculative insertion; concurrent MERGEs inserting the same new key can still fail on the unique index, whereas INSERT … ON CONFLICT handles that race.

WAL written per 50,000-row batch An upsert that rewrites every conflicting row writes 31 megabytes of WAL. With a conflict filter skipping unchanged values it writes 2.8 megabytes. A MERGE with an unchanged-row condition over the same data writes 3.1 megabytes. ON CONFLICT DO UPDATE 31.2 MB WAL + Conflict Filter 2.8 MB WAL MERGE with change check 3.1 MB WAL unchanged rows skipped means fewer versions, index entries and WAL

Before and After #

-- BEFORE: unconditional DO UPDATE
Insert on readings  Conflicting Tuples: 47896  WAL: bytes=31204880                         Execution Time: 6110.8 ms

-- AFTER: DO UPDATE … WHERE value IS DISTINCT FROM EXCLUDED.value
Insert on readings  Rows Removed by Conflict Filter: 47102  Conflicting Tuples: 794  WAL: bytes=2804410   Execution Time: 848.3 ms

Why a conflicting row is expensive #

It is tempting to think of an upsert as “check, then maybe insert”. The speculative insertion mechanism is more careful, because two sessions may try to insert the same key at the same moment. Each row first checks the arbiter index; if no conflict is visible, the executor inserts the heap tuple and index entries while marking the insertion speculative, then confirms it. If a conflicting row appeared in between, the speculative insertion is killed and the statement proceeds to the conflict action. When the conflict is visible upfront, the path is shorter: lock the existing row, evaluate the DO UPDATE filter, and write a new version. Every step touches the unique index and the heap, so a batch that is 95% conflicts does roughly as much work as a batch of 95% plain updates. Skipping unchanged rows with a conflict filter is the only way to make duplicates cheap.

For bulk loads where the incoming data is mostly new, loading into a staging table and using MERGE or an anti-join insert — INSERT … SELECT … WHERE NOT EXISTS — avoids per-row speculative insertion entirely, as long as no concurrent writer inserts the same keys. The ORM-side patterns for these statements are in ORM bulk insert and upsert plans.

Common Pitfalls #

Rewriting identical rows on conflict. Every conflict creates a new version and WAL. Diagnostic signal: high Conflicting Tuples for re-delivered data. Fix: a WHERE … IS DISTINCT FROM EXCLUDED… conflict filter.

Duplicate keys in one batch. The statement fails. Diagnostic signal: “cannot affect row a second time”. Fix: deduplicate the source first.

Partial unique index not inferred. ON CONFLICT (cols) does not match a partial index without the same WHERE. Diagnostic signal: error that no unique constraint matches. Fix: add the index predicate to ON CONFLICT … WHERE.

MERGE under concurrent inserts. Two sessions can both see NOT MATCHED. Diagnostic signal: unique violations from concurrent MERGE jobs. Fix: INSERT … ON CONFLICT for concurrent paths, or serialize the jobs.

Frequently Asked Questions #

What does Conflicting Tuples mean in EXPLAIN ANALYZE? #

It counts the rows whose insertion found an existing row through the arbiter index and were handled by the ON CONFLICT action instead. With DO UPDATE, each of those rows was updated unless a conflict filter removed it.

Is MERGE faster than INSERT ON CONFLICT? #

For synchronising large sets, MERGE can be faster because it is planned as one join rather than a per-row index check. For concurrent single-row upserts, INSERT ON CONFLICT is the safer choice because it handles races on the unique key.

Why does my upsert fail with “cannot affect row a second time”? #

The source contains the same conflict key more than once in one statement. PostgreSQL refuses to update the same row twice in a single command; deduplicate the input first.

Up: Data Modification Query Plans