Re: possible vacuum improvement?

Rod Taylor <rbt(at)zort(dot)ca> writes:
> On Tue, 2002-09-03 at 11:01, Tom Lane wrote:
>> For vacuum's purposes all that we really care to know about is the
>> number of obsoleted tuples in each table: committed deletes and updates,
>> and aborted inserts and updates all count. Furthermore, we do not need
>> or want a 100% reliable solution; approximate counts would be plenty
>> good enough.

> It would be nice if it could track successful inserts, and fire off an
> analyze run when it changes more than 20% from what stats says.

That's a thought too. I was only thinking of space reclamation, but
it'd be easy to extend the scheme to keep track of the number of tuples
successfully inserted, changed, or deleted (all three events would
affect stats) as well as the number of dead tuples. Then you could fire
auto-analyze every so often, along with auto-vacuum.

Auto-analyze might need more tuning controls than auto-vacuum, though.
Vacuum doesn't have any question about when it needs to run: a dead
tuple is a dead tuple. But for analyze you might have plenty of update
traffic and yet no meaningful change in the interesting stats for a
table. An admin who knows the behavior of his tables would like to be
able to configure the frequency of analyze runs, rather than trust to
a necessarily-not-too-bright auto-analyze routine. (Not sure whether
this is important enough to warrant the complications of making it
configurable though. You can always do it the old-fashioned way with
cron scripts if you want that kind of control, I suppose.)

regards, tom lane