TL;DR

Both MySQL InnoDB and PostgreSQL implement MVCC, but the underlying mechanisms differ fundamentally:

  • InnoDB: undo logs + rollback segments — history versions live externally
  • PostgreSQL: in-heap versioning — old tuples coexist with current ones on the same page

InnoDB: Undo Log Approach

Each row in InnoDB’s clustered index stores only the current version. Historical versions reside in undo logs, linked via DB_ROLL_PTR:

Clustered Index Record (current)
┌─────────────────────────────┐
│ id=1, amount=100            │ ← current version
│ DB_ROLL_PTR ────────┐       │
└──────────────────────┼──────┘
                       │
                       ▼
      Undo Log (version chain)
      ┌─────────────────────┐
      │ amount=80  (tx=102) │
      │ DB_ROLL_PTR ──┐     │
      └────────────────┼────┘
                       ▼
      ┌─────────────────────┐
      │ amount=50  (tx=99)  │
      │ DB_ROLL_PTR = NULL  │
      └─────────────────────┘

Key Characteristics

  • Rollback Segments house undo logs; the Purge thread cleans up
  • ReadView is created at first read, capturing active transaction list
  • Version chain traversal: current → undo chain → find visible version

PostgreSQL: In-Heap Versioning

PostgreSQL keeps multiple tuple versions in the same heap page, using xmin / xmax system columns:

┌─────────────── Heap Page ───────────────────────┐
│  Tuple 1:  xmin=103, xmax=0     (current)        │
│  Tuple 2:  xmin=100, xmax=103  (old, updated)    │
│  Tuple 3:  xmin=98,  xmax=100  (older version)   │
└─────────────────────────────────────────────────┘

Key Characteristics

  • UPDATE = mark old xmax + insert new tuple (insert-only model)
  • No undo log; old versions stay in the data page
  • VACUUM reclaims dead tuple space
  • Snapshot visibility decided by (xmin, xmax, xip) triplet

Head-to-Head Comparison

Aspect InnoDB PostgreSQL
History storage Undo log (external) In-heap (same page)
Update cost Write undo + modify row Insert new tuple only
Version lookup Chain traversal Direct page scan
Space reclamation Purge thread VACUUM
Rollback efficiency Apply undo Mark as invisible
Table bloat Lighter Needs periodic VACUUM

Which One for What?

  • High-update OLTP → InnoDB more stable; undo log has independent cleanup
  • Analytical queries → PostgreSQL’s in-heap approach may reduce IO
  • Long transactions → PostgreSQL risks table bloat; InnoDB’s undo log is time-bounded

Summary

Neither design is universally superior. InnoDB’s undo-log approach is more robust for update-heavy workloads, while PostgreSQL’s in-heap versioning is simpler and more elegant — but relies on VACUUM. Understanding these trade-offs is essential for day-to-day operations.

References