Estimate table bloat

Command

SELECT schemaname, relname AS table_name, n_dead_tup AS dead_rows, n_live_tup AS live_rows, round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > 0 ORDER BY n_dead_tup DESC LIMIT 20;

What it does

Shows the bloat ratio based on dead tuple counts and the last autovacuum time.

When to use it

To determine VACUUM needs and whether autovacuum is keeping up.

Caution

n_dead_tup is an estimate continuously updated from DML counters (it drops after VACUUM; ANALYZE only corrects the estimate); for exact physical bloat the pgstattuple extension (pgstattuple('customer')) is more accurate.