Inside Postgres: why VACUUM matters
summary
Explains how PostgreSQL implements MVCC with per-row xmin/xmax timestamps, how updates append new tuples and create hot chains rather than in-place changes, how indexes point to tuple CTIDs, and why shared_buffers and 8KB pages make cross-page redirects expensive. Gives a concrete example: updating 1,000,000 rows → 2,000,000 tuples doubles on-disk size (71MB → 142MB) until VACUUM marks dead tuples reusable; VACUUM FULL rebuilds and frees space but locks the table, PGRepack is a safer alternative.
AI-written summary · about
chapters (12)
- 0:00Postgres MVCC intro
- 1:09Postgres transactions
- 2:11Isolation levels
- 8:33MVCC row versions
- 17:24MVCC visibility rules
- 18:28Postgres disk storage
- 23:07Heap polypage optimization
- 32:58Postgres read/write
- 36:06MVCC dead tuples
- 37:08Vacuum garbage collector
- 40:40Vacuum full trade-offs
- 44:21Q&A
related talks
49mA Detailed Understanding of MVCC and Autovacuum Internals in PostgreSQL 14
50mWe have multiple concurrent versions of this title trying to understand MVCC
25mUnderstanding & Managing Postgres Table Bloat
50mTame the growth of your database
19mVacuuming Enhancements in PostgreSQL 18: Faster, Smarter, More Predictable
55mUnderstanding Autovacuum
27mMaintaining Large Tables in PostgreSQL
49mConcurrency Deep-Dive