Sample Header Ad - 728x90

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