Is it possible to set a timer for autovacuum in PostgreSQL

Question

I am running PostgreSQL 9.4.1. I am currently cleaning up data, which requires me to run expensive queries. It seems like my tables get "locked" up every day at the same time in the afternoon. My queries don't run during at this time and I can only resume after an hour of waiting. After researching why this could be, I am assuming that my database is starting autovacuum processes, which locks up my datatables.

Is it possible to schedule a time for autovacuum or to set a time where autovacuum should not run? I would like to avoid turning the autovacuum off.

• András Váczi: Please confirm that it is the autovacuum that blovks your access - this is not supposed to be so, the workers give way to allow other activities. Set up some monitoring and watch what happens at that time. You can choose something from here: wiki.postgresql.org/wiki/Monitoring, I recommend pg_view as it was developed by my colleagues.

Answer 1

You can set a time where autovacuum should not run.

Here's how to (assuming You're on linux):

1. Create a script to turn off/on autovacuum
2. The script should contain :

sed -i 's/autovacuum = on/autovacuum = off/g' postgresql.conf

pg_ctl -D PGDATA reload

3. Place the script on cron.

Let's say You have cron to turn off autovacuum at 2 PM and then have cron to turn on autovacuum at 3 PM.

Hope it helps.

• Josh Kupershmidt: Note that this solution will not kill off any already-running autovacuum workers, nor will it prevent wraparound-prevention autovacuums from kicking in. (Based on the information provided so far by OP, it seems unlikely that autovacuum is the true culprit anyway, but still...)
• Soni Harriz: i ever try doing 'alter table set (autovacuum_enabled=false)' when the autovacuum is running for that table, it then automatically stop the autovacuum. And you're right about the wraparound-prevention autovacuum.