On 2018-Aug-01, Andres Freund wrote:
> On 2018-08-01 12:07:16 -0400, Tom Lane wrote:
> > Andres Freund <andres@anarazel.de> writes:
> > > On 2018-08-01 10:24:24 -0400, Tom Lane wrote:
> > >> IMO, the action you need to take is enabling autovacuum. We've
> > >> seen many many people go down the path you are taking, and it's
> > >> generally led to no good in the end. Manual vacuuming tends
> > >> to miss stuff, and it cannot react adequately to activity spikes.
> >
> > > But it shouldn't matter here, autovacuum will start regardless, no?
> >
> > Sure, once it decides that emergency anti-wraparound vacuuming is
> > necessary. I really doubt the OP wants that to happen; it's the
> > exact opposite of non-intrusive.
>
> That's solely what would trigger it were autovacuum enabled, too? I've
> complained about "emergency anti-wraparound" beeing anything but
> emergency (they're largely unavoidable unless you manually script it),
> but they're what happen once autovacuum_freeze_max_age is reached, and
> that's the only trigger for vacuuming old relations independent of other
> activity?
With a small database like template0, it doesn't matter. The vacuuming
is going to be over before OP realizes it has happened anyway.
Certainly having it happen on a normal-sized table can become
problematic, but presumably OP has taken steps to avoid it when
disabling autovacuum (which is why only template0 is getting into
trouble.)
I think emergency vacuum should behave differently (not scan indexes,
just apply HOT page prune and clear old XIDs/multixacts), which would
make it much faster, but that's a separate line of thought (and of
development).
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services