VACUUM: Performance - Inefficient Index Phase
Detects when autovacuum runs in this server are forced to perform inefficient multiple index scan phases due to limited configured memory, and creates an issue with severity "warning". Triggers when at least
1 autovacuum run in the last 24h have had multiple index scan phases. Resolves automatically once fewer than
1 autovacuum run in the last 24h have had multiple index scan phases.
Ignores situations where configured autovacuum memory is already set to the maximum possible value of
This check is enabled by default. These parameters can be tuned in the Configure section of the Alerts & Check-Up page.
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.
autovacuum_work_memset too low
autovacuum_work_memcontrols 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.
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.
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →