AUTOVACUUM Cleanup Efficiency Is Low

Symptom

AUTOVACUUM requires a large number of I/O operations during cleanup. AUTOVACUUM has its own I/O control mechanism. It reduces the impact of AUTOVACUUM threads on services by controlling the number of page accesses per unit of time. This I/O control mechanism greatly affects the efficiency of VACUUM in large table and large-capacity scenarios.

Fault Locating

For details about how to estimate the cleanup efficiency, see How Do I Estimate the VACUUM Cleanup Rate or How Long It Will Take?.

Workarounds

If AUTOVACUUM cleanup is slow and cannot keep up with the generation of dead rows, dead rows are stacked continuously. In this case, perform the following operations:

1. Modify the AUTOVACUUM parameter: When the system I/O resources are sufficient, you can reduce the value of autovacuum_vacuum_cost_delay to speed up the VACUUM cleanup. The default value of this parameter is 20ms. That is, a forced sleep is triggered when about 20 to 50 pages are scanned, and the duration of each sleep is 20 ms. You are advised to set this parameter to 1ms to improve the cleanup efficiency of background threads.
2. Perform VACUUM manually: If dead rows cannot be reclaimed immediately after the parameter is modified, you can use the view to identify tables with a large number of dead rows and manually perform VACUUM on those tables. Manual execution of VACUUM is not subject to I/O control. Therefore, dead rows can be cleaned up efficiently.