Re: Autovacuum Improvements

[Matthew O'Connor <matthew@xxxxxxxx>]

Glen Parker wrote:
> Matthew O'Connor wrote:
> > No, how dirty a table isn't subjective, what is subjective is the 
> > question "Does it need to be vacuumed?".  A that is 1% dirty (to use 
> > your term) probably doesn't *need* to be vacuumed, but you might 
> > choose to vacuum it anyway at least you might at night when the system 
> > isn't in use.
>
> This leads me further from wanting to see a simple time contraint added. 
>  I'd like to see something more dynamic.
>
> Perhaps define a "dirtiness" rating, and then allow a minimum 
> "dirtiness" to be configured.  When autovacuum wakes up, it could build 
> a list of sufficiently dirty tables sorted in "dirtiness" order, and 
> could call an optional user defined function for each one, passing it 
> useful bits of information including each table's "dirtiness".  The 
> function could then decide whether to vacuum or not based on whatever 
> constraints the admin dreamed up.
>
> It would then be a simple matter to expose a function that, given a 
> table's OID, could report its "dirtiness" level.

The idea that has been discussed in the past is the concept of 
maintenance windows, that is for any given period of time, you can set 
different vacuum thresholds.  So at night you might make the thresholds 
very low so that nearly everything gets vacuumed but during the day you 
might only vacuum when something really needs it.  This accomplishes 
what you are asking for in a more general way that can accommodate a 
wide variety of usage patterns.