Re: pg_multixact not getting truncated

On 11/21/2014 10:44 AM, Josh Berkus wrote:
> Greg,
>
>
>> This is actually the way it used to be. It was changed because it was
>> discovered there was some case where an unfrozen xid would end up in
>> template0 anyways and for some reason it was hard to be sure to avoid it. I
>> don't recall exactly what the situation was that triggered it but the
>> argument was made then that it was safest to just include template0 in
>> autovacuum rather than depend on getting this 100% right and risk
>> corruption.
>
> Right, and that was fine before pg_multixact, because even with 500m
> XIDs in the bank, pg_clog is still pretty small. The problem is that
> with the same number of multixacts, pg_multixact is around *16GB* in size.
>
> Thing is, template0 is just there as a check on users messing up
> template1. Having that kind if precaution causing repeated operational
> problems for users is not good design. Maybe we should just get rid of
> template0 and come up with some other mechanism to reset template1 to
> bare-bones state.

Or and even simpler solution: provide a way for the superuser to
manually vacuum template0 *without* needing to update pg_database.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com