VACUUM and Bloat Management AI Prompt

Manage table and index bloat and configure VACUUM for this PostgreSQL database.

Database: {{database}}
High-write tables: {{tables}}
Current bloat symptoms: {{symptoms}} (slow queries, large table size, high dead tuple count)

1. Why bloat occurs:
   PostgreSQL uses MVCC (Multi-Version Concurrency Control): UPDATE and DELETE do not modify rows in place — they mark old versions as dead and insert new versions. Dead tuples accumulate until VACUUM reclaims them.

2. Measuring bloat:
   -- Dead tuple count per table
   SELECT relname, n_dead_tup, n_live_tup,
          ROUND(n_dead_tup::NUMERIC / NULLIF(n_live_tup,0) * 100, 2) AS dead_pct
   FROM pg_stat_user_tables
   ORDER BY n_dead_tup DESC
   LIMIT 20;

   -- Estimated table bloat (pgstattuple extension)
   SELECT * FROM pgstattuple('orders');

3. Autovacuum tuning:
   Default thresholds trigger VACUUM when: dead_tuples > 20% of table size.
   For large tables this is too infrequent — 20% of 100M rows = 20M dead tuples before VACUUM runs.

   Tune per high-write table:
   ALTER TABLE orders SET (
     autovacuum_vacuum_scale_factor = 0.01,   -- trigger at 1% dead tuples
     autovacuum_analyze_scale_factor = 0.005,
     autovacuum_vacuum_cost_delay = 2         -- less aggressive I/O throttling for this table
   );

4. Manual VACUUM for immediate relief:
   VACUUM (ANALYZE, VERBOSE) orders;           -- reclaim space, update statistics
   VACUUM (FULL) orders;                       -- full rewrite, reclaims max space (EXCLUSIVE LOCK)
   -- Use FULL only during maintenance windows; it blocks all access

5. pg_repack for VACUUM FULL without downtime:
   pg_repack -t orders --no-order
   - Rebuilds the table in the background without blocking reads or writes
   - Requires the pg_repack extension

6. Index bloat:
   -- Bloated indexes (indexes larger than the data they reference)
   SELECT indexname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
   FROM pg_stat_user_indexes
   ORDER BY pg_relation_size(indexrelid) DESC;

   REINDEX INDEX CONCURRENTLY idx_orders_customer;
   -- Rebuilds the index without locking

Return: bloat measurement queries, autovacuum tuning per table, VACUUM schedule, and pg_repack plan for maintenance-free compaction.