PostgreSQL ON CONFLICT vs MERGE for Idempotent Upserts

PostgreSQL 15 added SQL-standard MERGE, and the obvious question followed: should idempotent writes move to it from INSERT ... ON CONFLICT? For deduplication specifically the answer is usually no, and the reason is concurrency behaviour rather than syntax preference. This decision guide sits under database unique constraints & upserts.

Prerequisites. PostgreSQL 15 or later to run both forms, a unique constraint or exclusion constraint on the deduplication key, and the background from PostgreSQL unique constraints vs application-level checks.

---

Step 1 — The same write, both ways

-- Schema shared by both examples.
CREATE TABLE idempotency_keys (
    scoped_key  text PRIMARY KEY,
    fingerprint bytea       NOT NULL,
    state       text        NOT NULL DEFAULT 'pending',
    status_code smallint,
    response    jsonb,
    expires_at  timestamptz NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- A: INSERT ... ON CONFLICT — the PostgreSQL-native form.
INSERT INTO idempotency_keys (scoped_key, fingerprint, expires_at)
VALUES ($1, $2, now() + interval '24 hours')
ON CONFLICT (scoped_key) DO NOTHING
RETURNING scoped_key;
-- Zero rows returned  → the key already existed: read it and replay.
-- One row returned    → we own it: execute the handler.

-- B: MERGE — the SQL-standard form.
MERGE INTO idempotency_keys AS t
USING (VALUES ($1::text, $2::bytea)) AS s(scoped_key, fingerprint)
   ON t.scoped_key = s.scoped_key
WHEN NOT MATCHED THEN
    INSERT (scoped_key, fingerprint, expires_at)
    VALUES (s.scoped_key, s.fingerprint, now() + interval '24 hours')
WHEN MATCHED THEN
    DO NOTHING;

They read similarly. They behave differently the moment two sessions run them at once.

---

Step 2 — Behaviour under concurrent inserts

ON CONFLICT has special machinery for this: on hitting a conflicting tuple it waits for the other transaction, then re-reads and takes the conflict action rather than failing. MERGE has no such machinery — it evaluates its join once, and under READ COMMITTED two sessions can both conclude NOT MATCHED.

That is not a bug in MERGE; the SQL standard specifies it that way, and PostgreSQL’s documentation says so explicitly. It just makes MERGE the wrong default for a hot deduplication path, where concurrent duplicates are the normal case rather than an edge case.

# MERGE therefore needs the retry loop that ON CONFLICT does not.
from psycopg.errors import UniqueViolation

def register_with_merge(conn, scoped, fingerprint, attempts=3):
    for _ in range(attempts):
        try:
            with conn.transaction():
                conn.execute(MERGE_SQL, (scoped, fingerprint))
            return
        except UniqueViolation:
            continue           # a concurrent session won; re-read and replay
    raise RuntimeError("merge contention did not resolve")

---

Step 3 — RETURNING support

The deduplication flow needs to know whether it won the race, which means it needs a value back.

-- ON CONFLICT: RETURNING is supported and is the natural signal.
INSERT INTO idempotency_keys (scoped_key, fingerprint, expires_at)
VALUES ($1, $2, now() + interval '24 hours')
ON CONFLICT (scoped_key) DO NOTHING
RETURNING scoped_key;              -- 1 row = we won, 0 rows = replay

-- Need the existing row on conflict too? Force an update that changes nothing:
INSERT INTO idempotency_keys (scoped_key, fingerprint, expires_at)
VALUES ($1, $2, now() + interval '24 hours')
ON CONFLICT (scoped_key) DO UPDATE
    SET scoped_key = EXCLUDED.scoped_key       -- no-op, but makes the row visible
RETURNING scoped_key, state, status_code, response, (xmax = 0) AS inserted;

(xmax = 0) is the idiomatic PostgreSQL trick for distinguishing an insert from an update in a single statement: a freshly inserted tuple has xmax of zero, an updated one does not. It saves a round trip on the replay path.

MERGE gained RETURNING only in PostgreSQL 17, so on 15 and 16 you need a follow-up SELECT inside the same transaction — another round trip on the hot path.

---

Step 4 — The decision

Use ON CONFLICT for deduplication. It is one statement, one round trip, no error path under contention, and it tells you which branch fired. The portability argument is weak for a table that already depends on PostgreSQL-specific types and a partial index.

Use MERGE for batch reconciliation — a nightly job that inserts new ledger rows, updates changed ones and closes stale ones from a single staging query. That is what it is good at, and there the contention that makes it awkward here does not exist.

---

Step 5 — Verify under concurrency

import concurrent.futures as cf, psycopg, pytest

@pytest.mark.parametrize("trial", range(50))
def test_on_conflict_never_raises(trial, dsn):
    key = f"idem:v1:concurrency-{trial}"
    barrier = cf.ThreadPoolExecutor(max_workers=64)

    def attempt(_):
        with psycopg.connect(dsn) as conn, conn.cursor() as cur:
            cur.execute(
                """INSERT INTO idempotency_keys (scoped_key, fingerprint, expires_at)
                   VALUES (%s, %s, now() + interval '24 hours')
                   ON CONFLICT (scoped_key) DO NOTHING
                   RETURNING scoped_key""",
                (key, b"\x00" * 32),
            )
            return cur.rowcount

    results = list(barrier.map(attempt, range(64)))
    assert sum(results) == 1        # exactly one winner, and never an exception

# Same check from psql, 64 parallel sessions.
KEY="idem:v1:shell-probe-$RANDOM"
seq 64 | xargs -P 64 -I{} psql -qtA -c \
  "INSERT INTO idempotency_keys (scoped_key, fingerprint, expires_at)
   VALUES ('$KEY', '\\x00', now() + interval '24 hours')
   ON CONFLICT (scoped_key) DO NOTHING RETURNING 1;" | grep -c 1
# expect: 1

# The MERGE equivalent will print unique-violation errors to stderr — that is the point.

Confirm the index is actually used rather than a sequential scan, which would make the whole thing slow under load:

EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO idempotency_keys (scoped_key, fingerprint, expires_at)
VALUES ('probe', '\x00', now() + interval '24 hours')
ON CONFLICT (scoped_key) DO NOTHING;
-- expect: "Conflict Resolution: NOTHING" and "Conflict Arbiter Indexes: idempotency_keys_pkey"

---

Failure scenarios and debugging

---

SRE / observability checklist

1. idempotency_registration_outcome_total{outcome} — Counter split into won and replay. The replay share is the natural duplicate rate, typically 0.5%–2%.
2. idempotency_registration_duration_ms — Histogram of the single statement. Alert if p99 exceeds 15 ms; contention on the arbiter index shows here first.
3. pg_stat_user_tables.n_tup_ins versus distinct keys — A divergence means the conflict path is not firing and duplicates are being inserted under different keys.
4. pg_locks waits on the primary key index — Gauge of blocked sessions. A rising count means concurrent duplicates are arriving faster than the winner commits.
5. unique_violation_total — Counter parsed from the error log for the deduplication table. On ON CONFLICT this should be flat at zero; any value means a code path bypasses it.
6. pgstatindex(...).leaf_fragmentation — Gauge on the primary key. Above 30% means the key format is fighting the B-tree; see UUIDv4 vs UUIDv7 vs ULID.

---

Related

• Database Unique Constraints & Upserts — the parent page on constraint-backed deduplication and its guarantees.
• PostgreSQL Unique Constraints vs. Application-Level Checks — why the constraint, not a preceding SELECT, is what makes this safe.
• Handling Unique-Violation Races Under Serializable Isolation — what changes when the transaction runs at SERIALIZABLE.
• Redis vs PostgreSQL for Idempotency State — the prior question of which store should hold the key at all.