Upserts, MERGE and Transactions

Description

Reading data is forgiving: a slow or clumsy query still returns the right rows. Writing is where backends lose money. Most write bugs never show up in testing, because they need two requests at the same moment, or a crash halfway through:

• Two customers buy the last mug. Both requests read qty = 1, both see stock, both write qty = 0. Two sales, one mug.
• A double-clicked signup runs "is this email taken? no - insert" twice, and both inserts pass the check.
• A nightly catalog sync updates prices, inserts new products, then crashes before deactivating old ones - leaving a catalog that matches neither yesterday nor today.

The fixes all live in the database, and they're not hard once you've seen them. In order:

1. Doing the check and the change in one statement.
2. Upserts with INSERT ... ON CONFLICT.
3. MERGE, and writes that join other tables.
4. Transactions, savepoints and isolation levels.
5. Row locks, and job queues with SKIP LOCKED.

Examples use inventory, page_views, products, a price_feed and a jobs queue; every script ran in the sandbox on PostgreSQL 18.

Let the database do the arithmetic

The race above is a lost update: read a value into the app, compute, write the result back. Between the read and the write, someone else's write is overwritten:

Put the arithmetic and the condition in the UPDATE itself:

UPDATE inventory
SET qty = qty - 1
WHERE sku = 'MUG-01' AND qty >= 1;

UPDATE inventory
SET qty = qty - 1
WHERE sku = 'TEA-02' AND qty >= 1;   -- out of stock: updates 0 rows

Postgres locks the row it updates, so two concurrent decrements run one after the other - and the second re-checks qty >= 1 against the first one's result. The app learns the outcome from the row count: 1 means sold, 0 means out of stock. A CHECK (qty >= 0) is the backstop that turns any remaining bug into an error instead of negative stock.

Upserts: INSERT ... ON CONFLICT

"Insert, or update if it exists" as one statement:

INSERT INTO page_views AS v (page, views)
VALUES ('/pricing', 1), ('/docs', 1)
ON CONFLICT (page) DO UPDATE
SET views = v.views + EXCLUDED.views;

EXCLUDED is the row the insert proposed; the table's alias (v) is the row already there. Unlike "select, then insert or update" in the app, ON CONFLICT is safe when two sessions upsert the same new key at once: one inserts, the other waits and then updates. ON CONFLICT DO NOTHING skips existing keys instead - and its RETURNING returns nothing for them, so select the existing row separately if you need its id.

Two rules to know:

• The conflict target needs a matching unique index or constraint - ON CONFLICT (name) on a column without one fails: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification. (For a unique index on lower(email), the target is (lower(email)).)
• One statement can't update the same row twice:

INSERT INTO page_views AS v (page, views)
VALUES ('/docs', 1), ('/docs', 1)
ON CONFLICT (page) DO UPDATE
SET views = v.views + EXCLUDED.views;

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

Batches from queues and logs often repeat a key - aggregate them first (SELECT key, SUM(n) ... GROUP BY key) and upsert one row per key.

MERGE, and writes that join

MERGE (Postgres 15+) compares a source with a target and runs a different action for each case. Since Postgres 17 it can act on target rows missing from the source (WHEN NOT MATCHED BY SOURCE) and return what it did; since 18, RETURNING can read the old and new values:

CREATE TABLE price_log (action text, sku text, old_price numeric(10,2), new_price numeric(10,2));

WITH changed AS (
    MERGE INTO products p
    USING price_feed f ON f.sku = p.sku
    WHEN MATCHED AND p.price <> f.price THEN
        UPDATE SET price = f.price
    WHEN NOT MATCHED THEN
        INSERT (sku, name, price) VALUES (f.sku, 'New ' || f.sku, f.price)
    WHEN NOT MATCHED BY SOURCE THEN
        UPDATE SET active = false
    RETURNING merge_action() AS action, p.sku, old.price AS old_price, new.price AS new_price
)
INSERT INTO price_log SELECT * FROM changed;
SELECT action, sku, old_price, new_price FROM price_log ORDER BY sku;

