PostgreSQL MERGE vs INSERT ... ON CONFLICT: Picking the Right Upsert

The job that finally pushed me to MERGE was a nightly product-feed sync: roughly forty million rows from a warehouse extract, upserted into a pricing table, with one ugly extra requirement — rows that vanished upstream had to disappear here too. INSERT ... ON CONFLICT had carried the upsert half for years, but it has no vocabulary for "this row used to exist and now it should not". We ran a separate DELETE sweep with NOT IN against a staging table; it took eleven minutes, scanned forty million keys, and held locks deep into the morning traffic ramp. PostgreSQL 15's MERGE collapsed the whole thing into one statement, because the feed already carried a discontinued flag and a WHEN MATCHED arm can DELETE on a condition. The sync dropped from eleven minutes of lock pressure to about ninety seconds. I was delighted for exactly one night.

The second night, the sync died on unique-violation errors that INSERT ... ON CONFLICT had never once produced in three years. Same data, same keys, same table. That failure taught me more about the two statements than the documentation's feature matrix did, because it exposed what MERGE actually gives up to get its extra arms.

What does MERGE add over INSERT ... ON CONFLICT?

Three concrete things: multiple WHEN arms with different actions per row, a DELETE arm, and an arbitrary source query instead of a VALUES list. ON CONFLICT is strictly insert-or-update-or-nothing against one target row shape. MERGE is a join with side effects — it scans any source (a table, a subquery, a foreign table) and applies the first matching arm per row.

MERGE INTO pricing.feed_item p
USING staging.feed_extract f ON f.sku = p.sku
WHEN MATCHED AND f.discontinued THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET price = f.price, seen_at = now()
WHEN NOT MATCHED AND NOT f.discontinued THEN
  INSERT (sku, price, seen_at) VALUES (f.sku, f.price, now());

Two details in that shape matter. First, arm order is evaluation order: the discontinued DELETE must precede the generic WHEN MATCHED, or the update swallows every tombstone and the delete never fires. Second, the conditional NOT MATCHED arm keeps discontinued rows that never existed locally from being inserted just to satisfy symmetry. Neither idea is expressible with ON CONFLICT at all — the arbiter design has no room for "delete on match". If you are on row-level security, note that MERGE touches policies for every action it might perform, so a role that could insert and update but not delete will now fail the whole statement; that surprised us in staging and it is worth one line in your runbook.

Why does MERGE race where ON CONFLICT does not?

Because ON CONFLICT has an arbiter index and MERGE has nothing of the kind. ON CONFLICT (sku) performs a speculative insertion: concurrent inserters race inside the unique index itself, exactly one wins, and every loser is routed into DO UPDATE with the winning row locked and visible. Single row guaranteed, no error, no retry. MERGE is a plan, not an arbiter. It joins source to target under the statement's MVCC snapshot, and a row inserted by a concurrent transaction is simply invisible to that join. Two concurrent MERGE statements carrying the same new sku both find no match, both choose the INSERT arm, and one of them dies with SQLSTATE 23505 when the unique index catches the collision at insert time. Worse: if there is no unique constraint on the join key, both inserts succeed and you now own duplicates — the documentation warns about exactly this, and it means a unique constraint is not optional hygiene for MERGE, it is a precondition.

So concurrent MERGE means a retry loop on 23505 and 40001, plus disciplined batch ordering — I sort the staging feed by sku before merging, because multi-row MERGE batches touching the same keys in different orders are a deadlock generator. The honest fix for our sync was simpler: admit that a reconciliation job is inherently single-writer and serialize it with a session-level advisory lock. That is the architectural difference in one sentence. ON CONFLICT is built for many concurrent writers racing on the same keys; MERGE is built for one writer applying a complex changeset, and it punishes you for pretending otherwise.

What did PostgreSQL 17 add to MERGE?

RETURNING with the merge_action() function, and WHEN NOT MATCHED BY SOURCE — together they close the two gaps that remained after PG15. RETURNING merge_action() tells you per row whether it was inserted, updated, or deleted, which turns the sync into its own audit report. WHEN NOT MATCHED BY SOURCE THEN DELETE is true reconciliation: rows in the target that the extract no longer contains get removed without needing a discontinued flag or a separate sweep at all.

MERGE INTO pricing.feed_item p
USING staging.feed_extract f ON f.sku = p.sku
WHEN MATCHED THEN
  UPDATE SET price = f.price, seen_at = now()
WHEN NOT MATCHED BY TARGET THEN
  INSERT (sku, price, seen_at) VALUES (f.sku, f.price, now())
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
RETURNING merge_action() AS action, p.sku;

What PG17 did not change is the concurrency model. RETURNING and the new arm are plan features; the join-then-act structure and its MVCC blind spot are untouched. If anything, WHEN NOT MATCHED BY SOURCE widens the blast radius of running two reconciliations at once — two racing full-sync MERGEs still cannot see each other's in-flight work, so they die on 23505 over the same new rows and pile row-lock waits and deadlocks onto the same delete set. Serialize the job. The syntax got friendlier, the single-writer rule did not move.

Which upsert should your sync job actually use?

Here is the decision table I keep in prose, because every row of it is a production scar. Concurrent OLTP upsert on a natural key — session rows, idempotency keys, counters, webhook dedupe — is ON CONFLICT territory, full stop: race-free arbiter, no retry loop, lower latency. Batch reconciliation that must delete absent rows is MERGE territory, run as a single writer inside a maintenance window or behind an advisory lock. Need to know per row whether you inserted or updated on PG15 or 16? ON CONFLICT with RETURNING (xmax = 0) AS inserted is the old trick — it works because a freshly inserted tuple carries xmax zero, and it is an implementation detail rather than a contract, so treat it as a heuristic; on PG17, MERGE ... RETURNING merge_action() is the clean version. Need delete-absent semantics on PG15 or 16 without tombstone flags? Chain CTEs: a WITH gone AS (DELETE ... WHERE NOT EXISTS against the staging table RETURNING sku) feeding a plain INSERT ... ON CONFLICT for the upsert half — two statements' worth of work in one, each half race-defined.

My position after running both: for high-frequency upserts MERGE is the wrong tool no matter how nicely the syntax reads, and for reconciliation ON CONFLICT is the wrong tool no matter how safe it feels. The concurrency model is the feature, not the syntax. Pick the statement whose locking story matches your writer topology, then let the arms follow from that.

How do you watch upsert workloads with MonPG?

The failure modes of both statements surface in counters before they surface in errors. MERGE retries show up as a rising xact_rollback count in pg_stat_database; deadlocks from unordered batches appear in the deadlocks column of the same view; the delete arm generates dead tuples that autovacuum must chase, which is the same pressure loop described in the PostgreSQL vacuum guide. MonPG graphs exactly these series — commits against rollbacks, deadlocks, dead tuples and vacuum runs per table, plus per-statement latency from pg_stat_statements so you can watch the MERGE itself instead of inferring it from lock waits. That is how we caught the 23505 storm on night two: rollback rate tripled while commit rate stayed flat. If you run PostgreSQL upserts in production, MonPG's PostgreSQL monitoring puts those counters on one dashboard instead of leaving you to reconstruct the night from log files.