MySQL vs PostgreSQL MVCC: Heap Tuple Versions vs Undo Log Segments

An engineering team migrates a high-frequency analytics engine from MySQL InnoDB to PostgreSQL.

The service performs 50,000 row UPDATE statements per second, modifying a single last_seen_at timestamp column across user profiles.

Under MySQL InnoDB, the database handles the workload smoothly with low disk write amplification.

Under PostgreSQL, system metrics deteriorate rapidly:

Physical disk write volume increases 6x.
Database table sizes balloon by 40 Gigabytes within hours due to “dead tuples”.
CPU utilization spikes as the Autovacuum worker consumes system resources to clean up table pages.

Why did identical SQL UPDATE operations produce completely different resource overheads across MySQL and PostgreSQL?

The cause is a fundamental architectural divergence in how MySQL and PostgreSQL implement Multi-Version Concurrency Control (MVCC).

1. Architectural Overview: Two Approaches to MVCC

While both engines use MVCC to provide non-blocking snapshot reads, they store historical row versions in completely different locations:

MySQL InnoDB: In-Place Update + Undo Log      PostgreSQL: Multi-Tuple Heap Storage

 [ Primary Clustered Page ]                    [ Heap Page (Data File) ]
  +----------------------+                      +-----------------------+
  | Latest Tuple Image   |                      | Old Tuple (xmin, xmax)| <-- Dead Tuple
  | (Updated In-Place)   |                      +-----------------------+
  | DB_ROLL_PTR ---------+--+                   | New Tuple (xmin, xmax)| <-- Active Tuple
  +----------------------+  |                   +-----------------------+
                            v
                    [ Undo Log Segment ]       [ Secondary Index 1 ] [ Secondary Index 2 ]
                    | Historic Version |         Points to Old ctid   Points to New ctid
                    +------------------+         (Both indexes must be updated!)

MySQL InnoDB (Roll-Pointer Engine): Updates data pages in-place and writes historical version deltas into separate Undo Log Segments.
PostgreSQL (In-Page Heap Tuple Engine): Stores multiple version copies of the exact same row directly as separate physical tuples (Heap Tuples) inside the primary data table file.

2. PostgreSQL MVCC: Heap Tuples & `xmin`/`xmax`

Every row tuple in PostgreSQL contains hidden system headers:

xmin: The Transaction ID of the transaction that inserted the tuple.
xmax: The Transaction ID of the transaction that deleted or updated the tuple (0 if active/not deleted).
ctid: The physical tuple identifier (page_number, tuple_index) pointing to the row’s location on disk.

How PostgreSQL Executes an UPDATE

When you execute UPDATE users SET last_seen_at = NOW() WHERE id = 10:

PostgreSQL does not update the existing row tuple in-place.
It sets xmax = current_tx on the old tuple, marking it as logically deleted for future transactions.
It appends a brand new tuple containing the updated values into the heap page, setting its xmin = current_tx.

Write Amplification & Secondary Index Bloat

Because the new tuple gets a brand new physical disk address (ctid), every single secondary index on that PostgreSQL table must insert a new pointer entry pointing to the new ctid—even if the updated column had nothing to do with those secondary indexes!

Example: If a table has 8 secondary indexes, updating a single non-indexed timestamp column writes 1 new heap tuple + 8 new secondary index entries.

The Heap-Only Tuple (HOT) Optimization

To mitigate this write amplification, PostgreSQL implements Heap-Only Tuples (HOT):

If an UPDATE does not modify any indexed column, AND the new tuple fits on the exact same 8KB data page as the old tuple:
PostgreSQL chains the old tuple to the new tuple inside the page header, skipping secondary index updates entirely.

3. MySQL InnoDB MVCC: Undo Segments & In-Place Writes

In contrast, MySQL InnoDB handles UPDATE operations by modifying data page columns in-place:

The previous column state is written to an Undo Log Segment.
The record on the primary clustered index page is updated directly in RAM.
Secondary index leaf entries store the immutable Primary Key, not physical page byte offsets (ctid).

Why MySQL Avoids Secondary Index Bloat

If an UPDATE modifies a non-indexed column (like last_seen_at), InnoDB updates the clustered page in-place.

Secondary index leaf nodes store the Primary Key value (e.g., id = 10), which did not change.
Zero secondary index writes are required. Secondary index write amplification is completely avoided!

4. Garbage Collection: Purge Threads vs PostgreSQL VACUUM

Because both database engines accumulate historical version records, both require background garbage collection mechanisms to reclaim disk space.

MySQL InnoDB Purge Engine

Historical versions reside in dedicated Undo Tablespace files (undo_001).
The Purge Thread scans the Undo History List and recycles undo pages directly.
Primary table data files (.ibd) do not fill up with historical dead tuples.

PostgreSQL VACUUM & Autovacuum Engine

Historical versions (Dead Tuples) remain physically mixed alongside active rows inside main table heap pages.
The Autovacuum Engine must continuously scan entire table pages to remove dead tuples and update free space maps.
Table Bloat: If Autovacuum falls behind high-volume update workloads, tables and indexes expand in size on disk, degrading linear scan performance.

Transaction ID Wraparound (PostgreSQL)

Because PostgreSQL uses 32-bit integers for xmin/xmax Transaction IDs (max $2^{32} \approx 4.2$ billion IDs), transaction IDs will eventually wrap around. To prevent historic transactions from suddenly appearing as future transactions, PostgreSQL’s Autovacuum must periodically perform a Freeze Operation on all table pages. If freezing fails, PostgreSQL shuts down into read-only recovery mode to prevent data loss.

Comparative Architectural Matrix

Metric / Dimension	MySQL InnoDB	PostgreSQL
Historical Version Location	Separate Undo Log Segments	Main Table Heap Pages (Multi-Tuple)
`UPDATE` Execution Method	In-Place update + Undo Delta write	Append new tuple + mark `xmax` on old tuple
Secondary Index Entry Pointer	Logical Primary Key value	Physical Tuple ID (`ctid` page byte offset)
Non-Indexed Column Write Amplification	Low (No secondary index updates)	High (Requires HOT optimization to avoid index writes)
Read Latency for Historical Views	Requires traversing Undo Log chain	Direct page read (evaluating `xmin`/`xmax` headers)
Garbage Collection Mechanism	InnoDB Purge Threads (Undo Log recycling)	Autovacuum Engine (Scanning heap pages for dead tuples)
Garbage Collection Risk	Undo tablespace growth under long transactions	Table & Index Bloat + Transaction ID Wraparound

Summary & Next Steps

Understanding MVCC differences allows engineers to choose and tune database engines effectively:

MySQL InnoDB excels in high-frequency update workloads by modifying pages in-place, relying on Undo Logs to construct historical views, and using primary keys to prevent secondary index bloat.
PostgreSQL uses in-page heap tuples (xmin/xmax), offering fast snapshot evaluation but requiring HOT optimizations and vigilant Autovacuum tuning to mitigate write amplification and table bloat.

In the next article, we examine Operating Production Databases: High Availability Replication & Connection Pooling.

References & Further Reading

Corbett, J. C., et al. (2013). Spanner: Google’s Globally Distributed Database. ACM Transactions on Computer Systems (TOCS), 31(3), Article 8.
Cockroach Labs. CockroachDB Architecture: Multi-Version Concurrency Control (MVCC). CockroachDB Docs.
Liskov, B. (1993). Practical Uses of Synchronized Clocks in Distributed Systems. Distributed Computing, 6(4), 211–219.