vacuum-autovacuum

Analyze PostgreSQL VACUUM and autovacuum subsystem behavior for storage reclamation.

Updated Jun 1, 2026

Search Tags:#postgresql #performance-tuning #autovacuum #vacuum #database-internals #storage-reclamation

Ready to run

npx skills add https://github.com/matejformanek/postgres-claude --skill vacuum-autovacuum

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: vacuum-autovacuum
Source: https://github.com/matejformanek/postgres-claude/tree/main/.claude/skills/vacuum-autovacuum
Command: npx skills add https://github.com/matejformanek/postgres-claude --skill vacuum-autovacuum

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

This skill resolves uncertainty regarding PostgreSQL storage reclamation, helping developers and DBAs debug bloat, tune autovacuum performance, and understand the complex three-phase vacuum lifecycle.

Core Features & Use Cases

Internals Guidance: Provides authoritative explanations of heap scanning, HOT pruning, and TidStore radix-tree operations.
Performance Tuning: Assists in configuring autovacuum thresholds, cost limits, and wraparound protection settings.
Use Case: When a production database experiences unexpected bloat or high I/O during maintenance, use this skill to analyze the vacuum state machine and identify if tuning autovacuum parameters or adjusting maintenance_work_mem is required.

Quick Start

Use the vacuum-autovacuum skill to explain the three-phase heap scan process and identify why autovacuum workers are not triggering for a specific table.

Frequently Asked Questions about vacuum-autovacuum

High-intent search queries and answers about installing and using this skill.

FAQPage Schema

How does PostgreSQL autovacuum scheduling decide when to trigger a worker?▼

PostgreSQL autovacuum scheduling triggers workers based on table-level thresholds comparing dead tuples and total row counts. The autovacuum launcher evaluates these thresholds alongside cost limit settings to allocate worker processes for storage reclamation and wraparound prevention.

What is the three-phase VACUUM lifecycle in PostgreSQL heap scanning?▼

The PostgreSQL VACUUM lifecycle consists of heap scanning with HOT pruning, index cleanup, and final heap truncation phases. This three-phase process handles dead tuple removal, cleans associated index references, and reclaims physical storage space at the block level.

How do I tune autovacuum parameters to reduce table bloat in PostgreSQL?▼

Tune autovacuum parameters by adjusting autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor, and maintenance_work_mem. Lowering scale factors triggers workers more frequently, while increasing memory limits allows larger TidStore radix-tree operations for efficient heap pruning.

Why are autovacuum workers not triggering for a specific PostgreSQL table?▼

Autovacuum workers fail to trigger when the table's dead tuple count does not exceed the configured autovacuum threshold plus scale factor percentage of total rows. Additionally, an open transaction or long-running query maintaining a high xmin horizon can prevent tuple removal.

Can I prevent transaction ID wraparound by adjusting autovacuum settings?▼

Prevent transaction ID wraparound by configuring autovacuum_freeze_max_age and autovacuum settings. Autovacuum automatically prioritizes tables approaching the wraparound limit, triggering aggressive freezes to advance the xmin horizon and protect database availability.

What are the limitations of relying solely on autovacuum for index cleanup?▼

Autovacuum limitations include potential I/O spikes during index cleanup and delayed execution under high maintenance_work_mem constraints. For heavily bloated indexes, manual VACUUM or REINDEX operations may be required since autovacuum cost limiting can stall.