Postgres 18's RETURNING old and new closes a read-modify-write audit log trap

The pattern that fails quietly

A common audit-logging recipe goes like this: an application reads a row, computes the new value in its own code, writes the result back, and inserts a row into an audit table with the before and after values. A post on dev.to examines why this breaks under concurrency — and why the audit log added to catch such problems ends up concealing them.

In the author's reproduction, an account starts at 100 and ten concurrent workers each withdraw 10. Every worker runs the read-modify-write sequence, with a deliberate 50 ms gap so the timing is deterministic. The outcome: a final balance of 90 and ten audit rows that all record the same 100-to-90 transition. Nine withdrawals vanished, because each worker read the same starting balance and overwrote the others.

The damaging detail, according to the post, is that the log looks healthy: ten rows, no gaps, no nulls, nothing a consistency check would catch. During an incident, that table would steer investigators toward a retry bug while the real fault is a lost-update race. Removing the artificial delay makes the outcome non-deterministic instead of clean: three consecutive runs produced final balances of 50, 40 and 60, with only four to six distinct old values logged out of ten withdrawals.

What Postgres 18 changes

Two things fix this, and only one is new. Moving the arithmetic into the UPDATE itself — SET balance = balance - 10 — has always been possible and is what actually prevents lost updates, since the read and the write become one atomic operation. What Postgres 18 adds is the ability to reference the pre-change and post-change row in RETURNING via the old. and new. prefixes, so a single statement reports the value it replaced rather than a stale value remembered by application code.

That makes the audit insert composable with the update in one statement:

sql 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;

With the same ten concurrent workers, the outcome flips: final balance 0, ten audit rows, ten distinct old values — a step-by-step record descending from 100 to zero. Before version 18, the author notes, the same guarantee required a trigger reading OLD and NEW, which places the audit logic in database-side code that readers of the application rarely inspect.

Upserts and the xmax workaround

Because an INSERT has no previous row, everything under old is null on inserts — which turns old.id IS NULL into a readable test for which branch an upsert took. The post positions this as a replacement for the long-standing idiom of inspecting the xmax system column (casting it through text and bigint) to distinguish inserts from updates. That trick works and appears throughout older codebases, the author writes, but it demands knowledge of an internal column and reads as a bug to anyone who lacks that context.

Version 18 gotchas

The post also lists the fine print. Columns under old are null on INSERT and columns under new are null on DELETE, so a generic audit helper pointed at every statement can silently log nulls forever. A table with a column literally named old still resolves a bare old reference to that column, keeping existing queries working; the prefixed old. and new. forms are what activate the new aliases, and they can be renamed with RETURNING WITH (OLD AS prev, NEW AS cur) when both a column and an alias are needed in the same statement. The feature is 18-only: on Postgres 17 the same query fails with "missing FROM-clause entry for table 'old'", an error that does not obviously point at a version problem. The author verified the behaviour on postgres:18 in Docker, version 18.6.

Why it matters

Lost updates from read-modify-write sequences are among the oldest concurrency bugs in transactional databases, and application-side audit logs can make them harder to diagnose because they record self-consistent fiction. Postgres 18's old and new references in RETURNING let the write and its audit record happen in one statement, so the log can only describe changes that actually occurred. Teams relying on the xmax upsert detection or trigger-based auditing get a clearer alternative — once they can standardise on version 18.