3 Things I Wish I Knew About the Postgres MERGE Statement Before It Broke My Production Concurrency

Postgres 15’s MERGE statement is a beautiful lie designed to lure in developers who are tired of the proprietary syntax of INSERT ... ON CONFLICT. On the surface, it looks like a cleaner, more readable way to handle "upserts," but in a high-concurrency production environment, it can be a source of random "tuple already updated" errors and deadlocks that ON CONFLICT simply doesn't suffer from.

I learned this the hard way during a migration where I thought I was "cleaning up" our SQL to be more standard-compliant. Within an hour of deployment, our error logs were screaming. Here are the three things I wish I’d understood before I hit git push.

1. MERGE is a collection of actions, not an atomic "UPSERT"

The biggest misconception is that MERGE is just a fancy wrapper for INSERT ... ON CONFLICT. It isn't. In the Postgres world, ON CONFLICT is a specialized, highly optimized operation designed specifically for atomic upserting. MERGE, however, is a sequence of join-like operations that determine which "action" to take.

Because MERGE evaluates its conditions and then executes the appropriate command (INSERT, UPDATE, or DELETE), there is a tiny, microscopic gap between the "check" and the "act."

Look at this standard-looking block:

MERGE INTO user_scores AS target
USING (SELECT 101 AS user_id, 500 AS score) AS source
ON target.user_id = source.user_id
WHEN MATCHED THEN
  UPDATE SET score = source.score
WHEN NOT MATCHED THEN
  INSERT (user_id, score) VALUES (source.user_id, source.score);

If two transactions run this simultaneously for the same user_id, both might see that the row doesn't exist and both will try to INSERT. One will win; the other will throw a unique_violation error.

If you use INSERT ... ON CONFLICT, Postgres handles this "speculative insertion" internally. It locks the index entry and ensures only one succeeds while the other waits and then updates. MERGE doesn't have that same "wait and retry" logic built-in for the insert case.

2. The "Tuple to be updated was already modified" Error

In a heavy concurrent environment, MERGE loves to throw serialization errors. Specifically, you’ll start seeing this gem:
ERROR: tuple to be updated was already modified by an operation in the current transaction (or similar concurrent update errors).

This happens because MERGE doesn't always handle row-locking the way you'd expect during the "MATCHED" phase. If Transaction A is merging a row and Transaction B updates that same row before Transaction A finishes its action, Transaction A might fail rather than simply re-evaluating.

Contrast that with ON CONFLICT:

INSERT INTO user_scores (user_id, score)
VALUES (101, 500)
ON CONFLICT (user_id) 
DO UPDATE SET score = EXCLUDED.score;

With ON CONFLICT, Postgres is incredibly robust at waiting for the lock on that specific row to release and then proceeding with the update. It’s "battle-hardened" for concurrency. MERGE is much more sensitive to the isolation level and the state of the row during the join. If you're using READ COMMITTED (the default), MERGE can still feel like it's tripping over its own feet when multiple workers touch the same keys.

3. MERGE doesn't support the DO NOTHING grace of UPSERT

One of the most common patterns in data ingestion is "Insert this if it's new, otherwise do absolutely nothing."

In the old way, we do this:

INSERT INTO tags (tag_name)
VALUES ('postgres')
ON CONFLICT (tag_name) DO NOTHING;

This is fast, it returns immediately, and it doesn't bump the sequence of the primary key if nothing happens (usually).

In MERGE, the equivalent looks like this:

MERGE INTO tags t
USING (VALUES ('postgres')) AS s(tag_name)
ON t.tag_name = s.tag_name
WHEN NOT MATCHED THEN
  INSERT (tag_name) VALUES (s.tag_name);

Wait, where is the WHEN MATCHED THEN DO NOTHING? You just... omit it. But the behavior is different. The MERGE statement still has to perform a join between the source and the target table. If you're doing a bulk merge of 1,000 rows where 999 already exist, MERGE does the work of matching all 1,000 before deciding to only insert one.

While ON CONFLICT is optimized for the "path of least resistance," MERGE is optimized for complex logic (like deleting rows that *don't* match, which ON CONFLICT can't do).

When should you actually use MERGE?

I'm not saying MERGE is useless. It’s actually great for complex ETL (Extract, Transform, Load) processes where you need to do more than just an upsert.

Use MERGE if:
- You need to DELETE rows in the target table that aren't in the source.
- You have multiple WHEN MATCHED conditions with different filters.
- You're running big batch updates where concurrency isn't an issue (e.g., a nightly cleanup script).

Example of where MERGE shines:

MERGE INTO inventory i
USING incoming_shipment s ON i.product_id = s.product_id
WHEN MATCHED AND s.quantity = 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET quantity = i.quantity + s.quantity
WHEN NOT MATCHED THEN
  INSERT (product_id, quantity) VALUES (s.product_id, s.quantity);

The Final Verdict

If you are building a web app where multiple API workers might try to update the same user record at the same time, stay away from `MERGE`. Stick with INSERT ... ON CONFLICT. It is more concise, handles row-level locking more gracefully, and won't wake you up at 2:00 AM because of a race condition you thought was solved in the 90s.

MERGE is a Swiss Army knife. It’s got a lot of blades, but if all you need to do is drive a screw, use a screwdriver—use ON CONFLICT.