Temporary shutdown of autovacuum during rush hour

Question

I have been asked to temporarily disable autovacuum in PostgreSQL 14.3. From 7 a.m. to 5 p.m., autovacuum is to be turned off, and at other times it is to be turned back on. I intend to achieve this by setting the command

psql -c "ALTER SYSTEM SET autovacuum = off;" && psql -c "SELECT PG_RELOAD_CONF();"

and

psql -c "ALTER SYSTEM SET autovacuum = on;" && psql -c "SELECT PG_RELOAD_CONF();"

in cron at appropriate times. I checked this solution on a test database, and everything works as it should.

Question for you community: do you see any risks associated with such a solution on the production database? The solution is designed to eliminate performance problems during peak hours.

Is the solution presented in the description correct?

Answer 1

Your solution is a good way to achieve what you intend to do.

However, I doubt the sanity of the measure. Disabling autovacuum for 10 hours may lead to a lot of bloat on busy tables that may well drive your application into the ground. If autovacuum consumes too many resources, you can either tune it to consume less or (if you need it to be fast) use stronger hardware.

If you have a table or two where autovacuum gives you trouble (and these are large tables), you could disable autovacuum only on those with

ALTER TABLE sometab SET (autovacuum_enabled = off);

Again, I would doubt the sanity of the measure, but at least it would not affect all tables.

Answer 2

The risk is that there may well be smallish rapidly changing tables which need to be vacuumed many times during a 10 hour interval in order to avoid problems (bloat, or degraded index-only scans). So you might avoid one problem to create a different one.

A better solution would probably be to keep autovacuum on, but make it less prone to doing anything during busy times. Maybe something like alter system set autovacuum_vacuum_scale_factor TO 0.4; during the day, and then reset it to the default of 0.2 at night. Of course you would still need the PG_RELOAD_CONF(). Or maybe a better option is to reverse that, make it more prone to vacuum at night by doing alter system set autovacuum_vacuum_scale_factor TO 0.1; and resetting it to the default during the day.

It would be easier to find a good solution if you knew exactly what the problem was, in terms of which tables were being vacuumed at what time which lead to problems, and whether it was being driven by wraparound, inserts, or update/deletes.