Table Bloat (Dead Tuples Above Threshold)

Overview

Postgres uses MVCC (Multi-Version Concurrency Control) to serve reads without blocking writes. Every UPDATE creates a new row version; the old version becomes a dead tuple. VACUUM reclaims those dead tuples so the space can be reused. When VACUUM can't keep up with the write rate—due to long transactions, aggressive autovacuum thresholds, or high write volume—dead tuples accumulate and the table grows larger than its live data requires.

High bloat means more pages to scan in sequential scans, more I/O for index maintenance, and inflated pg_total_relation_size. For tables with very high write rates, tuning autovacuum_vacuum_scale_factor down to 1% is usually sufficient to prevent runaway bloat.

Detection SQL

Run this query with a read-only role. No writes, no locks.

-- Tables where dead tuples exceed 10% of live tuples and absolute count > 10,000
SELECT
  schemaname,
  relname                                           AS table_name,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1)
                                                    AS dead_pct,
  last_autovacuum,
  last_manual_vacuum,
  pg_size_pretty(pg_total_relation_size(relid))     AS total_size
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
  AND n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) > 0.10
ORDER BY n_dead_tup DESC;

Fix SQL

Replace placeholders in <angle brackets> with values from the detection output before running.

-- Trigger autovacuum more aggressively for a specific table
ALTER TABLE <table_name> SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 1000
);

-- Or run manual VACUUM (online, no lock)
VACUUM (VERBOSE, ANALYZE) <table_name>;

-- If bloat is extreme and autovacuum isn't keeping up, reclaim disk with:
VACUUM FULL <table_name>;  -- takes exclusive lock, use in maintenance window

Worked example

Common false positives

• Tables freshly loaded via COPY or INSERT with no prior deletes — n_dead_tup may lag statistics collection; run ANALYZE to refresh.
• Temporary tables and unlogged tables have different vacuum behaviour and are excluded from this rule.