2

I would like to schedule a vacuumdb cron job for an entire database, but I don't want it to run past a certain time. Is it safe to just stop the job with killall vacuumdb?

  • Why don't you let the built-in autovacuum do its job? – a_horse_with_no_name Apr 22 '17 at 5:48
  • If you really, really need to do it that way, use pg_cancel_backend(pid int) instead of killing the process. – joanolo Apr 22 '17 at 9:34
  • What is autovacuumdb? The stock PostgreSQL code doesn't contain such a thing. – jjanes Apr 24 '17 at 16:12
  • I meant vacuumdb. – LINUX G33NYUS Apr 24 '17 at 16:25
  • And I don't think that pg_cancel_backend() works on vacuumdb, it only works on auto vacuum. – LINUX G33NYUS Apr 24 '17 at 21:55
4

Yes, it's safe. Use SIGTERM (the default, signal 15 on Linux) not SIGKILL (signal 9). If it doesn't complete it might waste recent work and have to repeat it.

However, you should really just let autovacuum do its job.

  • 1
    Autovacuum has got a lot of parameters that can be tuned so that it does not hinder the database normal operation... and so, it can be let to do its job without much effect on overall performance. – joanolo Apr 22 '17 at 10:15
  • 4
    @joanolo but none of the parameters tell it to do the vacuuming when the database is at its least loaded time. The way the activity-based thresholds work, it is most likely to kick in at the most-loaded time, not the least-loaded time. So many people intervene to schedule vacuums during off-peak times, for good reasons. – jjanes Apr 24 '17 at 16:10
  • @jjanes: Agreed, nevertheless... waiting for too long without analyzing the data might lead to much worse exeuction plans... which slow down the database no matter what the load. – joanolo Apr 24 '17 at 19:30
  • 2
    @joanolo Right. I'd say the best practice, if you need to schedule manual vacuuming or analyzing, would be to leave autovac turned on. If your manual processing is sufficient, the auto process will find it has little or nothing to do. If your manual process is insufficient, you still have a fall back. – jjanes Apr 24 '17 at 20:16

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.