We have multiple concurrent versions of this title trying to understand MVCC
summary
Explains that every PostgreSQL row carries version metadata so visibility depends on xmin/xmax visibility plus the transaction status, shows how updates create dead rows and gives a real example with 5,666 dead tuples and replication lag of several GB caused by autovacuum WAL traffic. Demonstrates using txid_current/txid_status, pg_stat_activity and pg_stat_all_tables, explains auto-vacuum thresholds (default 20% dead tuples), contrasts VACUUM vs vacuum full downtime, and shows when to cancel or terminate backends.
AI-written summary · about
chapters (28)
- 0:00Introduction to MVCC
- 1:05MVCC core concepts
- 3:31MVCC and maintenance pain
- 5:14Replication lag mystery
- 6:17Row versions and snapshots
- 7:43Transactions and visibility
- 9:28Transaction IDs and status
- 10:55Xmin Xmax explained
- 13:20Dead rows and their impact
- 15:04Isolation levels overview
- 17:28Read committed vs repeatable read
- 18:50What is vacuum?
- 20:12Manual vacuum and semicolons
- 21:20Investigating dead rows
- 23:25Hunting idle transactions
- 24:47Terminating long queries
- 26:12Vacuum locking effects
- 27:18Vacuum full dangers
- 28:43Replication and vacuum lag
- 30:29Vacuum as traffic jam
- 31:11Autovacuum benefits
- 31:53Monitoring vacuum progress
- 32:57Vacuum progress details
- 33:38Transaction wraparound
- 36:51Freezing old transactions
- 38:15Monitoring wraparound risk
- 39:20Summary and key points
- 41:32Q&A session
related talks
49mA Detailed Understanding of MVCC and Autovacuum Internals in PostgreSQL 14
48mInside Postgres: why VACUUM matters
50mExplaining the PG concurrency control mechanisms using rollercoasters
50mTame the growth of your database
49mConcurrency Deep-Dive
28mUnderstanding Transaction Isolation for App Developers
55mUnderstanding Autovacuum
25mUnderstanding & Managing Postgres Table Bloat