Tuning autovacuum_naptime

One of the things I sometimes get asked to do is review someone's postgresql.conf settings.  From time to time, I run across a configuration where the value of autovacuum_naptime has been increased, often by a large multiple.  The default value of autovacuum_naptime is 1 minute, and I have seen users increase this value to 1 hour, or in one case, 1 day.  This is not a good idea.  In this blog post, I will attempt to explain why it's not a good idea, and also something about the limited circumstances under which you might want to change autovacuum_naptime.

PostgreSQL's autovacuum system involves two types of processes: an autovacuum launcher, and autovacuum workers. The autovacuum launcher's job is to start autovacuum workers, and the job of the workers is to perform whatever VACUUM and ANALYZE operations are needed in order to maintain good database performance. Since a single cluster may contain any number of databases, it would not work to simply have a single autovacuum worker that ran all the time, because any given PostgreSQL process can be connected to at most one database.  The autovacuum launcher is not connected to any specific database, and in fact has very little knowledge of what is happening in any of the individual databases that make up the cluster.  Its whole job is to make sure that each database is periodically visited by an autovacuum worker, which will be able both to determine which tables within that database need to be vacuumed or analyzed and to perform the corresponding operations.

But, how often should each database be visited by an autovacuum worker?  In the previous paragraph, I said that it should happen "periodically," but that doesn't answer the question of exactly how frequently it should happen. As it turns out, this is exactly what is controlled by the autovacuum_naptime parameter.  The default setting of 1 minute means that the autovacuum launcher should strive to launch an autovacuum worker for each database once per minute.  This may seem very frequent, but it isn't really.  We are not talking about how frequently any particular table in the database should be vacuumed or analyzed, but rather about how often we check whether it needs to be vacuumed or analyzed.  The check itself is very quick, and the worker will simply exit if it finds that no work needs to be done.

By configuring autovacuum to check each database less frequently, you will tend to increase the amount of time that passes between the point at which a table needs a VACUUM or ANALYZE operation to maintain good performance and the point at which an autovacuum worker performs that operation.  In the case of VACUUM, this will typically lead to table bloat: dead row versions won't get removed in a timely fashion, and more space will have to be allocated in order to store the new row versions created by UPDATE or INSERT operations performed on that table.  If VACUUM had run in a timely fashion, space freed up by DELETE operations or by removing the old row versions after an UPDATE could have been used for this purpose, rather than allocating additional space from the operating system.  In the case of ANALYZE, this will typically lead to poor query performance.  If the data in your table changes enough that the statistics ought to be updated, and by increasing autovacuum_naptime you delay that statistics update, then you will be running with inaccurate statistics for a longer period of time, perhaps resulting in poor query plans.

In my experience, at least, the effects of increasing autovacuum_naptime are far more devastating than the rather tame description in the previous paragraph might suggest.  If your table are heavily updated, it is not merely that they will bloat (i.e. grow without any particular good reason), but that they will do so at an extremely rapid rate that result in severe performance degradation and likely create a situation where you have no reasonable alternative to running VACUUM FULL.  If you want your database cluster to perform well, and you probably do, you want autovacuum to notice bloat as soon as it starts to happen and issue VACUUM commands to reign the situation in before it gets worse.

Since the previous paragraph suggests that increase autovacuum_naptime is a really bad idea, you might reasonably wonder whether decreasing autovacuum_naptime is a really good idea.  I have run across one case where I recommended such a settings change and it helped a lot.  In that case, the customer had a very small table which was used as a queue, with items added and removed constantly at very high speed.  It bloated very rapidly with the default settings, but changing autovacuum_naptime to 15 seconds fixed the problem.

However, this approach is unlikely to be right for everyone.  The key fact in that particular case was that the table that was bloating was very small.  Only one VACUUM operation can be in progress on any given table at the same time; if you have a table that takes 5 minutes to vacuum, checking whether it's time to do so every 15 seconds rather than every minute seems unlikely to help very much.  If, though, the table takes only a few seconds to vacuum, checking more often could make a big difference.

There is another subtlety here as well, which is that, if there are enough workers available, the autovacuum launcher will even launch workers in a database that already has one or more workers.  Consider an installation with a single large database - this is a common scenario - where a large number of tables all need to be vacuumed around the same time - also a common scenario.  When autovacuum_naptime next expires, a worker will be launched in that database.  When it expires again, another worker will be launched in that database, even if the previous worker is still running.  And then, assuming the default configuration of autovacuum_max_workers = 3, when autovacuum_naptime expires one more time, the third worker will be started in that database.  So, with default settings, after 2-3 minutes, all workers will be hard at work on the problematic database.  If you increase autovacuum_naptime, you not only increase the expected time before the first worker starts, but also rate at which additional workers are started in the same database.  Generally, this is undesirable.  Remember that autovacuum's whole goal is to ensure that VACUUM and ANALYZE operations are performed as soon as possible after the point where the triggering thresholds are exceeded.  If many tables cross the threshold at around the same time, the "ramp up" behavior described in this paragraph is an important part of responding to that situation effectively.

I know of only one scenario in which it might be advisable to increase autovacuum_naptime, and that is the case of a database cluster with a very large number of databases.  In such a case, you might find that the CPU usage of the autovacuum launcher becomes fairly significant, even when the cluster overall is nearly idle.  This happens because the autovacuum launcher must try to launch a worker in each database once per autovacuum_naptime interval, and so it gets busier and busier as the number of databases grows.  If the system is very nearly idle, and the databases are subject to few or no modifications, then it might be more important to save CPU cycles than to launch workers in a timely fashion.  Raising autovacuum_naptime could accomplish this.  Even in this scenario, however, it should be raised only as much as needed to tamp down excessive CPU consumption.  Otherwise, such a change is just increasing the risk of database bloat without any compensating advantage.

I realize that this blog post is quite technical and really just scratches the surface of this topic.  The main point I want to make is that increasing autovacuum_naptime, especially by a large multiple, is a bad idea.  By way of analogy, it would be reasonable to check your refrigerator every week and throw away any food that has gotten moldy, but it would be unreasonable to perform the same check only once per year.  If you did perform that check only once per year, it would be fair to say that the performance of your refrigerator would stink.  In the same way, if you radically increase autovacuum_naptime, your database performance will probably stink, too.