Table Bloat Diagnosis and Remediation

warningProactive Health

Tables have grown much larger than expected due to bloat from dead tuples, impacting query performance and storage costs.

Prompt: “One of my PostgreSQL tables is taking up 50GB of disk space but only has 10GB of actual data when I dump it. How do I measure bloat and safely reclaim this space?”

Agent Playbook

When an agent encounters this scenario, Schema provides these diagnostic steps automatically.

When diagnosing table bloat in PostgreSQL, start by quantifying the actual bloat percentage using dead versus live tuple counts, then investigate why autovacuum isn't keeping up — check its run frequency and look for long-running transactions that block cleanup. Based on bloat severity, choose between manual VACUUM (< 30%), scheduled maintenance VACUUM (30-50%), or disruptive VACUUM FULL/pg_repack (> 50%), and tune autovacuum settings to prevent recurrence.

1Quantify the bloat percentage

Start by calculating bloat as (`dead-tuple` / `live-tuple`) * 100 using the `n_dead_tup` and `n_live_tup` metrics from pg_stat_user_tables, or check the `postgresql-table-bloat` metric directly if available. In your case with 50GB disk but 10GB dump, you're looking at roughly 80% bloat, which is well above the 50% threshold where manual intervention is required. Compare the `postgresql-table-size` metric to your expected data volume to confirm the gap. This measurement tells you how aggressive your remediation needs to be.

Table bloat exceeds 50% requiring manual VACUUM table bloat exceeding healthy threshold n_dead_tupn_live_tuptable_bloattable_sizepostgresql.table.size

2Check autovacuum effectiveness and history

Look at `postgresql-last-vacuum-age` to see when the table was last vacuumed — if it's been days or weeks, autovacuum isn't keeping up with your workload. Check the `postgresql-autovacuumed` counter to verify autovacuum is actually running on this table, not just configured globally. If autovacuum is running but bloat is still accumulating, the default thresholds (typically 50 base + 20% of table) are too high for a high-churn table. This tells you whether the problem is autovacuum not running at all versus not running frequently enough.

autovacuum not reclaiming space effectively Table bloat from suboptimal autovacuum settings slows query performance postgresql.last_vacuum_ageautovacuumedn_dead_tup

3Look for long-running transactions blocking cleanup

Query pg_stat_activity for transactions older than a few minutes, especially idle-in-transaction sessions — even a single long-running transaction prevents VACUUM from reclaiming tuples deleted after that transaction started. Check `postgresql-locks` for any locks that might be blocking autovacuum workers from accessing the table. A transaction that's been open for hours can create a bloat backlog of gigabytes on a busy table, so this is often the root cause when autovacuum settings look reasonable but bloat keeps growing.

long-running transactions preventing cleanup postgresql.locksn_dead_tup

4Select the appropriate remediation strategy

With 80% bloat, regular VACUUM won't reclaim disk space — it only marks space for reuse but doesn't shrink the table file. You need either VACUUM FULL (requires exclusive lock, table unavailable during operation) or pg_repack (online, minimal locking, but requires extension install and free disk space equal to table size). I typically use pg_repack for production tables during business hours and VACUUM FULL during maintenance windows. Check `postgresql-locks` before running either to ensure no active long transactions will cause the operation to block or fail.

Table bloat wastes disk space without autovacuum full or pg_repack need for VACUUM FULL or pg_repack storage cost impact from bloat table_bloatpostgresql.lockspostgresql.table.size

5Rebuild indexes and tune autovacuum to prevent recurrence

After reclaiming table space, rebuild indexes with REINDEX CONCURRENTLY (PostgreSQL 12+) since they bloat alongside tables and degrade query performance even after table cleanup. Then tune autovacuum for this table specifically: lower autovacuum_vacuum_threshold to 50 and autovacuum_vacuum_scale_factor to 0.05-0.10 so it triggers more frequently on high-churn tables. Monitor `dead-tuple` growth and `postgresql-autovacuumed` frequency over the next few days to verify the new settings prevent bloat from building up again.

Index bloat wastes disk space and degrades query performance Table bloat from suboptimal autovacuum settings slows query performance Table bloat increases sequential scan cost linearly autovacuumedn_dead_tupn_live_tuprelation_pages