In the world of relational databases, there is a holy grail of high performance: readers should never block writers, and writers should under no circumstances force readers to wait.
If every SELECT query placed a shared lock on an entire table or row, and every UPDATE froze reads, no modern e-commerce site or social network could survive a second of real traffic. To solve this conflict, the concept of Multi-Version Concurrency Control (MVCC) was invented.
The idea is simple: when a transaction begins reading data, it captures a point-in-time snapshot of the database (Snapshot or Read View) and sees rows strictly as they existed at that moment, ignoring concurrent changes from other users.
Yet what's astonishing is that the two flagship relational DBMSs of the modern web—MySQL (with the InnoDB engine) and PostgreSQL—implemented this principle on diametrically opposed laws of storage physics.
A foundational analysis of this difference was documented by former Twitter and Yahoo! principal database architect Jeremy Cole in his study, while concurrency details are covered in the official PostgreSQL MVCC documentation. Let's take both approaches apart under the hood.
The InnoDB Model in MySQL: A Single Live Row and a Rollback Chain
InnoDB's architecture follows the in-place update principle:
- Only one current version of a row is physically stored in the primary clustered B-Tree index at any time;
- Every record's header hides two mandatory system fields:
DB_TRX_ID(6 bytes—the ID of the transaction that created or modified the record) andDB_ROLL_PTR(7 bytes—the Rollback Pointer); - When a transaction executes an
UPDATE, the old row value is copied into a dedicated rollback disk space—the Undo Log—and the table record is overwritten in place with the new value. Meanwhile,DB_ROLL_PTRpoints to the old copy in the Undo Log.
If a row is updated five times in a row, a linked list (rollback chain) forms in the Undo Log, leading backward in time from the row's current state.
How does reading work? If a reading transaction with an older Read View accesses a row and sees that its DB_TRX_ID is newer than the snapshot, InnoDB unwinds the Undo Log chain on the fly in memory, applying reverse deltas to reconstruct the past row snapshot for the reader.
And once old transactions finish, a background system process called Purge scans the history list (History List Length, HLL) and frees unneeded Undo Log segments.
The PostgreSQL Model: Multiple Tuple Versions Straight in the Table Heap
PostgreSQL engineers took a completely different route, choosing an append-only model:
- PostgreSQL has no separate Undo Log space for storing historical values;
- On an
UPDATE, the old row is never overwritten at all. Instead, a brand-new tuple (new row version) is written into a physical table page (the heap page); - Each tuple's header carries system metadata:
xmin(ID of the creating transaction) andxmax(ID of the deleting or updating transaction, or 0 for a live row).
The old row version stays right where it is in the table file, with its xmax field stamped with the updating transaction's ID.
A reading transaction simply scans the table page and, by comparing xmin and xmax against its list of active transactions, determines which tuple versions are visible to it and which should be ignored.
The flip side of this simplicity is Table Bloat. Over time, millions of "dead tuples" accumulate inside tables. To reclaim space, PostgreSQL critically relies on the autovacuum background daemon, which continuously sweeps pages, cleans out dead records, and updates free-space maps.
Operational Consequences: What You Pay For in Production
The difference in fundamental design leads to completely different behavior under real load:
1. Cost of Frequent Updates (Write Amplification)
In MySQL, updating non-indexed columns is dirt cheap: the record is modified in place in the buffer pool's RAM, a short delta is written to the Undo Log, and secondary indexes aren't touched at all. In PostgreSQL, every update creates a new tuple. That means all secondary indexes on the table must receive new pointers to the new row—unless the HOT (Heap-Only Tuples) optimization kicks in, which requires spare free space on the exact same table page.
2. The Long-Running Transactions Trap
This is every DBA's worst nightmare:
- In MySQL: if an analyst kicks off a heavy report or a backup hangs in
REPEATABLE READmode, it holds open an old Read View. That completely blocks the Purge process for the entire database! History List Length balloons into the millions,ibdata1or undo tablespaces swell, and ordinarySELECTqueries slow down by hundreds of times as they unwind mile-long rollback chains. - In PostgreSQL: a long open transaction pins the system threshold
OldestXmin.VACUUMloses permission to remove any dead tuples created after that point. As a result, table sizes on disk multiply, the buffer cache fills with garbage, and the database risks running out of disk space.
Sober Architectural Conclusions
There is no "universally best" DBMS: each engine sacrificed something different to win its strengths.
InnoDB in MySQL is ideal for workloads with frantic point updates by primary key (counters, balances, financial transactions), but demands ruthless control over hung transactions and constant monitoring of History List Length.
PostgreSQL delivers incredible flexibility with extensions, rich data types, and indexing, yet requires careful tuning of autovacuum thresholds and an understanding that every updated row physically occupies a new tuple on disk. Understanding this internal mechanics lets you design resilient data schemas and avoid surprises when traffic peaks.

