PostgreSQL Table Bloat Excessive

Introduction

PostgreSQL table has excessive bloat when autovacuum not aggressive enough. This guide provides step-by-step diagnosis and resolution.

Symptoms

Typical error output:

WARNING: table "large_table" has excessive bloat
Estimated dead tuples: 5000000, Actual: 4500000
Autovacuum not triggered due to cost delay

Common Causes

1. 1.Autovacuum not aggressive enough for high-churn table
2. 2.Cost limit and delay settings too conservative
3. 3.Long-running transactions preventing vacuum cleanup
4. 4.Freeze map not advancing due to oldest transaction

Step-by-Step Fix

Step 1: Check Current State

SELECT schemaname, relname, n_dead_tup, n_live_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > 1000000;
SELECT pg_stat_get_live_tuples(c.oid) AS live, pg_stat_get_dead_tuples(c.oid) AS dead FROM pg_class c WHERE relname = 'large_table';
SELECT current_setting('autovacuum_vacuum_cost_delay');

Step 2: Identify Root Cause

-- Check for blocking processes
SELECT * FROM pg_stat_activity WHERE state != 'idle';
SELECT * FROM information_schema.processlist WHERE time > 30;

Step 3: Apply Primary Fix

```sql -- Reduce autovacuum cost delay for table ALTER TABLE large_table SET (autovacuum_vacuum_cost_delay = 2); ALTER TABLE large_table SET (autovacuum_vacuum_cost_limit = 10000);

-- Run manual vacuum full during maintenance VACUUM (FULL, VERBOSE) large_table; ```

Step 4: Apply Alternative Fix

```sql -- Alternative fix: Check configuration SELECT * FROM pg_settings WHERE name LIKE '%vacuum%';

-- Adjust parameters dynamically ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 10; SELECT pg_reload_conf();

-- Verify the fix SELECT * FROM pg_stat_user_tables WHERE relname = 'target_table'; ```

Step 5: Verify the Fix

SELECT n_dead_tup, n_live_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'large_table';
SELECT pg_size_pretty(pg_total_relation_size('large_table'));
-- Bloat ratio should decrease

Common Pitfalls

• Running vacuum during peak hours without resource management
• Forgetting to analyze after vacuum for statistics update
• Not monitoring autovacuum progress on large tables
• Setting cost delay too high for high-churn tables

Best Practices

• Schedule maintenance windows for vacuum full operations
• Monitor bloat ratio and autovacuum frequency
• Tune autovacuum parameters per table based on churn rate
• Use pg_stat_progress_vacuum to monitor vacuum progress

Related Issues

• PostgreSQL Autovacuum Not Running
• PostgreSQL Dead Tuple Accumulation
• PostgreSQL Transaction ID Wraparound
• PostgreSQL Table Size Excessive