In some occasions, and after making a massive
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.