Re: How to avoid Force Autovacuum

On Fri, Aug 9, 2013 at 9:06 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Vishalakshi Navaneethakrishnan <nvishalakshi(at)sirahu(dot)com> wrote:
>
>> select * from pg_database where datname = 'template0';
>> -[ RECORD 1 ]-+------------------------------------
>> datname | template0
>> datdba | 10
>> encoding | 6
>> datcollate | en_US.UTF-8
>> datctype | en_US.UTF-8
>> datistemplate | t
>> datallowconn | f
>> datconnlimit | -1
>> datlastsysoid | 12865
>> datfrozenxid | 2025732249
>> dattablespace | 1663
>> datacl | {=c/postgres,postgres=CTc/postgres}
>>
>>
>> select * from pg_stat_database where datname = 'template0';
>> -[ RECORD 1 ]--+------------------------------
>> datid | 12865
>> datname | template0
>> numbackends | 0
>> xact_commit | 320390
>> xact_rollback | 7
>> blks_read | 3797
>> blks_hit | 9458783
>> tup_returned | 105872028
>> tup_fetched | 1771782
>> tup_inserted | 10
>> tup_updated | 457
>> tup_deleted | 10
>> conflicts | 0
>> temp_files | 0
>> temp_bytes | 0
>> deadlocks | 0
>> blk_read_time | 0
>> blk_write_time | 0
>> stats_reset | 2013-04-19 19:22:39.013056-07
>
> Well, that's why template0 is getting vacuumed. At some point
> someone must have set it to allow connections; otherwise you would
> have zero for commits, rollbacks, and all those block and tuple
> counts.

Non-zero values are normal. There is no mechanism to prevent
template0 from getting vacuumed. template0 will get vacuumed once
every autovacuum_freeze_max_age even if no one has ever connected to
it, and that vacuum will cause block reads and writes to happen. (But
I'm not sure why it would contribute xact_rollback or tup_updated, and
the tup_returned seems awfully high to be due to only anti-wrap-around
vacs.)

Cheers,

Jeff