Re: Large historical tables and autovacuum

David Morton wrote:
>>> We have many large tables which contain static historical data, they
are auto vacuumed on
>>> a regular basis (sometimes to prevent wraparound) which i suspect
causes a few annoying side effects:
>>> - Additional WAL file generation
>>> - Increased 'changed' data as far as our online rsync based backups
are concerned
>>>
>>> Is there any way to tell Postgres that these tables are now not
available for changes so
>>> we can avoid these seemingly pointless maintenance tasks ?

Fernando Hevia replied:
>> ALTER TABLE table_name SET (
>> autovacuum_enabled = false
>> );

> What are the implications of doing this ?
> Some times we experience the vacuum being started to prevent
wraparound, i understand this will always
> take place if required regardless of autovacuum settings ?

You are right, that won't help at all.
Autovacuum to prevent transaction ID wraparound will
still take place. And if the table does not change, normal
autovacuum will leave the table alone anyway.
So don't change this setting.

> Is there any way of making the table 'read only' so its nice and tidy
/ immutable ?

There is no way to prevent a VACUUM at least every
autovacuum_freeze_max_age transactions.

The best you can do is to manually schedule VACUUMs
for this table at times when it does not hurt so much.

Yours,
Laurenz Albe