FM-KED-002 — PostgreSQL Table and Index Bloat (Missing or Ineffective VACUUM)

Severity: S2 — High Recovery Class: B — Standard Recovery Covered by Monthly Support: Yes 

 

 Description 

 PostgreSQL database performance degrades over time due to table and index bloat caused by insufficient or ineffective VACUUM operations. 

 This issue manifests gradually and is commonly observed on systems with high write activity, long-running transactions, or misconfigured autovacuum settings. 

 

 Typical Symptoms 

 Slow queries without obvious query plan changes Increased disk usage on database volumes Tables or indexes significantly larger than expected Elevated I/O usage Application timeouts under normal load 

 

 Diagnostic Checklist 

 Identify Database Size and Largest Tables 

 SELECT

 relname AS table_name,

 pg_size_pretty(pg_total_relation_size(relid)) AS total_size

FROM pg_catalog.pg_statio_user_tables

ORDER BY pg_total_relation_size(relid) DESC

LIMIT 10; 

 Check Autovacuum Activity 

 SELECT

 relname,

 last_vacuum,

 last_autovacuum,

 n_dead_tup

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC; 

 

 Recovery Procedure 

 Follow steps carefully . Some operations are I/O intensive. 

 

 1. Run Manual VACUUM (Non-blocking) 

 VACUUM (VERBOSE, ANALYZE); 

 Recommended for moderate bloat and active systems. 

 

 2. Vacuum Specific Tables 

 VACUUM (VERBOSE, ANALYZE) table_name; 

 Use when bloat is localized. 

 

 3. Reclaim Disk Space (Blocking) 

 VACUUM FULL table_name; 

 ⚠️ Locks the table for the duration of the operation ⚠️ Use during maintenance windows only 

 

 4. Reindex Bloated Indexes 

 REINDEX TABLE table_name; 

 Or concurrently, when supported: 

 REINDEX INDEX CONCURRENTLY index_name; 

 

 

 Preventive Notes 

 Ensure autovacuum is enabled and properly tuned Monitor n_dead_tup growth over time Avoid long-running transactions Schedule periodic maintenance for write-heavy tables 

 

 Operational Notes 

 Disk space reclaimed by VACUUM is reusable by PostgreSQL, not always returned to the OS VACUUM FULL physically rewrites tables and should be used sparingly