debugsqlpostgresqlMajor
Table bloat from dead tuples: when VACUUM is not keeping up
Viewed 0 times
table bloatdead tuplesVACUUMautovacuumMVCCpg_repackn_dead_tup
Problem
Tables that receive many UPDATEs or DELETEs grow large on disk even as visible row count stays constant. Queries slow down as PostgreSQL reads more 8KB pages filled with dead tuples.
Solution
Diagnose and address table bloat:
-- Check dead tuple accumulation:
SELECT relname, n_live_tup, n_dead_tup,
round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
-- Force an immediate VACUUM:
VACUUM (VERBOSE, ANALYZE) high_churn_table;
-- Reclaim disk space (requires exclusive lock, use pg_repack for zero-downtime):
VACUUM FULL high_churn_table;
-- Tune autovacuum aggressiveness per table:
ALTER TABLE high_churn_table SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 100
);
-- Check dead tuple accumulation:
SELECT relname, n_live_tup, n_dead_tup,
round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
-- Force an immediate VACUUM:
VACUUM (VERBOSE, ANALYZE) high_churn_table;
-- Reclaim disk space (requires exclusive lock, use pg_repack for zero-downtime):
VACUUM FULL high_churn_table;
-- Tune autovacuum aggressiveness per table:
ALTER TABLE high_churn_table SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 100
);
Why
PostgreSQL uses MVCC: UPDATEs write new row versions and mark old ones dead. VACUUM reclaims dead tuples but does not (by default) return space to the OS. Tables grow until VACUUM FULL or pg_repack is run.
Gotchas
- VACUUM FULL acquires an AccessExclusiveLock; use pg_repack for live production tables
- A long-running transaction prevents VACUUM from cleaning tuples it could see
- autovacuum_vacuum_cost_delay throttles I/O; lower it for write-heavy tables
- pg_stat_user_tables.n_dead_tup resets after VACUUM; monitor trends over time
Code Snippets
Measure actual physical bloat with pgstattuple
-- Estimate physical bloat (requires pgstattuple extension):
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('high_churn_table');Context
High-update-rate tables like session stores, event queues, or order status tables
Revisions (0)
No revisions yet.