Christophe Pettus: Parallel Autovacuum: It’s Not About The CPU

Why It Matters

Parallel autovacuum can cut vacuum times for heavily‑indexed tables, freeing resources for user queries, but misconfiguration can waste parallel slots and degrade overall performance.

Key Takeaways

• Parallel autovacuum only speeds up tables with many or costly indexes
• Vacuum remains I/O‑bound; extra workers won’t bypass storage limits
• Workers share max_parallel_workers pool, reducing slots for query parallelism
• Default off is best for most clusters; enable per‑table when justified
• Use pg_stat_progress_vacuum to confirm index‑cleanup workers are active

Pulse Analysis

PostgreSQL 19’s parallel autovacuum extends the existing parallel vacuum machinery to the automatic vacuum daemon. The change introduces two GUCs—autovacuum_max_parallel_workers and the per‑table autovacuum_parallel_workers—allowing the index‑cleanup phase to be distributed among several processes. Crucially, the heap scan that builds the dead‑tuple list stays single‑threaded, so only tables with multiple or heavyweight indexes, such as GIN or BRIN structures, see measurable speed gains. This nuance is often missed by DBAs who assume any parallelism automatically translates to faster vacuuming.

Performance gains hinge on storage bandwidth rather than raw CPU. Vacuuming is predominantly I/O‑bound: reading heap pages and index pages from disk dominates runtime. On typical cloud‑hosted PostgreSQL instances—RDS, Cloud SQL, or similar—provisioned IOPS often sit at the ceiling for a 200 GB table, meaning additional workers merely queue behind the same storage bottleneck. Only environments equipped with high‑throughput NVMe, gp3 volumes sized for the workload, or local SSDs can fully exploit parallel workers without saturating the I/O path. Otherwise, the extra processes increase context switches and consume valuable parallel slots needed for user queries.

Operationally, the safest default is to keep parallel autovacuum disabled cluster‑wide. For tables that truly need it—large partitioned tables or wide jsonb datasets with multiple GIN indexes—set a modest per‑table parallel worker count, typically two, and ensure autovacuum_max_parallel_workers matches the highest per‑table setting. Verify that max_parallel_workers and max_worker_processes are sized to accommodate both vacuum and query parallelism. Ongoing validation via pg_stat_progress_vacuum and autovacuum duration metrics will confirm whether the feature delivers the intended reduction in wall‑clock vacuum time.