Should I disable autovacuum on a table while I do a bulk update?
8
votes
2
answers
8589
views
I need to perform a simple update on all rows in a table. The table has 40-50 million rows. Dropping indexes and constraints during the
UPDATE
results in a massive performance improvement.
But what about autovacuum? Can autovacuum start a VACUUM
or ANALYZE
in the middle of an UPDATE
? If so, it would be useless work that would eat up machine resources. I could disable it on the table prior to the UPDATE
and then re-enable it afterwards:
ALTER TABLE my_table
SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);
-- Drop constraints, drop indexes, and disable unnecessary triggers
UPDATE my_table SET ....;
-- Restore constraints, indexes, and triggers
ALTER TABLE my_table
SET (autovacuum_enabled = true, toast.autovacuum_enabled = true);
Does this even work if I don't commit after the first ALTER
?
Also, if I disable it during the UPDATE
, will it trigger *after* the update, or will it ignore those updates because it was disabled during them? (My suspicion is that it will run, but I'd rather be sure.)
I'm using PG 9.3 right now, but should be upgrading soon. So any mention of changes in newer versions is appreciated.
Asked by jpmc26
(1652 rep)
Jan 18, 2017, 07:52 PM
Last activity: Jun 8, 2023, 07:32 PM
Last activity: Jun 8, 2023, 07:32 PM