Christophe Pettus: All Your GUCs in a Row: Autovacuum_max_workers

Why It Matters

Understanding the interplay between worker count and cost limit prevents wasted resources and ensures vacuum operations keep pace with high‑volume, multi‑tenant databases. Proper tuning directly impacts storage efficiency and query performance in production environments.

Key Takeaways

• Default autovacuum_max_workers is 3; changing requires restart.
• Cost limit splits I/O budget across workers, limiting speed gains.
• More workers help only with many tables or mixed‑size workloads.
• Raise autovacuum_vacuum_cost_limit proportionally when adding workers.
• Autovacuum workers use shared memory but not max_connections.

Pulse Analysis

PostgreSQL’s autovacuum process is the engine that reclaims dead tuples and maintains table statistics, a silent but critical component of database health. By default, the system runs up to three autovacuum workers, a setting stored in the postmaster configuration and therefore only effective after a restart. Administrators often reach for autovacuum_max_workers when they see long‑running vacuum jobs, assuming that more parallel workers will accelerate cleanup. However, the real throttle is autovacuum_vacuum_cost_limit, which defines a shared I/O budget for all workers. When that budget stays fixed, each additional worker receives a smaller slice, so the total vacuum throughput remains roughly constant.

The practical impact emerges in environments with a large number of tables or partitions, such as multi‑tenant SaaS platforms or data warehouses with hundreds of shards. In those scenarios, a handful of workers can become a bottleneck, queuing many tables and delaying cleanup of small, frequently updated tables. Adding workers can alleviate this queue, but only if the cost limit is scaled up to preserve the per‑worker I/O allowance. A common heuristic is to double autovacuum_vacuum_cost_limit when doubling the worker count, adjusting further based on the storage subsystem’s capabilities—NVMe drives can tolerate aggressive settings that older SANs could not.

From an operational standpoint, autovacuum workers do not consume max_connections slots, but they do allocate shared memory and proc‑array entries at startup, a modest overhead for most systems. The safest default remains three workers, with any increase justified by concrete log evidence of tables waiting for a slot. When scaling up, always raise the cost limit in tandem; otherwise, the change merely fragments the vacuum budget without delivering performance gains, leaving the database’s storage efficiency unchanged.