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
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
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.