Controlling Autovacuum

Unlike other database systems, Postgres makes the cleanup process visible and tunable to users. Autovacuum performs recycling of old rows and updates optimizer statistics. It appears in ps command output, the pg_stat_activity system view, and optionally in the server logs via log_autovacuum_min_duration.

Postgres also allows fine-grained control over the autovacuum cleanup process. Occasionally users find that cleanup is slowing the system down, and rather than modifying the behavior of autovacuum, they decide to turn it off via the autovacuum setting.

However, turning off autovacuum can cause problems. Initially the system will run faster since there is no cleanup overhead, but after a while old rows will clog up user tables and indexes, leading to increasing slowness. Once that happens, you can turn on autovacuum again, and it will recycle the old rows and free up space, but there will be much unused space that can't be reused quickly, or perhaps ever.

Turning off autovacuum is kind of like someone trying to save time by not changing the engine oil in their car. They are correct that for a while their car will be in the shop less often, but ultimately the car will be in the shop for a long time, or will stop working. Therefore, don't turn off autovacuum.

Another problem is that while autovacuum is turned off, optimizer statistics were not being updated, perhaps causing slower execution plans. Fortunately restarting autovacuum does fully fix that problem.

Now that we know that turning off autovacuum is a bad idea, what are the options if administrators want to reduce the overhead of autovacuum. Well, first, there are many autovacuum tuning parameters that allow autovacuum activity to happen less often, or consume fewer resources while running.

A more aggressive, and perhaps creative, approach is to change when autovacuum runs. Most systems have busy times and idle times. Sometimes this can be determined by the time of the day or the day of the week, e.g. Sundays, 0200-0500. In other cases it can be determined by the system's load average or number of active sql sessions inpg_stat_activity.

No matter how it is determined, wouldn't it make sense for autovacuum to run during these idle periods, and not run during busy periods? Well, it's not quite that simple. You don't really want to force autovacuum to run during idle times if there is no useful activity to perform, nor do you want it to never run during busy times in case there is a massive delete that requires cleanup or a large data change that requires updated optimizer statistics.

What you want is to encourage autovacuum to run during idle periods, and discourage it from running during busy times. Fortunately, that is easy to do, especially with the alter system command added in Postgres 9.4.

Let's assume you have your own method to determine busy and idle times. When an idle time starts, simply execute:

ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;

ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;

SELECT pg_reload_conf();

 pg_reload_conf

----------------

 t

Of course, if you changed these settings in postgresql.conf you can use this in psql to reduce the current value of autovacuum_vacuum_scale_factor by half:

SELECT current_setting('autovacuum_vacuum_scale_factor')::float8 / 2 AS scale

\gset

ALTER SYSTEM SET autovacuum_vacuum_scale_factor = :scale;

You might also want to reduce autovacuum_freeze_max_age and autovacuum_multixact_freeze_max_age during this period. Then, at the end of the busy period, run this:

ALTER SYSTEM RESET autovacuum_vacuum_scale_factor;

ALTER SYSTEM RESET autovacuum_analyze_scale_factor;

SELECT pg_reload_conf();

 pg_reload_conf

----------------

 t

This will set the values back to their defaults. Pretty simple, huh? Yet effective. A more sophisticated approach would be to proportionally decrease these settings based on the load on the system.

Let's walk through how this works. Suppose we don't change any settings during idle times and use the default autovacuum_vacuum_scale_factor of 20%. If a table has 19.5% expired rows at midnight, autovacuum will not run. When the percentage of expired rows reaches 20% at 11am, autovacuum will run and remove the expired rows. Now suppose that autovacuum_vacuum_scale_factor is reduced by half at midnight — autovacuum will run and all tables will have less than 10% expired rows by the time the idle period ends. Now, during the busy time autovacuum will only run if a table has increased from 10% expired rows to 20%.

It would be nice if this proportional behavior could be added to autovacuum but I am not sure how to implement that in a clean way.

Bruce Momjian is a Senior Database Architect at EnterpriseDB. 

This blog originally appeared on Bruce's personal blog.