Autovacuum, Bloat and Freeze

Understand how PostgreSQL cleans up dead row versions, what happens when it can't, and how to tune autovacuum for high-churn tables — all hands-on with a real stand.

Every UPDATE in PostgreSQL leaves a dead row behind. Autovacuum cleans them up, but only if the thresholds let it, no long transaction is in the way, and the table isn't already bloated beyond recovery. This course walks the whole lifecycle hands-on: we stand up a real database, churn half a million rows, watch autovacuum kick in after we lower its threshold with a reloption, block it with a long-running transaction, then measure bloat directly from pg_stat_user_tables and pg_relation_size. We finish with transaction ID wraparound — the freeze counter every production DBA watches — and build a single monitoring query that surfaces all of it at once.

What you'll build

• Read the autovacuum threshold formula and predict when cleanup runs
• Set per-table reloptions to tune autovacuum for high-churn tables
• Identify long transactions that block dead-tuple removal via pg_stat_activity
• Measure table bloat from pg_stat_user_tables and pg_relation_size
• Read age(relfrozenxid) and understand the wraparound risk
• Run VACUUM FREEZE and observe the freeze counter drop to zero
• Use a single monitoring query as a production autovacuum health check

Contents

1. A database for watching autovacuum
2. Start it
3. Connect with psql
4. The events table — the churn engine
5. The accounts table — a quieter neighbour
6. Create the tables
7. Seed the events table, deterministically
8. Seed the accounts table
9. Load the data
10. Confirm what we are sitting on
11. Refresh the statistics baseline
12. The threshold formula in one query
13. Read the thresholds on the live database
14. The churn script — dead tuples on demand
15. Create the first batch of dead tuples
16. Read the dead-tuple counter
17. Lower the threshold with a reloption
18. Apply the reloption to the live table
19. Confirm the reloption is stored
20. Run churn again to push clearly past the new threshold
21. Wait for autovacuum to run
22. A script that holds a transaction open
23. Open the long transaction in the background
24. Create new dead tuples while the transaction is open
25. VACUUM can see the dead tuples but cannot remove them
26. Find the culprit in pg_stat_activity
27. Terminate the blocking session
28. VACUUM can now remove the dead rows
29. Measuring table bloat directly
30. Accumulate visible bloat
31. Read the bloat after churning
32. Index bloat — a separate problem
33. The freeze age — the wraparound clock
34. Current freeze age on a fresh database
35. Reset the freeze counter with VACUUM FREEZE
36. Freeze age dropped to zero
37. The production monitoring query
38. Run the dashboard on the current state
39. The full picture: long transaction, churn, and the dashboard