> > > Usually it gets really bad if you *don't* run vacuum continuously, maybe
> > > hopeing to do it in slower times at night. For high-update db you have
> > > to run it continuously, maybe having some 5-15 sec pauses between runs.
> >
> > And how much I/O does this take?
>
> Surprisingly its mostly WAL traffic, the heap/index pages themselves are
> often not yet synced to disk by time of vacuum, so no additional traffic
> there. If you had made 5 updates per page and then vacuum it, then you
> make effectively 1 extra WAL write meaning 20% increase in WAL traffic.
Is this also holding about read traffic ? I thought vacuum will make a
full table scan... for big tables a full table scan is always badly
influencing the performance of the box. If the full table scan would be
avoided, then I wouldn't mind running vacuum in a loop...
In fact I think that it would make sense to replace the whole current
vacuum stuff with a background thread which does that continuously using
a dead space map. That could be a heap sorted by tuple deletion time,
and always cleaned up up to the oldest running transaction's start
time... there would be no need for any other autovacuum then.
Cheers,
Csaba.
