vacuum-autovacuum

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.

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.

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.

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.

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.

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.