Обсуждение: Large historical tables and autovacuum

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:

turn auto vacuum off for those tables.

于 2012/9/11 4:30, David Morton 写道:
> 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 ?
>
> Dave

What are the implications of doing this ?

On Mon, Sep 10, 2012 at 9:04 PM, David Morton <davidmorton78@gmail.com> wrote:
> Is there any way of making the table 'read only' so its nice and tidy /
> immutable ?

Once a table actually *is* read-only, you can VACUUM FREEZE it.

rls

--
:wq

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