I have a table with daily partition schema on Postgresql 9.1 where we are keeping 2 years of data.
Often I experience that autovacuum process is busy with old tables where there is no change. How can I stop it?
Please advice.
I typically run a vacuum freeze in old partitions that don't get any changes as part of a maintenance script. If the tables actually get no changes, autovac should ignore them unless wrap becomes an issue at max_freeze_age... Which, it shouldn't of you vacuum freeze and there are no changes.
Unfortunately that is not true (although it may become true in 9.6).
Currently the only way PostgreSQL has of knowing that a table is all-frozen is to read the whole table and observe each tuple to be frozen.
What I do is always set vacuum_cost_page_hit and vacuum_cost_page_miss to zero.
Vacuuming an all-frozen table for wrap around then goes very quickly, as there is nothing to write and the reading goes very fast.
The only problem I've had with that is for very large gin or gist indexes, which are not vacuumed in physical order but rather logical order. Even then the problem is mostly how long it takes to do the vacuum (i.e. those settings fail to fix the problem, but don't cause more problems), not the impact it has on the IO of other processes. Your mileage may vary, of course.