Accelerating VACUUM in Postgres
Couple of days ago we received a question in our blog about whether it is possible to speed up the VACUUM for large tables once the process is launched.
In short, once the VACUUM starts working it is not possible to influence its speed.
The main reason for this, is that the majority of resources in this process directed towards disk operations and these are not very speedy. Besides, there is still a need to leave enough resources towards routine database processes.
With that in mind, it is possible to change system preferences that will allow individual VACUUM workers to speed up its performance.
To do this you need to deploy quite ”aggressive” configurations. This way VACUUM processes (AUTOVACUUM to be exact) will be launched more frequently, but with each launch these processes will require less actions that in turn will reduce overall working time.
Here is the setup process for AUTOVACUUM parameters:
- Allocation of appropriate number of
autovacuum_max_workers(the default is 3, which is low, so something like 10 would be more appropriate). - Increasing frequency of AUTOVACUUM checks launch (
autovacuum_naptime= 1s, which the default is – 1min). - Lowering the threshold that triggers AUTOVACUUM launch (
autovacuum_vacuum_threshold= 50,autovacuum_analyze_threshold= 10,autovacuum_vacuum_scale_factor= 0.05,autovacuum_analyze_scale_factor= 0.05). - Lowering the threshold that triggers FREEZE (
vacuum_freeze_min_age= 5000000,vacuum_freeze_table_age= 15000000)
Parameter autovacuum_max_workers requires service restart while others can be amended without service disruption.
Using the above parameters will increase the frequency in which AUTOVACUUM visits tables while breaking down its work to smaller parts. Following these changes the launch of VACUUM will require a lower share of workload and will, in turn, speed up the overall command execution.
In addition, a more “aggressive” parameter values can be set up for the VACUUM command (vacuum_cost_delay = 0, vacuum_cost_page_hit = 0, vacuum_cost_page_miss = 1, vacuum_cost_page_dirty = 10, vacuum_cost_limit = 100). This allows VACUUM to utilize more resources at the expense of other server processes and the decreased pause limit between server workloads.
Let me know if you have any questions in the comments below.
Want to learn more about AUTOVACUUM? Check out our blog posts on the topic and this talk from PGCon.
