Maintaining Large Tables in PostgreSQL

on YouTube ↗27m

" summary

Diagnoses specific failure modes—autovacuum falling behind causing silent table and index bloat, cache trashing as working set exceeds memory, vacuum/freeze storms from autovacuum_freeze_max_age hitting 200M, runaway WAL and replication lag, HOT update failures and lock amplification—and gives concrete fixes: per-table tuning of autovacuum_vacuum_scale_factor and cost limits, scheduled manual VACUUM/ANALYZE and pg_repack, partitioning or hot/cold splits, fillfactor to preserve HOT updates, and watching metrics like n_tup_hot_upd and pg_stat_checkpointer to verify improvement.

AI-written summary · about

" chapters (11)

1. 0:00Music & introduction
2. 1:58Autovacuum limits and table bloat
3. 3:16Cache inefficiency and I/O bottlenecks
4. 4:20Vacuum / freeze storms explained
5. 6:33WAL volume explosion and replication lag
6. 9:18Lock contention, cascading blocks, and statistics staleness
7. 11:21HOT update failures and performance optimizations
8. 15:31Autovacuum tuning strategies that work
9. 20:06Preserving HOT updates and controlling WAL pressure
10. 23:09Reducing lock contention and keeping stats fresh
11. 26:36Closing

" related talks

• 25mUnderstanding & Managing Postgres Table BloatChelsea Dole · Citus Con: An Event for Postgres 2023
• 32mOptimizing autovacuum: PostgreSQL’s vacuum cleanerSamay Sharma · Citus Con: An Event for Postgres 2022
• 47mAutoVacuum - Tuning and MonitoringLloyd Albin · Postgres Conference Orlando 2025
• 55mUnderstanding AutovacuumDan Robinson · PGConf Silicon Valley 2016
• 49mUnderstanding and Fixing AutovacuumRobert Haas · PostgreSQL Conference Europe 2023
• 50mTame the growth of your databaseAdam Wolk · PostgreSQL Conference Europe 2023
• 33mPostgreSQL Observed—and ExplainedKaren Jex, Stacey Haysler · PostgreSQL Conference Europe 2024
• 48mThe Good, The Bad, and The Bloated: An Autovacuum StoryJérémie Grauer · PostgreSQL Conference Europe 2025