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

Vacuum Monitoring Workflow

Step 1: Check Current Vacuum Activity

Tool: monitor_vacuum_progress
Parameters:
  action: "progress"

What this shows:

• Currently running VACUUM operations (manual and autovacuum)
• Phase of each vacuum (scanning heap, vacuuming indexes, truncating heap, etc.)
• Progress percentage and estimated time remaining
• Which autovacuum workers are active

Vacuum phases explained:

Step 2: Identify Tables Needing Vacuum

Tool: monitor_vacuum_progress
Parameters:
  action: "needs_vacuum"
  schema_name: "public"
  min_dead_tuples: 500
  include_toast: true

What to look for:

Transaction wraparound explained: PostgreSQL uses 32-bit transaction IDs (XIDs). After ~2 billion transactions, old XIDs "wrap around" and data could appear to be in the future. PostgreSQL will force a shutdown at 2^31 - 1 million XIDs to prevent data corruption. Autovacuum normally prevents this by freezing old XIDs, but if vacuum can't keep up, manual intervention is needed.

Step 3: Review Autovacuum Configuration

Tool: monitor_vacuum_progress
Parameters:
  action: "autovacuum_status"

Also check the settings:

Tool: review_settings
Parameters:
  category: "autovacuum"

Key autovacuum parameters:

The scale factor problem: With default autovacuum_vacuum_scale_factor = 0.2, a 100-million-row table needs 20 million dead tuples before autovacuum triggers. This is almost always too high for large tables.

Step 4: Check Recent Vacuum History

Tool: monitor_vacuum_progress
Parameters:
  action: "recent_activity"

Categories:

• never: Table has never been vacuumed (new table or vacuum disabled)
• stale: Last vacuum > 7 days ago
• recent: Last vacuum 1-7 days ago
• fresh: Last vacuum within 24 hours

What to investigate:

• Tables in "never" or "stale" category
• Tables where autovacuum ran recently but dead tuples are still high (vacuum not effective)
• Tables with very frequent vacuum (may indicate excessive churn)

Step 5: Check for Vacuum Blockers

If vacuum is not making progress, check for long-running transactions:

Tool: get_active_queries
Parameters:
  include_idle: true
  min_duration_seconds: 60

Common vacuum blockers:

• idle in transaction sessions hold back the visibility horizon
• Long-running queries prevent dead tuple cleanup for rows they might still see
• Unused replication slots prevent WAL cleanup and can prevent vacuum from removing dead tuples

Tuning Recommendations

For High-Churn OLTP Tables

Tables with millions of INSERTs/UPDATEs/DELETEs per day:

-- Aggressive per-table settings
ALTER TABLE schema.hot_table SET (
  autovacuum_vacuum_scale_factor = 0.01,       -- Trigger at 1% dead tuples
  autovacuum_vacuum_threshold = 1000,           -- Or at least 1000 dead tuples
  autovacuum_analyze_scale_factor = 0.005,      -- Analyze at 0.5% changes
  autovacuum_vacuum_cost_delay = 0,             -- No throttling for this table
  autovacuum_vacuum_cost_limit = 1000           -- Higher work limit
);

For Large Analytical Tables

Tables that are mostly read with occasional bulk loads:

-- Less frequent but thorough vacuum
ALTER TABLE schema.fact_table SET (
  autovacuum_vacuum_scale_factor = 0.05,       -- 5% threshold (OK for large read-mostly tables)
  autovacuum_vacuum_threshold = 10000,
  autovacuum_enabled = true,                    -- Ensure it's on
  autovacuum_freeze_min_age = 100000000,        -- Freeze less aggressively
  autovacuum_freeze_table_age = 300000000       -- Full-table freeze less often
);

For Append-Only / Time-Series Tables

Tables where old data is never updated:

