Understanding & Managing Postgres Table Bloat
summary
Explains that Postgres bloat comes from MVCC creating dead tuples (heavy UPDATE/DELETE patterns) plus conservative autovacuum settings; measurable with pgstattuple (accurate but O(n)) or lightweight估imate queries (O(1)); fixes include tuning autovacuum (set autovacuum_vacuum_scale_factor low, threshold, autovacuum_vacuum_cost_delay ≈2ms, increase max_workers) and reclaiming space with VACUUM FULL (exclusive lock) or online tools like pg_repack (works on RDS) vs pg_squeeze (requires binary change); gives empirical bloat guidance (small tables ≤70%, medium ≈25%, large ≤18–20%).
AI-written summary · about
chapters (6)
- 0:00Introduction
- 1:52Multi-version Concurrency Control (MVCC)
- 8:19Vacuum
- 10:37Table bloat
- 14:27Avoiding Postgres table bloat
- 22:19Designing bloat-aware data access patterns
related talks
27mMaintaining Large Tables in PostgreSQL
48mInside Postgres: why VACUUM matters
48mThe Good, The Bad, and The Bloated: An Autovacuum Story
50mTame the growth of your database
47mAutoVacuum - Tuning and Monitoring
32mOptimizing autovacuum: PostgreSQL’s vacuum cleaner
55mUnderstanding Autovacuum
49mA Detailed Understanding of MVCC and Autovacuum Internals in PostgreSQL 14