Christophe Pettus: All Your GUCs in a Row: Autovacuum_worker_slots

Why It Matters

The ability to adjust autovacuum concurrency without downtime removes a long‑standing operational bottleneck, especially for large multi‑tenant deployments, and improves overall database performance and availability.

Key Takeaways

• PG 18 adds autovacuum_worker_slots to pre‑allocate worker memory
• autovacuum_max_workers becomes SIGHUP‑able, adjustable without restart
• Default 16 slots cover most workloads; memory overhead negligible
• Live tuning reduces maintenance windows for multi‑tenant PostgreSQL clusters

Pulse Analysis

PostgreSQL’s autovacuum process is a critical background task that reclaims space and prevents table bloat, but until version 18 administrators faced a hard stop when trying to increase concurrency. The new autovacuum_worker_slots parameter reserves a fixed number of shared‑memory slots at server start‑up, similar to max_connections. Because the allocation occurs before the postmaster launches, changing this setting still requires a restart, but the default of 16 slots is deliberately generous, covering the vast majority of workloads with negligible memory impact.

The real breakthrough lies in making autovacuum_max_workers SIGHUP‑reloadable. DBAs can now issue a pg_reload_conf() or edit postgresql.conf and have the change take effect instantly, provided the new value does not exceed the pre‑allocated slots. This live‑tuning capability eliminates the maintenance windows that previously accompanied scaling from three to six or more workers in large, multi‑tenant environments. Operators can monitor vacuum lag, adjust the worker count, and observe I/O and throughput effects in real time, ensuring optimal performance without risking service interruption.

Best‑practice guidance recommends leaving autovacuum_worker_slots at its default of 16 unless you anticipate an exceptionally large fleet of databases that might need more concurrent vacuums. In most cases, the focus should be on fine‑tuning autovacuum_max_workers based on observed table wait times and disk activity. This approach mirrors PostgreSQL’s long‑standing design pattern of pre‑allocating resources—such as max_connections and max_wal_senders—and then dynamically adjusting active usage, reinforcing the platform’s reputation for flexible, high‑availability operations.