Table Bloat (Dead Tuples Accumulation)

Scenario

A DBA notices that a high-traffic orders table consuming 40 GB of disk space should contain only ~2 GB of actual live data. The table receives heavy UPDATE traffic throughout the day. Queries are running slower than normal despite existing indexes, and application engineers report increasing read latency. No data was bulk-inserted recently.

How to Identify

Conditions:

• pg_stat_user_tables shows high n_dead_tup relative to n_live_tup
• Table size (pg_relation_size) is far larger than estimated live data size
• Slow sequential scans and index scans on the table
• last_autovacuum is NULL, very old, or autovacuum frequency is insufficient for the churn rate
• pgstattuple extension shows high dead_tuple_percent

Analysis Steps

-- 1. Identify tables with high dead tuple counts
SELECT relname,
       n_dead_tup,
       n_live_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  20;

-- 2. Compare physical size vs estimated live data size
SELECT relname,
       pg_size_pretty(pg_relation_size(relid))       AS table_size,
       pg_size_pretty(pg_total_relation_size(relid))  AS total_size,
       n_live_tup,
       pg_size_pretty((n_live_tup * 200)::BIGINT)    AS estimated_live_size
FROM   pg_stat_user_tables
WHERE  relname = 'orders_bloat'
ORDER  BY pg_relation_size(relid) DESC;

-- 3. Check autovacuum thresholds vs dead tuple counts
SELECT relname, n_dead_tup,
       current_setting('autovacuum_vacuum_threshold')  AS av_threshold,
       current_setting('autovacuum_vacuum_scale_factor') AS av_scale_factor
FROM   pg_stat_user_tables
WHERE  relname = 'orders_bloat';

-- 4. Use pgstattuple for accurate bloat measurement (requires extension)
-- CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders_bloat');

-- 5. Check if autovacuum is currently running on this table
SELECT pid, query, state, wait_event_type, wait_event
FROM   pg_stat_activity
WHERE  query ILIKE '%autovacuum%'
  AND  query ILIKE '%orders_bloat%';

Pitfalls

• VACUUM does not shrink table files — it only marks dead space as reusable. Use VACUUM FULL or pg_repack to reclaim disk space.
• VACUUM FULL takes an ACCESS EXCLUSIVE lock — all reads and writes are blocked during the operation. Schedule in a maintenance window or use pg_repack for online reclamation.
• autovacuum_vacuum_scale_factor (default 0.2) means autovacuum triggers only after 20% of the table is dead. On a 50M-row table, that is 10M dead tuples before any cleanup runs. Lower this per-table for high-churn tables.
• Dead tuples do not disappear even after rows are deleted — they persist until VACUUM runs. Heavy UPDATE workloads accumulate dead tuples faster than VACUUM can remove them if cost settings are too conservative.
• Do not confuse ANALYZE with VACUUM — ANALYZE updates statistics only; it does not remove dead tuples.

Resolution Approach

Dead tuples accumulate when rows are updated or deleted — PostgreSQL uses MVCC and keeps old row versions until VACUUM removes them. The fix depends on urgency: