For PostgreSQL it is critical, that autovacuum is able to keep up with the changes to the instance. One of the parameters you can adapt for this is autovacuum_max_workers. This parameter controls how many worker process can be started in parallel by the autovacuum launcher process. By default, this is set to 3, which means that a maximum of three worker processes can run in parallel to do the work. While you can increase this parameter easily, it requires a restart of instance to become active. Starting with PostgreSQL 18 (scheduled to be released later this year), you’ll be able to change this on the fly.

The default configuration for PostgreSQL 18 is still three worker processes:

postgres=# select version();
                                version                                 
------------------------------------------------------------------------
 PostgreSQL 18devel on x86_64-freebsd, compiled by clang-18.1.6, 64-bit
(1 row)

postgres=# show autovacuum_max_workers ;
 autovacuum_max_workers 
------------------------
 3
(1 row)

But now, you can increase that on the fly without restarting the instance (sighup means reload):

postgres=# select context from pg_settings where name = 'autovacuum_max_workers';
 context 
---------
 sighup
(1 row)

postgres=#

On a PostgreSQL 17 (and before) instance, it looks like this (postmaster means restart):

postgres=# select version();
                                                                                           version                                                                                            
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.2 on x86_64-unknown-freebsd14.2, compiled by FreeBSD clang version 18.1.6 , 64-bit
(1 row)

postgres=# select context from pg_settings where name = 'autovacuum_max_workers';
  context   
------------
 postmaster
(1 row)

So, lets assume we have an instance where a lot of stuff is going on and we want to increase the worker processes to 32 on a PostgreSQL 18 instance:

postgres=# alter system set autovacuum_max_workers = 32;
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

postgres=# show autovacuum_max_workers ;
 autovacuum_max_workers 
------------------------
 32
(1 row)

That seems to have worked, but if you take a look at the PostgreSQL log file you’ll notice this:

2025-01-08 11:48:59.504 CET - 1 - 4411 - [local] - postgres@postgres - 0LOG:  statement: alter system set autovacuum_max_workers = 32;
2025-01-08 11:49:05.210 CET - 8 - 4174 -  - @ - 0LOG:  received SIGHUP, reloading configuration files
2025-01-08 11:49:05.212 CET - 9 - 4174 -  - @ - 0LOG:  parameter "autovacuum_max_workers" changed to "32"
2025-01-08 11:49:05.221 CET - 1 - 4180 -  - @ - 0WARNING:  "autovacuum_max_workers" (32) should be less than or equal to "autovacuum_worker_slots" (16)

This means there still is a limit, which is defined by “autovacuum_worker_slots“, and the default for this one is 16. For most instances this probably is fine, you can go from the default (3) anywhere up to 16 without restarting the instance. If you think you’ll need more from time to time, then you also should increase autovacuum_worker_slots, but this does require a restart of the instance.

Here the link to the commit.

Post Views: 344
Thumbnail [60x60]
by
Daniel Westermann