Large historical tables and autovacuum

[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

[Rural Hunter]

turn auto vacuum off for those tables.

Post by David Morton
We have many large tables which contain static historical data, they
are auto vacuumed on a regular basis (sometimes to prevent wraparound)
- 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

[Fernando Hevia]

Post by 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
- 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 ?

ALTER TABLE *table_name* SET (
autovacuum_enabled = false
);

[David Morton]

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 ?

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

Dave

Post by Fernando Hevia

Post by 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
- 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 ?

ALTER TABLE *table_name* SET (
autovacuum_enabled = false
);

[Rosser Schwarz]

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

[Albe Laurenz]

Post by David Morton

Post by David Morton
We have many large tables which contain static historical data, they

are auto vacuumed on

Post by David Morton

Post by David Morton
a regular basis (sometimes to prevent wraparound) which i suspect
- Additional WAL file generation
- Increased 'changed' data as far as our online rsync based backups

are concerned

Post by David Morton

Post by David Morton
Is there any way to tell Postgres that these tables are now not

available for changes so

Post by David Morton

Post by David Morton
we can avoid these seemingly pointless maintenance tasks ?

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

Post by David Morton
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.

Post by David Morton
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