pg-bloat-analysis

PostgreSQL Bloat Analysis

This skill guides detection, analysis, and remediation of table and index bloat in PostgreSQL databases using pgtuner-mcp tools.

When to Use This Skill

Use this skill when the user:

• Reports tables or indexes growing unexpectedly large
• Asks about database bloat, dead tuples, or fragmentation
• Mentions disk space running low
• Sees slow sequential scans on tables that should be small
• Asks "why is my table so large?" or "how do I reclaim disk space?"
• Notices VACUUM not keeping up or autovacuum running constantly
• Reports degraded performance after bulk DELETE or UPDATE operations

MCP Resources Available

• pgtuner://health/bloat -- Quick bloat health assessment (lightweight)
• pgtuner://table/{schema}/{table_name}/stats -- Table statistics including dead tuple counts
• pgtuner://settings/autovacuum -- Autovacuum configuration (relevant for root cause analysis)

Related MCP Prompt

This skill is referenced by the health_check MCP Prompt (Step 6: Quick Bloat Scan). For dedicated bloat investigation, follow this skill's full workflow.

Prerequisites

• The pgtuner-mcp server must be connected with a valid DATABASE_URI
• Required: pgstattuple extension (for accurate bloat measurement)
• Without pgstattuple: The tools fall back to statistical estimation (less accurate)

Pre-Flight Checks

• If analyze_table_bloat or get_bloat_summary returns an error about pgstattuple, inform the user: "The pgstattuple extension is not installed. Bloat measurements will be estimated from pg_stat_user_tables statistics, which is less accurate. For precise measurements, install pgstattuple: CREATE EXTENSION pgstattuple;"

Agent Decision Tree

User reports bloat concerns
  |
  +--> General "how bloated is my database?"
  |    --> Start with Step 1 (get_bloat_summary)
  |
  +--> Specific table is too large
  |    --> Skip to Step 2 (analyze_table_bloat) for that table
  |
  +--> Disk space running low
  |    --> Start with Step 1, then prioritize high-bloat tables
  |
  +--> After Step 1, triage results:
       |
       +--> Dead tuple ratio > 20% on any table?
       |    --> Deep-dive Step 2, then investigate root cause (Step 5)
       |
       +--> XID age > 800M on any table?
       |    --> URGENT: redirect to pg-vacuum-tuning emergency procedures
       |
       +--> Index bloat > 40%?
       |    --> Step 3 (index bloat analysis), recommend REINDEX
       |
       +--> Autovacuum not keeping up?
            --> Step 4 (vacuum status), then pg-vacuum-tuning skill