Commit or Rollback, Postgres Don't Care

Introduction

I was recently reading about how Postgres implements MVCC. One thing that fascinated me, and that I expected to be complicated, is transaction rollback. I always assumed a rollback meant more work than a commit, since the database would have to undo every change. After reading how Postgres does it, I realized that, as far as the table data is concerned, Postgres doesn’t much care whether you commit or roll back. Neither outcome requires going back over each changed row and rewriting it.

Data Layout in MVCC

Postgres stores table data in files made up of fixed-size pages, usually 8 kB each. I won’t go deep into the page structure. The important part is that a row can exist as multiple versions (tuples), and each version’s header carries two transaction IDs:

xmin is the ID of the transaction that created this version, via INSERT or UPDATE.
xmax is 0 if unused. In the simple case, it holds the ID of the transaction that deleted or updated this version. (It can also record row locks, but I’ll stick to the simple case.)

You can see both yourself with SELECT xmin, xmax, * FROM t;.

The header also has flag bits, defined in htup_details.h. Four of them cache what’s known about xmin and xmax. For an ordinary, non-frozen tuple they mean:

Flag	Meaning when set
`HEAP_XMIN_COMMITTED`	creator committed
`HEAP_XMIN_INVALID`	creator aborted
`HEAP_XMAX_COMMITTED`	deleter committed
`HEAP_XMAX_INVALID`	no valid deleter

Both XMIN bits set together is a special case meaning the tuple is frozen, so I’ll leave that aside.

These bits are only hints. The authoritative record of each transaction’s outcome lives centrally in pg_xact, described in the transaction system README. Hint bits just cache that answer on the tuple, so later readers don’t need to look it up again.

MVCC Lifecycle

When a row is inserted, xmin is set to the current transaction’s ID and xmax is 0. While that transaction is still running, neither xmin hint bit is set, because the outcome isn’t known yet. The inserting transaction sees its own row in subsequent commands. Other transactions see it only after it commits, and only if their snapshot was taken after the commit.

An UPDATE doesn’t overwrite the row. It sets xmax on the old version and writes a new version whose xmin is that transaction ID. Say transaction 100 inserted a row and committed, and transaction 205 then updates it:

xmin  xmax  data
100   205   balance = 50   -- old version
205   0     balance = 80   -- new version

If 205 commits, pg_xact records it as committed. New snapshots treat the old version as deleted and the new one as live.

If 205 rolls back, pg_xact records it as aborted. Readers see that the new version’s creator aborted, so it’s invisible. They see that the old version’s deleter aborted, so it’s still live. No reverse rewrite of the row is needed.

Either way, ending the transaction doesn’t require walking and rewriting every tuple it changed. That doesn’t mean the heap never changes afterwards. The next reader to check these tuples can fill in hint bits. After a commit, that’s HEAP_XMAX_COMMITTED on the old version and HEAP_XMIN_COMMITTED on the new one. After an abort, it’s HEAP_XMAX_INVALID and HEAP_XMIN_INVALID.

Whichever version ended up dead also still takes space. After a commit, that’s the old version, once no snapshot can still need it. After a rollback, it’s the aborted new version. Page pruning and VACUUM reclaim that space later, when it’s safe to do so.

Not Quite “Don’t Care”

To be fair, commit and rollback don’t cost exactly the same. A commit may wait for its WAL to be flushed or for synchronous replicas to confirm. A rollback still performs cleanup, like releasing locks and other resources. What surprised me is that neither outcome goes back over the rows it touched. Postgres records one status for the transaction and lets readers and VACUUM sort out the rest.