At 03:12 on a Tuesday I got paged for deadlocks on an ingestion service that had been boring for two years. The service merged webhook events into an order_events table with INSERT ... ON DUPLICATE KEY UPDATE, roughly 12,000 upserts a second at peak, and two separate fires were burning at once. The first was visible in SHOW ENGINE INNODB STATUS: about 180 deadlocks an hour during the batch window, always the same statement on both sides of the victim pair. The second fire was quieter and worse. The table's INT auto-increment counter was advancing close to 40,000 values a second on a table that gained maybe 200 new rows a second. The arithmetic said the counter would hit the signed INT ceiling in about eleven days, on a workload that was ninety-five percent updates.
Both fires traced back to the same statement. MySQL's upsert allocates an auto-increment value before it knows whether the row exists, and that value is gone even when the statement lands on the update path. And the duplicate check that decides which path to take takes row locks, which under the default REPEATABLE READ isolation can deadlock against concurrent upserts touching the same unique key. We widened the column to BIGINT and added retry logic to survive, but the incident pushed that workload to the top of our PostgreSQL migration list. There, ON CONFLICT solved the deadlock problem outright and traded the ID problem for a different discipline: getting the arbiter index right.
This article is the honest comparison I wish I had read before that Tuesday: where MySQL's upsert bites, what PostgreSQL's actually guarantees, and how to build idempotent writes on either engine without lying to yourself about the failure modes.
Why does ON DUPLICATE KEY UPDATE burn auto-increment IDs even when it updates?
Because InnoDB allocates the next auto-increment value before it discovers the duplicate, and a value handed to a statement is never returned to the counter, even when the statement ends up updating an existing row instead of inserting. The statement starts life as an insert; the duplicate is only discovered when the insert probe hits the unique index. By then the counter has already advanced, and there is no mechanism to roll it back, because rolling it back under concurrency would serialize every insert in the table.
On a healthy insert-heavy table this waste is invisible. On an upsert-heavy table it is the dominant behavior: if ninety-five percent of your statements land on the update path, the counter runs roughly twenty times faster than your row count. Our order_events table had 400 million rows and a counter at 1.9 billion. That is how you end up eleven days from exhausting a signed INT on a table that is not actually growing. Gaps in auto-increment values are expected and harmless by themselves; the failure mode is pure counter exhaustion, and the fix, widening to BIGINT, is an online-schema-change project on a big table that you would rather schedule than improvise.
The same allocate-first design creates a second quirk on the update path: LAST_INSERT_ID() returns nothing useful after an update, so client libraries report an insert id of zero for rows that very much exist. The standard workaround is to make the update clause feed the id back through LAST_INSERT_ID:
-- Returns the row id on BOTH the insert and the update path
INSERT INTO order_events (event_key, payload, updated_at)
VALUES ('evt_88412', '{"status":"paid"}', NOW())
ON DUPLICATE KEY UPDATE
payload = VALUES(payload),
updated_at = VALUES(updated_at),
id = LAST_INSERT_ID(id);Two footnotes worth knowing. The VALUES(col) syntax is deprecated since MySQL 8.0.20 in favor of row aliases (INSERT ... AS new ON DUPLICATE KEY UPDATE payload = new.payload), though the old spelling still runs everywhere I have tried it. And innodb_autoinc_lock_mode changes how bulk inserts reserve ranges, but it does not change the update-path waste; that is structural.
Why do concurrent MySQL upserts deadlock, and is REPLACE any safer?
They deadlock because the duplicate check takes locks, and REPLACE is not safer at all; it is the same lookup with a delete hidden underneath. Under the default REPEATABLE READ isolation, when an INSERT discovers a duplicate in a unique index, InnoDB locks the conflicting index record before it decides what to do next. Run thousands of concurrent upserts against the same unique key and those lock acquisitions interleave with the insert-intention locks of the inserts themselves, and the wait graph closes into cycles. MySQL's own documentation warns that INSERT ... ON DUPLICATE KEY UPDATE against concurrent sessions can deadlock. We lived in that warning: every deadlock in our incident had the same upsert on both sides.
On MySQL 8.0, performance_schema replaces the old innoDB lock monitor tables and lets you watch the waits form in real time:
SELECT r.trx_id AS waiting_trx,
r.trx_mysql_thread_id AS waiting_thread,
b.trx_id AS blocking_trx,
b.trx_mysql_thread_id AS blocking_thread
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks dl
ON dl.engine_lock_id = w.requesting_engine_lock_id
JOIN information_schema.innodb_trx r
ON r.trx_id = dl.engine_transaction_id
JOIN performance_schema.data_locks dl2
ON dl2.engine_lock_id = w.blocking_engine_lock_id
JOIN information_schema.innodb_trx b
ON b.trx_id = dl2.engine_transaction_id;The mitigations are unglamorous but effective: retry on error 1213 in the application, process batches in a consistent key order so lock acquisition order stops varying, and consider READ COMMITTED, which drops most of the gap-locking behavior that makes the cycles easy to form. None of these removes the need to understand what actually happened in a given incident; the MySQL deadlock postmortem workflow covers that reading of the deadlock log in detail.
REPLACE deserves its own paragraph because it looks like a friendlier upsert and is anything but. REPLACE is delete-then-insert: when the key exists, the old row is deleted and a fresh row is inserted. That means ON DELETE CASCADE foreign keys fire and child rows vanish silently; ON DELETE RESTRICT makes the REPLACE fail outright; both DELETE and INSERT triggers run; the auto-increment counter advances every single time; and any column you did not list reverts to its default instead of keeping its old value. After one incident where a REPLACE quietly cascaded away a few thousand child rows, we banned it anywhere a foreign key points at the table. ON DUPLICATE KEY UPDATE has quirks; REPLACE has appetites.
How does PostgreSQL's ON CONFLICT work, and why does it need an arbiter index?
PostgreSQL's upsert works by speculative insertion: it tentatively inserts the row, and only when a conflict with a unique index becomes visible does it divert to the conflict action. The arbiter index is how you tell PostgreSQL which uniqueness you mean. The columns in the conflict_target must match a unique index, and if no unique index covers exactly those columns, the statement fails with the error about there being no unique or exclusion constraint matching the ON CONFLICT specification. I hit that error on the first deploy of the migrated service: the unique index was on (tenant_id, event_key) and the statement said ON CONFLICT (event_key). No covering index, no inference, statement rejected. Annoying for an afternoon, but it fails loudly instead of silently doing the wrong thing, which is the right failure mode.
CREATE UNIQUE INDEX order_events_tenant_key_uq
ON order_events (tenant_id, event_key);
INSERT INTO order_events (tenant_id, event_key, payload, updated_at)
VALUES (41, 'evt_88412', '{"status":"paid"}', now())
ON CONFLICT (tenant_id, event_key)
DO UPDATE SET
payload = EXCLUDED.payload,
updated_at = EXCLUDED.updated_at
WHERE order_events.updated_at < EXCLUDED.updated_at;The mechanics worth internalizing: EXCLUDED is the row proposed for insertion, so the DO UPDATE clause reads like a merge between the existing row and the candidate. The WHERE clause on DO UPDATE is where you encode merge semantics; the one above makes the upsert safe against out-of-order event delivery by refusing to overwrite a newer row with an older payload. DO NOTHING skips conflicting rows entirely and takes no update lock. Partial unique indexes participate in inference too, but then the conflict_target must include a matching predicate, which is the second most common way to trigger the no-matching-constraint error.
One honesty note, because the marketing version of this comparison gets it wrong: PostgreSQL is not gap-free either. A speculative insertion that loses to a conflict still consumed its sequence value, so identity columns show gaps under heavy upsert load, exactly like InnoDB's counter. The practical difference is that PostgreSQL identity columns are typically BIGINT, which turns the waste from an eleven-day-to-exhaustion incident into arithmetic trivia. The discipline that actually changes is the arbiter index: PostgreSQL makes you say precisely which uniqueness you are merging on, and refuses to guess.
How do the two engines behave differently under high contention?
The short answer: MySQL serializes upserts with index-record locks that can deadlock, while PostgreSQL serializes them with waits that cannot. When a PostgreSQL session's speculative insert encounters a conflicting tuple from a transaction that is still in flight, it simply blocks until that transaction commits or aborts, then proceeds down the appropriate path. Sessions working on different keys do not interact at all, because there are no gap locks to overlap. The 03:00 deadlock storm we lived with on MySQL has no direct analog; the closest PostgreSQL experience is contention showing up as lock waits in pg_stat_activity, which is a latency problem, not a correctness lottery. The broader locking model is covered in the PostgreSQL locks and deadlocks guide.
The cost sheet still has entries on the PostgreSQL side, and ignoring them is how upsert workloads go bad a quarter after migration. Every DO UPDATE that fires is an update, and every update in PostgreSQL creates a new row version, so an upsert-hot table accumulates dead tuples at exactly the rate of its upsert traffic. Autovacuum has to keep up with that, which usually means per-table tuning rather than the defaults; the PostgreSQL vacuum guide covers the knobs. Second, PostgreSQL only guarantees clean ON CONFLICT behavior at READ COMMITTED. At REPEATABLE READ, concurrent conflicting upserts can surface serialization failures, so the application still needs retry logic, just for a different error code than MySQL's 1213. The honest summary is that PostgreSQL removes the deadlock class and the ID-burn urgency, and in exchange asks you to manage vacuum pressure and pick the right isolation level. I will take that trade every time, but it is a trade.
How do you build idempotent writes with idempotency keys in both engines?
The pattern is the same shape on both engines: a unique constraint on the idempotency key, an upsert that treats "already there" as success, and application logic that reads the outcome instead of assuming it. The engines differ in how cleanly the outcome is reported back. On PostgreSQL, DO NOTHING plus RETURNING gives you an unambiguous signal: a returned row means you performed the write, zero rows means someone already did.
INSERT INTO payment_events (idempotency_key, payload)
VALUES ('pay_9f2c-attempt-1', '{"amount": 4200}')
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING id;DO NOTHING is also the cheap option mechanically: no row version is created on the conflict path, so a retry storm does not generate vacuum work. On MySQL the equivalent uses a deliberately harmless update, and the signal comes from the affected-rows count: 1 means inserted, 2 means an existing row was changed, and 0 means the row matched and nothing changed.
INSERT INTO payment_events (idempotency_key, payload)
VALUES ('pay_9f2c-attempt-1', '{"amount": 4200}')
ON DUPLICATE KEY UPDATE
payload = VALUES(payload);Watch two traps on the MySQL side. First, the CLIENT_FOUND_ROWS connection flag changes the affected-rows semantics for matched-but-unchanged rows, so code that was correct against one client configuration can misread another. Second, resist INSERT IGNORE. It looks like the idempotent primitive you want, but it downgrades every error class to a warning: a duplicate key is silently skipped, and so is a truncated string or a bad type conversion. You lose the difference between a benign replay and your data not fitting the column, and that difference is usually the whole point. Whichever engine you are on, if the application needs the canonical row after the upsert, select it inside the same transaction rather than trusting whatever the upsert happened to do.
Watching upsert behavior with MonPG
Everything in this article is observable, and it should be observed, because upsert misbehavior is quiet until it is suddenly an incident. On the PostgreSQL side, the signals are lock waits in pg_stat_activity, dead tuples and vacuum lag on upsert-hot tables, and commit rates by database, and those are exactly the layers MonPG's PostgreSQL monitoring keeps history on, so the week an upsert workload doubles its update churn shows up as a trend instead of a surprise.
MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the counters from this incident are the ones it will surface: deadlock counts and lock-wait graphs from performance_schema, per-table auto-increment headroom so counter exhaustion is a dashboard panel rather than a pager event, and the transaction mix that tells you an upsert-heavy table is burning IDs twenty times faster than it grows. Until then, the MySQL monitoring page tracks where that work stands, and the queries above will hold the line. Measure the counter, not just the row count; that is the whole lesson of my 03:12 Tuesday.