On 6th of January 2025, Nathan Bossart committed patch:
Allow changing autovacuum_max_workers without restarting. This commit introduces a new parameter named autovacuum_worker_slots that controls how many autovacuum worker slots to reserve during server startup. Modifying this new parameter's value does require a server restart, but it should typically be set to the upper bound of what you might realistically need to set autovacuum_max_workers. With that new parameter in place, autovacuum_max_workers can now be changed with a SIGHUP (e.g., pg_ctl reload). If autovacuum_max_workers is set higher than autovacuum_worker_slots, a WARNING is emitted, and the server will only start up to autovacuum_worker_slots workers at a given time. If autovacuum_max_workers is set to a value less than the number of currently-running autovacuum workers, the existing workers will continue running, but no new workers will be started until the number of running autovacuum workers drops below autovacuum_max_workers. Reviewed-by: Sami Imseih, Justin Pryzby, Robert Haas, Andres Freund, Yogesh Sharma Discussion: https://postgr.es/m/20240410212344.GA1824549%40nathanxps13
This is pretty interesting.
As you maybe know, I'm fan of autovacuum, and personally believe that people, generally, don't configure their AVs correctly.
One part of the equation is ability to configure autovacuum in such a way that it will work more during specific times of day.
Thanks to this change we can, for example:
- set autovacuum_worker_slots to value like 50
- set autovacuum_max_workers to normal-ish value of 3-10
and then, for example during nights, or weekends, boost autovacuum_max_workers to something higher, to make AV do more work that it needs to (if it needs).
So, let's see how that would actually work.
=$ SHOW autovacuum_worker_slots ; autovacuum_worker_slots ───────────────────────── 50 (1 ROW) =$ SHOW autovacuum_max_workers ; autovacuum_max_workers ──────────────────────── 3 (1 ROW)
So this is the baseline. Now, let's assume we're entering period of time that we want to let AV do more work, so I can:
=$ ALTER system SET autovacuum_max_workers = 10; ALTER SYSTEM =$ SELECT pg_reload_conf(); pg_reload_conf ──────────────── t (1 ROW)
And from this moment on, autovacuum will work with up to 10 concurrent workers:
=$ SHOW autovacuum_max_workers ; autovacuum_max_workers ──────────────────────── 10 (1 ROW)
And when the low-load period will end and we'd have to scale back maintenance work:
=$ ALTER system reset autovacuum_max_workers; ALTER SYSTEM =$ SELECT pg_reload_conf(); pg_reload_conf ──────────────── t (1 ROW) =$ SHOW autovacuum_max_workers ; autovacuum_max_workers ──────────────────────── 3 (1 ROW)
This is absolutely great. Have been waiting for something like this for a very long time.
Thanks a lot to everyone involved.