7

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.

  • 2
    The massive UPDATE itself won't trigger an autovacuum until after the UPDATE finishes and sends its info to the stats collector. If an autovacuum is already due before the UPDATE was even started, than one might start up during the UPDATE. It true that it could do some useless work, but that is true at all times, not just during massive UPDATES. Hardly seems worth worrying about to me. – jjanes Jan 18 '17 at 22:02
6

No

There is simply no point. autovacuum will not clean up any rows locked in a running transaction. So autovacuum

  • may not do anything useful.
  • may report that the rows are dead and nonremovable
  • may do something useful with the other rows (if there are any).

But, it won't block or slow down the update in any meaningful sense that I'm aware of.

  • will autovacuum be accessing the database files on disk? – tomsv Nov 30 '18 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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