Re: Autovacuum Improvements

Matthew O'Connor wrote:
> 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.

I wonder if the simple solution is to just have a cron script modify
postgresql.conf and pg_ctl reload. That seems very flexible, or have
two postgresql.conf files and move them into place via cron.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +