23

In some occasions, and after making a massive update, insert or delete from a table, I have started a VACUUM FULL ANALYZE to make sure the DB was not getting too bloated. Doing it in a production database has let me discover that this was not a good idea, because I could block the table for a long period of time. So, I cancelled the process, maybe tried just VACUUM (not full) or let AUTOVACUUM do later whatever it can do.

The question is: if I stop a VACUUM or AUTOVACUUM "mid-way", is all the processing already done lost?

For instance, if VACUUM already found 1 M dead rows and I stop it, is all this information lost? Does VACUUM work in a fully transactional manner ("all or nothing", like a very good number of PostgreSQL processes)?

If VACUUM can be safely interrupted without all the work being lost, is there any way to make vacuum work incrementally? [Work for 100 ms, stop, wait 10 ms to allow for non-blocking the rest of the world... and so on]. I know you can do part of this by tuning autovacuum parameters, but I am thinking along the lines of being able to control this programmatically, to be able to do it at certain times / under certain conditions.

NOTE: Stop / cancel / kill the process means in this context:

  • If using pgAdmin, press the "Cancel Query" button.
  • If working programmatically, call pg_cancel_backend().

I assume that both are equivalent. I have not used any shell/system-level kill command.

Improve this question

1 Answer 1

Reset to default
20

Work done by an interrupted VACUUM FULL will be entirely lost, as it will simply revert to using the previous version of the table and throw away the in-progress version of the table.

Work done by a regular (not-FULL) VACUUM might not be entirely lost. It cleans indexes in batches, and any batches which were fully cleaned will not need to be cleaned again. They will still need to be inspected again, but will be found already clean the next time. So you might save some write IO which won't need to be repeated.

Improve this answer
2
  • 2
    Would love more detail on this, particularly on autovacuum. I have busy servers with many database and sometimes autovacuums can take a long time. When that happens, creating a new index, for instance, is impossible because the autovacuum has a lock. It would be ideal in some cases to kill the autovacuum and apply the index and then hopefully when the autovacuum runs again it doesn't have to run for nearly as long. Any way to see details of what autovacuum has done/is doing to a table and indexes?
    – Kurt Koller
    Commented Jan 31, 2018 at 19:59
  • 6
    9.6 introdcued a view for monitoring vacuum progress: postgresql.org/docs/current/static/progress-reporting.html. I have not played around with it myself, so don't know how well it will work for you. Autovacuum should yield to the lock automatically, unless it is being done for wrap-around. The default settings for autovacuum are heavily throttled, so it might not run any faster the next time just because it is being throttled to the same speed. I routinely set vacuum_cost_page_hit and vacuum_cost_page_miss to zero.
    – jjanes
    Commented Jan 31, 2018 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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