pg-vacuum-tuning

PostgreSQL Vacuum Tuning

This skill guides monitoring, troubleshooting, and tuning of PostgreSQL vacuum operations using pgtuner-mcp tools.

When to Use This Skill

Use this skill when the user:

• Asks about autovacuum configuration or tuning
• Reports autovacuum running too slowly or too aggressively
• Mentions transaction ID wraparound warnings
• Sees tables accumulating dead tuples
• Asks "why is autovacuum always running?" or "why isn't autovacuum cleaning my table?"
• Reports table bloat growing over time
• Wants to understand vacuum progress on a long-running operation
• Asks about VACUUM FREEZE or preventing forced shutdowns

MCP Resources Available

• pgtuner://settings/autovacuum -- Current autovacuum configuration (lightweight)
• pgtuner://health/bloat -- Quick bloat assessment
• pgtuner://table/{schema}/{table_name}/stats -- Per-table dead tuple counts and vacuum timestamps

Related MCP Prompt

This skill is related to the health_check MCP Prompt (wraparound dimension) and the vacuum aspects of pg-bloat-analysis skill.

Prerequisites

• The pgtuner-mcp server must be connected with a valid DATABASE_URI
• Access to pg_stat_activity, pg_stat_user_tables, pg_stat_progress_vacuum

Agent Decision Tree

User asks about vacuum
  |
  +--> "Why is autovacuum always running?"
  |    --> Start Step 1 (progress), then Step 3 (autovacuum config)
  |    --> May be normal for high-churn tables. Check if it's completing.
  |
  +--> "Why isn't my table being vacuumed?"
  |    --> Start Step 2 (needs_vacuum), then Step 3 (autovacuum config)
  |    --> Check if scale_factor threshold is too high for large tables
  |
  +--> "Transaction wraparound warning"
  |    --> URGENT: Start Step 2 (needs_vacuum), check XID age
  |    --> IF XID age > 1.2B: Go to Emergency Procedures immediately
  |
  +--> "How long will this vacuum take?"
  |    --> Start Step 1 (progress) to see current phase and completion %
  |
  +--> "General vacuum tuning"
       --> Follow Steps 1-4 in order, then Tuning Recommendations