🚀AdvancedConcurrency
MVCC Multi-Version Concurrency Control
Understand how PostgreSQL achieves concurrent read/write without blocking using MVCC.
Overview
MVCC (Multi-Version Concurrency Control) is PostgreSQL's foundation for concurrent access. Instead of locking rows, PostgreSQL creates a new version of each modified row, allowing readers and writers to work simultaneously without blocking each other. Readers see a consistent snapshot; writers see their own changes only after commit.
Each row version has xmin (the transaction that created it) and xmax (the transaction that deleted/updated it). The visibility rules determine which version a given transaction can see. Dead tuple cleanup is performed by VACUUM.
Key Points
- •Readers never block writers; writers never block readers
- •Each row version has xmin (creator transaction) and xmax (deleter transaction)
- •Snapshots determine which row versions are visible
- •Dead tuples (old versions) accumulate and must be cleaned by VACUUM
- •VACUUM ANALYZE removes dead tuples and updates statistics
- •Table bloat occurs when VACUUM doesn't keep up with writes
Examples
Viewing row version info
-- View MVCC system columns
SELECT xmin, xmax, ctid, * FROM orders LIMIT 5;
-- Run VACUUM to clean dead tuples
VACUUM orders;
VACUUM ANALYZE orders;
-- Check table bloat
SELECT relname, n_dead_tup, n_live_tup,
ROUND(n_dead_tup::NUMERIC / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4