MUG-01's price changed, CUP-04 is new, PEN-03 isn't in the feed so it was deactivated (its price didn't change), and TEA-02 matched with the same price, so no clause fired. Clauses are tried in order and the first whose condition holds wins - put the specific WHEN MATCHED AND ... before a catch-all WHEN MATCHED.

MERGE is for reconciling data, not for racing upserts: if two sessions MERGE the same new key at once, one fails with a unique violation. For concurrent "insert or update", use ON CONFLICT.

Plain UPDATE ... FROM other_table and DELETE ... USING other_table join inside a write, and RETURNING on any write hands back the rows it touched - with old and new in Postgres 18, an audit trail needs no second query:

CREATE TABLE stock_moves (sku text, old_qty int, new_qty int);

WITH moved AS (
    UPDATE inventory SET qty = qty - 2
    WHERE sku = 'MUG-01'
    RETURNING sku, old.qty AS old_qty, new.qty AS new_qty
)
INSERT INTO stock_moves SELECT * FROM moved;

Transactions

BEGIN ... COMMIT makes several statements one unit: they all take effect, or - on ROLLBACK or any error - none do.

BEGIN;
UPDATE inventory SET qty = qty + 10 WHERE sku = 'TEA-02';
UPDATE inventory SET qty = qty - 10 WHERE sku = 'MUG-01';   -- 5 - 10 breaks the CHECK
COMMIT;

ERROR: new row for relation "inventory" violates check constraint "inventory_qty_check"

The first UPDATE succeeded, but the transaction failed, so TEA-02 still has 0. After an error, a transaction accepts nothing but ROLLBACK. A SAVEPOINT marks a point you can roll back to without abandoning the whole transaction:

BEGIN;
UPDATE inventory SET qty = qty + 10 WHERE sku = 'TEA-02';
SAVEPOINT before_mug;
UPDATE inventory SET qty = qty - 1 WHERE sku = 'MUG-01';
ROLLBACK TO SAVEPOINT before_mug;
COMMIT;

Isolation decides what a transaction sees of others running at the same time:

A transaction at READ COMMITTED does not stop the lost update: two transactions can both SELECT qty (1) and both write qty = 0. Either write atomically as above, lock the row first (next section), or use REPEATABLE READ/SERIALIZABLE - and retry the whole transaction when it fails with a serialization error.

Row locks and job queues

SELECT ... FOR UPDATE locks the rows it returns until the transaction ends; another transaction that tries to update or lock them waits. That's the tool when the decision really must happen in the app: lock, read, decide, write, commit.

Queue workers add SKIP LOCKED: each worker takes the next job no one else holds, instead of waiting behind a job another worker is busy with:

WITH next_job AS (
    SELECT id FROM jobs
    WHERE status = 'queued'
    ORDER BY run_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'running'
FROM next_job
WHERE jobs.id = next_job.id;

A second worker running the same statement at the same moment would skip job 1 and claim job 2. (NOWAIT fails immediately instead of waiting.)

Locks bring deadlocks: a transfer from A to B locks A then B, while one from B to A locks B then A - each waits for the other, and Postgres aborts one. Lock rows in a consistent order (by id) and keep transactions short - never wait on a user or a network call while holding locks.

Which write tool?

Where it breaks

• Read, compute in the app, write back - lost updates, even inside a READ COMMITTED transaction.
• "Check, then insert" - two requests both pass the check; use a unique constraint and ON CONFLICT.
• Upserting a batch with repeated keys - aggregate the source first.
• MERGE for concurrent upserts - it can fail with a unique violation; use ON CONFLICT.
• SERIALIZABLE without retries - serialization failures are expected; retry the transaction.
• Locks in different orders, or held across slow work - deadlocks and pile-ups.

Tradeoffs

• ON CONFLICT vs MERGE: ON CONFLICT is the concurrency-safe upsert on one unique key; MERGE expresses many rules at once (insert, update, deactivate) for syncs that run alone.
• Atomic UPDATE vs FOR UPDATE vs SERIALIZABLE: the atomic statement is simplest and fastest when the rule fits in a WHERE; FOR UPDATE when the app must decide; SERIALIZABLE protects complex multi-row rules at the cost of retries.
• One big transaction vs batches: one transaction is all-or-nothing, but holds locks longer; big backfills usually run in batches that are each safe to repeat.

Going further. The deep dive (next tab) opens up MVCC - why an UPDATE writes a new row, what that does to table size, HOT updates and fillfactor, vacuum - plus write skew, idempotency keys, the transactional outbox, advisory locks, and how other engines differ.

Check yourself

1. Why doesn't wrapping "SELECT balance, then UPDATE with the new balance" in BEGIN/COMMIT prevent a lost update?

2. A batch upsert fails with ON CONFLICT DO UPDATE command cannot affect row a second time. What's wrong with the batch?

3. What does SKIP LOCKED add to SELECT ... FOR UPDATE LIMIT 1 in a job queue?

Key takeaways

• Put the check and the change in one statement: UPDATE ... SET qty = qty - 1 WHERE qty >= 1, and read the row count.
• Upsert with INSERT ... ON CONFLICT (key) DO UPDATE SET col = t.col + EXCLUDED.col; it needs a unique index, and one row per key in the source.
• MERGE reconciles a source and a target - including rows missing from the source - and RETURNING (with old/new in Postgres 18) records what changed.
• Transactions are all-or-nothing; isolation decides what they see. READ COMMITTED alone doesn't prevent lost updates.
• FOR UPDATE locks rows; SKIP LOCKED builds job queues; lock in a consistent order to avoid deadlocks.