8

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.

jpmc26
  • 1,652
  • 3
  • 20
  • 38

2 Answers2

8

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.

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
1

Only if you're doing multiple successive updates. If so, disabling autovaccume will prevent postgres from vacuuming between updates.

Kyouma
  • 111
  • 2