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.
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:
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:
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.
Postgres 18 lets you name the row before and after the change directly in RETURNING:
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:
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.
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:
If you have written Postgres for a while you will recognise what this replaces. The old trick was:
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.
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: