Versioning and Visibility
When a row is updated in an MVCC-compliant system, the database does not overwrite the existing data. Instead, it creates a new version of the row, marking it with a transaction identifier (XID). Each row header contains metadata fields, typically tracking the creation XID and the expiration XID. Visibility is determined by comparing the current transaction's start time against these XIDs.
Read consistency: Transactions only see row versions committed before their snapshot start time.
Garbage collection: Older versions no longer visible to any active transaction are eventually reclaimed by background vacuuming processes.
Write conflicts: If two transactions attempt to update the same row, the system detects a conflict, forcing the second transaction to abort or retry.
The Trade-offs of Multi-Versioning
While MVCC significantly improves throughput, it introduces complexity in storage and maintenance. Storing multiple versions increases disk space usage, requiring efficient page management. Furthermore, the vacuuming process—which purges obsolete versions—must be tuned carefully. If the vacuum process lags behind the rate of updates, the database suffers from 'bloat,' where performance degrades due to the increased overhead of scanning through outdated row versions to find current ones.
Engineers should treat MVCC not as a 'set and forget' feature, but as a system component that requires awareness of transaction longevity. Long-running transactions are particularly dangerous in MVCC systems because they prevent the garbage collector from reclaiming older versions of data, leading to rapid storage growth. Understanding the relationship between snapshot lifetime and physical storage is essential for maintaining a healthy database under heavy load.
