A Detailed Understanding of MVCC and Autovacuum Internals in PostgreSQL 14
summary
Explains that PostgreSQL MVCC keeps undo as old rows in the same table (xmin/xmax/ctid visible via page_inspect), demonstrates pg_free_space_map to show free space, and measures auto‑vacuum behaviour with default formula (vacuum when changes ≥ 0.2N + 50) and cost settings (default cost_limit 200* with 20 ms sleep), deriving best‑case vacuum throughput (~78 MB/s memory, ~7.8 MB/s disk, ~3.9 MB/s dirty). Covers XID wraparound limits (32‑bit ≈4.2B, warnings at ≈11M left, stop at ≈1M) and PG14’s less aggressive freeze scanning.
AI-written summary · about
chapters (5)
- 0:00Start
- 2:46UNDO Management - Oracle & PostgreSQL
- 22:06VACUUM / AUTOVACUUM
- 28:11Transaction ID Wraparound
- 43:35Points to Note
related talks
48mInside Postgres: why VACUUM matters
55mUnderstanding Autovacuum
50mWe have multiple concurrent versions of this title trying to understand MVCC
19mVacuuming Enhancements in PostgreSQL 18: Faster, Smarter, More Predictable
25mUnderstanding & Managing Postgres Table Bloat
50mTame the growth of your database
49mConcurrency Deep-Dive
47mImproved Freezing in Postgres Vacuum: From Idea to Commit