-- Minimal vacuum needed since there are few dead tuples
ALTER TABLE schema.log_table SET (
  autovacuum_vacuum_scale_factor = 0.1,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_freeze_min_age = 50000000,         -- Freeze sooner (data won't change)
  fillfactor = 100                               -- Pack pages full (no updates expected)
);

Global Autovacuum Tuning

-- For servers with SSDs and sufficient I/O capacity
ALTER SYSTEM SET autovacuum_max_workers = 6;
ALTER SYSTEM SET autovacuum_naptime = '15s';
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '0ms';    -- No throttling on SSD
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000;
ALTER SYSTEM SET vacuum_cost_limit = 1000;

-- For wraparound prevention
ALTER SYSTEM SET autovacuum_freeze_max_age = '300000000'; -- 300M (default 200M)

-- Apply: SELECT pg_reload_conf();

Output Format

Current Vacuum Status

Autovacuum Health

• Workers: 2/3 active
• Configuration assessment
• Tables exceeding thresholds

Recommended Changes

Per-table and global tuning SQL, ordered by priority.

Wraparound Risk Assessment

Tables ranked by XID age with time-to-wraparound estimate.

Emergency Procedures

Transaction Wraparound Emergency (XID age > 1.2 billion)

-- 1. Cancel non-essential queries
SELECT pg_cancel_backend(pid) FROM pg_stat_activity
WHERE state != 'idle' AND query NOT LIKE 'autovacuum%'
AND backend_start < now() - interval '1 hour';

-- 2. Run manual VACUUM FREEZE on the affected table
VACUUM (FREEZE, VERBOSE) schema.table_name;

-- 3. Monitor progress
SELECT * FROM pg_stat_progress_vacuum;

Autovacuum Completely Stalled

-- 1. Check for blocking transactions
SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE xact_start < now() - interval '1 hour'
ORDER BY xact_start;

-- 2. Terminate long idle-in-transaction sessions (careful!)
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND xact_start < now() - interval '1 hour';

-- 3. Verify autovacuum is enabled
SHOW autovacuum;

-- 4. Force autovacuum to recheck
SELECT pg_reload_conf();

Important Notes

• Never disable autovacuum (autovacuum = off). It prevents transaction wraparound.
• VACUUM FULL is rarely needed and locks the table. Prefer regular VACUUM or pg_repack.
• Per-table settings override global settings and are stored in pg_class.reloptions.
• After bulk DELETE operations, run manual VACUUM to promptly reclaim space.
• Monitoring tools in this skill are read-only and safe for production.
• Autovacuum parameter changes via ALTER SYSTEM require pg_reload_conf() (no restart).
• Per-table settings via ALTER TABLE ... SET take effect on the next autovacuum run.

Iterative Verification

After applying vacuum tuning changes:

1. After autovacuum tuning: Monitor with monitor_vacuum_progress action: "progress" over the next hours to confirm autovacuum is now running on the target tables.
2. After manual VACUUM: Re-run monitor_vacuum_progress action: "needs_vacuum" to confirm dead tuples were cleaned up.
3. After resolving blocking transactions: Re-run get_active_queries with include_idle: true to confirm no more blockers, then re-check vacuum progress.
4. After VACUUM FREEZE: Read pgtuner://table/{schema}/{table_name}/stats to verify XID age was reset.

When to Stop and Ask the User

• If XID age > 1 billion: "CRITICAL: Transaction wraparound risk on table X. This requires immediate VACUUM FREEZE. Shall I provide the emergency procedure? All other work should be paused."
• If autovacuum is disabled: "Autovacuum is turned OFF. This is extremely dangerous as it can lead to transaction wraparound and database shutdown. Shall I enable it immediately?"
• If vacuum blockers are long-running application transactions: "Vacuum progress is blocked by long-running transactions from the application. This requires application-level fixes. Would you like me to identify the specific sessions?"
• If the user asks about VACUUM FULL: "VACUUM FULL rewrites the entire table with an exclusive lock. For a production table, consider pg_repack instead. Do you have a maintenance window?"

Cross-References to Other Skills

• Bloat analysis and remediation: Use pg-bloat-analysis skill
• Lock contention blocking vacuum: Use pg-lock-diagnosis skill
• Connection issues (idle-in-transaction): Use pg-connection-analysis skill
• Autovacuum settings as part of config tuning: Use pg-config-tuning skill

PostgreSQL Version Notes

Production Safety

• All monitor_vacuum_progress calls are read-only
• review_settings is read-only
• get_active_queries is read-only
• Emergency pg_cancel_backend / pg_terminate_backend calls should only be used with explicit user approval
• Autovacuum tuning via ALTER SYSTEM / ALTER TABLE ... SET does not require a restart