PostgreSQL MVCC & Tuple Visibility

Mental model

PostgreSQL implements Multi-Version Concurrency Control (MVCC) so that readers do not block writers and writers do not block readers. Every UPDATE or DELETE creates new physical tuple versions rather than overwriting existing disk pages in place.

Theory

Each table tuple contains hidden system columns: xmin (transaction ID that created the tuple) and xmax (transaction ID that expired/deleted the tuple). A transaction snapshot determines visibility based on whether xmin is committed before snapshot creation and xmax is uncommitted or created after snapshot creation.

Alternatives and trade-offs

• Lock-based Concurrency: Traditional databases use shared/exclusive locks for reads, causing read starvation under heavy writes.
• MVCC (PostgreSQL): Eliminates read locks via tuple versions, but incurs table bloat requiring periodic VACUUM worker sweeps.

Failure modes and misconceptions

1. Long-Running Transactions: Uncommitted long-running transactions freeze the active snapshot horizon, preventing VACUUM from cleaning up dead tuples and causing massive disk bloat.
2. VACUUM FULL Lock Hazards: VACUUM FULL rewrites entire tables to disk and acquires an ACCESS EXCLUSIVE lock, blocking all production queries. Use pg_repack or autovacuum tuning instead.

Decision scenario

Configure autovacuum scale factors aggressively (autovacuum_vacuum_scale_factor = 0.05) for high-update tables to prevent dead tuple accumulation without running manual blocking vacuums.

Learning outcomes

• Trace tuple visibility rules using xmin, xmax, and active transaction snapshots.
• Compare READ COMMITTED, REPEATABLE READ, and SERIALIZABLE isolation levels.
• Tune autovacuum settings to prevent database table bloat.

Trade-offs

MVCC enables non-blocking concurrent reads and writes, but requires effective background autovacuum management to reclaim dead tuple disk space.