Re: How to stop autovacuum for daily partition old tables

On Wed, Jan 20, 2016 at 6:00 PM, Scott Mead <scottm(at)openscg(dot)com> wrote:

>
>
> On Jan 20, 2016, at 19:54, AI Rumman <rummandba(at)gmail(dot)com> wrote:
>
> But, will it not create transaction wraparound for those table?
>
> Thanks.
>
> On Wed, Jan 20, 2016 at 4:44 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
> wrote:
>
>>
>> ALTER TABLE your_schema.your_table SET (autovacuum_enabled = false,
>> toast.autovacuum_enabled = false);
>>
>> On Wed, Jan 20, 2016 at 6:22 PM, AI Rumman <rummandba(at)gmail(dot)com> wrote:
>>
>>> Hi,
>>>
>>> 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.

Cheers,

Jeff