The idea: same snapshot, old versions in different places

PostgreSQL and InnoDB both use MVCC (multi-version concurrency control): a reader gets a snapshot and never waits for a writer. To do that, both must keep the old version of a row while someone may still need it. They keep it in different places. PostgreSQL writes every new version as a new tuple in the table and leaves the old one where it was. InnoDB changes the row in place and moves the old version into the undo log. The canvas runs the same statements on the same row in both engines, side by side.

Left, PostgreSQL after UPDATE by transaction 101: the heap page holds tuple (0,1) bal 100 with xmax 101 and t_ctid to (0,2), and tuple (0,2) bal 110 with xmin 101. Right, InnoDB: the clustered index record holds bal 110, DB_TRX_ID 101 and DB_ROLL_PTR pointing to an undo record holding bal 100, DB_TRX_ID 100.
One UPDATE: PostgreSQL adds a second tuple to the table; InnoDB overwrites the record and keeps the old version in undo.

PostgreSQL: a new tuple per UPDATE

Every heap tuple has a header with xmin (the transaction that created it) and xmax (the transaction that deleted or replaced it, 0 if none). An UPDATE never changes the old tuple's data. It writes a whole new tuple with xmin = its own xid, then stamps the old tuple's xmax and points its t_ctid at the new one. Whether a transaction committed is looked up in pg_xact, two bits per xid. (Demo: one UPDATE in both engines)

Here bal has no index, so the update is a HOT (heap-only tuple) update: the new tuple goes on the same page, the index entry keeps pointing at (0,1), and a lookup follows the t_ctid chain from there. See the PostgreSQL heap page for line pointers and HOT chains.

InnoDB: update in place, old version to undo

The clustered index holds one version of each row, the newest. Each record has two hidden columns: DB_TRX_ID (who wrote this version) and DB_ROLL_PTR (where its previous version is). An UPDATE first copies the old version into an undo record, then changes the record in place. Each undo record points to the one before it, so the row has a version chain in undo, newest first. See InnoDB undo logs and purge for the full mechanism.

Reading with a snapshot: who walks the chain

In this page no other transaction is running when the reader starts, so its snapshot is one number S: changes by transactions below S are visible, the rest are not. PostgreSQL stores it as xmin/xmax/xip; InnoDB as a read view (m_up_limit_id, m_low_limit_id, m_ids). Both engines return the same value. The work differs:

  • An old snapshot. PostgreSQL finds the old tuple still in the table: it is the first one on the chain. InnoDB reads the newest record, finds DB_TRX_ID too new, and rebuilds older versions one undo record at a time. (Demo: an old snapshot reads the old version)
  • A new snapshot. InnoDB is done after the record. PostgreSQL starts at (0,1) and steps over each dead version until it reaches the newest. (Demo: old reader vs new reader: who walks the chain)
After three updates by 101, 102 and 103. An old reader with snapshot 101 examines 1 tuple in PostgreSQL but 4 versions in InnoDB (record plus 3 undo records). A new reader with snapshot 104 examines 4 tuples in PostgreSQL but only the record in InnoDB.
PostgreSQL makes new readers step over old versions; InnoDB makes old readers rebuild them from undo.

Clean-up: VACUUM vs purge

An old version can go once every snapshot sees the change that replaced it. Both engines compute this from the oldest open snapshot: PostgreSQL calls it OldestXmin, InnoDB the purge view. VACUUM (usually autovacuum) removes dead tuples and marks their space free for reuse; the table file does not shrink. When the root tuple (0,1) of a HOT chain goes, its line pointer stays as a redirect because the index still points at it. The InnoDB purge thread walks the history list oldest first and frees undo records. (Demo: VACUUM and purge clean up)

Long transactions: table bloat vs history list

A transaction that keeps its snapshot open holds the horizon back in both engines, and neither can clean up. The garbage piles up in different places. In PostgreSQL it is table bloat: dead tuples fill the pages, the table and its indexes grow, and every scan steps over them. In InnoDB it is a long history list: the table stays small, but the undo tablespace grows and old readers walk longer chains. (Demo: a long reader blocks VACUUM and purge)

While a reader holds snapshot 101 and four updates commit, PostgreSQL's heap page holds 5 tuples, 4 of them old versions kept for the reader, and InnoDB holds 1 record plus 4 undo records with history list length 4. After the reader commits, VACUUM frees 4 line pointers but the page keeps its size, and purge frees 4 undo records.
A long snapshot blocks both clean-ups: PostgreSQL's table bloats, InnoDB's history list grows.

ROLLBACK

In PostgreSQL a ROLLBACK only marks the xid aborted in pg_xact. The new tuple is dead at once and the old tuple's xmax is ignored; VACUUM removes the leftover later. InnoDB has to undo the change: it copies the old version back from the undo record into the row. Rolling back a large transaction in InnoDB can take as long as the transaction itself. (Demo: ROLLBACK: free in Postgres, work in InnoDB)

Side by side

PostgreSQLInnoDB
UPDATE writesa new tuple; old tuple gets xmaxold version to undo; record changed in place
row headerxmin, xmax, t_ctidDB_TRX_ID, DB_ROLL_PTR
old versions live inthe table (heap)undo tablespaces
reader with an old snapshotfinds the old tuplerebuilds it from undo
reader with a new snapshotsteps over dead tuplesreads the record
clean-upVACUUM / autovacuumpurge thread, history list
ROLLBACKmark aborted in pg_xactapply undo records
a long transaction causestable and index bloatlong history list, long chains

See also MVCC and isolation levels for what snapshots allow and forbid, and PostgreSQL xid wraparound for the other reason VACUUM must run.

What the page leaves out

Snapshots taken while other transactions run (a non-empty xip / m_ids), concurrent writers and row locks. PostgreSQL non-HOT updates, which add an index entry per version, opportunistic page pruning on reads, hint bits, the free space map and visibility map, and VACUUM FULL. InnoDB secondary indexes, purge by trx_no rather than trx id, batch purge over rollback segments and undo truncation. Real undo records store only the changed columns.