pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLarge historical tables and autovacuum
6+ messages / 5 participants
[nested] [flat]
* Large historical tables and autovacuum
@ 2012-09-10 20:30 David Morton <davidmorton78@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: David Morton @ 2012-09-10 20:30 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Large historical tables and autovacuum
@ 2012-09-11 01:57 Rural Hunter <ruralhunter@gmail.com>
parent: David Morton <davidmorton78@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Rural Hunter @ 2012-09-11 01:57 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Large historical tables and autovacuum
@ 2012-09-11 03:57 Fernando Hevia <fhevia@gmail.com>
parent: David Morton <davidmorton78@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Fernando Hevia @ 2012-09-11 03:57 UTC (permalink / raw)
To: David Morton <davidmorton78@gmail.com>; +Cc: pgsql-admin
On Mon, Sep 10, 2012 at 5:30 PM, David Morton <davidmorton78@gmail.com>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 ?
>
ALTER TABLE *table_name* SET (
autovacuum_enabled = false
);
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Large historical tables and autovacuum
@ 2012-09-11 04:04 David Morton <davidmorton78@gmail.com>
parent: Fernando Hevia <fhevia@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: David Morton @ 2012-09-11 04:04 UTC (permalink / raw)
To: pgsql-admin
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
On Tue, Sep 11, 2012 at 3:57 PM, Fernando Hevia <fhevia@gmail.com> wrote:
>
> On Mon, Sep 10, 2012 at 5:30 PM, David Morton <davidmorton78@gmail.com>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 ?
>>
>
> ALTER TABLE *table_name* SET (
> autovacuum_enabled = false
> );
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Large historical tables and autovacuum
@ 2012-09-11 04:29 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: David Morton <davidmorton78@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Rosser Schwarz @ 2012-09-11 04:29 UTC (permalink / raw)
To: David Morton <davidmorton78@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Large historical tables and autovacuum
@ 2012-09-11 08:55 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: David Morton <davidmorton78@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Albe Laurenz @ 2012-09-11 08:55 UTC (permalink / raw)
To: David Morton *EXTERN* <davidmorton78@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-09-11 08:55 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-10 20:30 Large historical tables and autovacuum David Morton <davidmorton78@gmail.com>
2012-09-11 01:57 ` Rural Hunter <ruralhunter@gmail.com>
2012-09-11 03:57 ` Fernando Hevia <fhevia@gmail.com>
2012-09-11 04:04 ` David Morton <davidmorton78@gmail.com>
2012-09-11 04:29 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2012-09-11 08:55 ` Albe Laurenz <laurenz.albe@wien.gv.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox