On 2018-08-01 12:20:15 -0400, Alvaro Herrera wrote: > On 2018-Aug-01, Andres Freund wrote: > > > On 2018-08-01 12:07:16 -0400, Tom Lane wrote: > > > Andres Freund <andres@xxxxxxxxxxx> 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.) Right. > 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). What I'd love is for freeze_max_age triggered vacuums *not* to be emergency vacuums. They should just be normal ones. There should be a separate GUC that triggers the emergency bit. It's really annoying to get a hard to kill ant-wraparound autovacuum on an insert only table, where it's the only thing that'll trigger the autovacuum. Greetings, Andres Freund
