2

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.

  • 7
    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. – dezso May 8 '15 at 8:37
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

  1. 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.

  • 1
    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...) – Josh Kupershmidt May 8 '15 at 13:48
  • 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. – Soni Harriz May 8 '15 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.