Your audit log is probably lying to you. Postgres 18 fixes it in one statement.

I've written this more times than I'd like, in four or five languages by now:

SELECT balance FROM accounts WHERE id = 1;
-- application does the arithmetic
UPDATE accounts SET balance = $new WHERE id = 1;
INSERT INTO audit_log (account_id, old_balance, new_balance) VALUES (1, $old, $new);

Read it, do the sum, write it back. Log what happened. Nothing about that raises an eyebrow in review, and it falls apart the second two people hit it together. The annoying part is that the audit log, which you added to catch this sort of thing, is what buries it.

Postgres 18 turns the whole thing into one statement. Before that, though, the bug is worth a proper look, because it did more damage than I expected when I finally ran it.

Reproducing it

One account starting at 100. Ten workers, each taking out 10. What should happen is obvious enough: you end on zero, and the audit log walks down in steps, 100 to 90, then 90 to 80, and so on.

Each worker does the read-then-write thing from above. I put a 50 ms gap in the middle so the timing lands the same way every run:

final balance                      | 90
audit rows                         | 10
distinct old_balance values logged | 1

 old_balance | new_balance | times_logged
-------------+-------------+--------------
         100 |          90 |           10

Ninety. Nine withdrawals gone. All ten workers read the same 100, wrote back the same 90, and each one logged itself as the one that did it.

That last part is the bit I'd want someone to notice. It isn't only that money went missing. The audit log agrees with itself. Ten neat rows, every one internally consistent, no gaps, no nulls, nothing a validator would flag. Hand me that table during an incident and I'd conclude the same withdrawal got retried ten times, then go and read the retry logic, which is fine.

Take the sleep out and it stops being deterministic. It doesn't stop happening. Three runs back to back:

final balance 50, distinct old values logged 5
final balance 40, distinct old values logged 6
final balance 60, distinct old values logged 4

Between 4 and 6 writes lost out of 10, on this machine, with nothing slowing anything down. You don't need a debugger and a following wind to hit that. It's roughly a coin flip.

The one-statement version

Postgres 18 lets you name the row before and after the change directly in RETURNING:

UPDATE accounts SET balance = balance - 10 WHERE id = 1
RETURNING old.balance AS was, new.balance AS now;

 was | now
-----+-----
 100 |  90

There are two changes in there, and the interesting one isn't the new syntax.

Doing balance - 10 inside the statement means the read and the write happen together, so there's no gap for anyone to slip into. That has been possible forever, and it's the bit that stops the lost update.

What Postgres 18 adds is that you also get told what the value was, from the same statement that replaced it. Not what it was when you last looked. The value this particular UPDATE actually overwrote. So the audit row can be written from the same operation:

WITH moved AS (
  UPDATE accounts SET balance = balance - 10 WHERE id = 1
  RETURNING old.balance AS was, new.balance AS now
)
INSERT INTO audit_log (account_id, old_balance, new_balance)
SELECT 1, was, now FROM moved;

Same ten concurrent workers:

final balance                      | 0
audit rows                         | 10
distinct old_balance values logged | 10

 old_balance | new_balance
-------------+-------------
         100 |          90
          90 |          80
          80 |          70
          70 |          60
          60 |          50
          50 |          40
          40 |          30
          30 |          20
          20 |          10
          10 |           0

Zero, and the staircase survived. Ten workers went at it simultaneously and the log still came out in order, because every row was written by the statement doing the work instead of by an application repeating what it had been told a moment earlier.

Before 18 you could get this, but you needed a trigger with OLD and NEW, which means the audit logic lives somewhere a reader of the application code will never look.

The bit that will save you the most time

RETURNING old gives you something else for free. On an INSERT there is no old row, so old is null. Which means an upsert can finally tell you which branch it took:

INSERT INTO accounts VALUES (3, 10)
ON CONFLICT (id) DO UPDATE SET balance = EXCLUDED.balance
RETURNING old.id IS NULL AS was_inserted, old.balance, new.balance;

First run, no existing row:

 was_inserted | balance | balance
--------------+---------+---------
 t            |         |      10

Run it again against the row that now exists:

 was_inserted | balance | balance
--------------+---------+---------
 f            |      10 |      20

If you have written Postgres for a while you will recognise what this replaces. The old trick was:

RETURNING (xmax::text::bigint <> 0) AS was_update

Reading a system column, casting it to text, casting that to a bigint, and comparing it to zero, to find out whether your own statement inserted or updated. It works, and it's all over older codebases. It also requires you to know what xmax is, and to anyone who doesn't, it looks like a bug.

old.id IS NULL doesn't need a footnote.

Three things I got wrong on the first pass

On INSERT everything under old is null, and on DELETE everything under
new is null. Obvious once you say it out loud, easy to miss when you've written one audit helper and pointed every statement at it. Log old.balance from an INSERT and you get a null, silently, forever.

I assumed a table with a column named old would break. It does not.
Bare old still resolves to your column, so existing queries keep working:

UPDATE legacy SET old = 'CHANGED2' WHERE id = 1 RETURNING old;
-- returns CHANGED2, the column, not the pre-update row

The old. and new. prefixes are what activate the aliases. If you need both in one statement, rename them:

RETURNING WITH (OLD AS prev, NEW AS cur) prev.old, cur.old

It is 18 only. On 17 you get an error that does not obviously point at
a version problem:

ERROR:  missing FROM-clause entry for table "old"

I ran that against postgres:17 to check, and if you land here from searching that string, this is your answer.

Run it yourself

Everything above came from postgres:18 in Docker, version 18.6, on a laptop. No cloud account, nothing to sign up for:

docker run -d --name pg18 -e POSTGRES_PASSWORD=demo -e POSTGRES_DB=demo postgres:18
docker exec -it pg18 psql -U postgres -d demo

CREATE TABLE accounts (id int PRIMARY KEY, balance numeric NOT NULL);
INSERT INTO accounts VALUES (1, 100);

UPDATE accounts SET balance = balance - 10 WHERE id = 1
RETURNING old.balance AS was, new.balance AS now;

Your concurrency numbers won't match mine, which is rather the point of a race. Run the naive version a few times and watch the final balance land somewhere different each go.

What I would take from this

The feature itself is small. One line describes it: RETURNING now understands old and new.

What made it worth an evening was what it exposed on the way. I've written the read-then-write pattern for years, and I've added audit tables to catch exactly the class of problem that pattern creates, without ever noticing that the audit table inherits the same race and so can't see it. Ten rows, perfectly consistent, all wrong.

If you have that pattern in a codebase somewhere, the arithmetic-in-SQL half is the urgent fix and it works on any version. The old/new half is what lets you delete the trigger you wrote to work around not having it.