Why is autovacuum using so much IO when running on a partition's master table after upgrading PosgreSQL from 8.4 to 9.1?
Solution 1:
With help from Postgres developers we have found an answer, it seems that since 9.0 ANALYZE will also run on child tables when running on the parent. Since our use-case triggered the autovacuum very often, that triggered an ANALYZE before running as well and it was also analyzing the child tables.
We settled on using a bigger threshold for autovacuum:
ALTER TABLE ... SET (autovacuum_analyze_threshold = ...);
That will decrease the ANALYZE frequency and keep our IO, CPU WAIT and LOAD values down.