VACUUM: Performance - Inefficient Index Phase

Impact

Autovacuum on affected tables may take significantly longer. VACUUM processes tables and their indexes in phases. To avoid impacting your primary workload, autovacuum is limited to using a certain amount of memory. If this is not enough to process all dead rows, the index scan phase will need to be repeated (possibly several times). This is inefficient in terms of CPU and I/O.

Common Causes

• autovacuum_work_mem set too low

  The setting autovacuum_work_mem controls how much memory vacuum can use (if set to -1, it falls back to the value of maintenance_work_mem). If this is set too low, multiple index scan phases may be required.

• VACUUM occurring too infrequently

  If VACUUM is not occurring often enough, many dead rows can accumulate between runs, and will require more memory to clean up.

Solution

Increasing autovacuum_work_mem can allow autovacuum to proceed more efficiently. It's hard to predict how much memory may be needed, but doubling the value (up to the maximum of 1GB) and monitoring the impact is a reasonable approach. If autovacuum_work_mem is set to -1, it will fall back to the current value of maintenance_work_mem. It is generally preferable to set it separately. Update the setting to the recommended value by using ALTER SYSTEM or modifying the parameters in your cloud provider portal.