Re: Autovacuum Improvements

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>
Cc: Russell Smith <mr-russ(at)pws(dot)com(dot)au>, Glen Parker <glenebob(at)nwlink(dot)com>, Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Re: Autovacuum Improvements
Date: 2006-12-21 17:36:01
Message-ID: 1166722561.15606.11.camel@coppola.muc.ecircle.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

On Thu, 2006-12-21 at 18:03, Matthew T. O'Connor wrote:
> The maintenance window design as I understand it (Alvaro chime in if I
> get this wrong) is that we will be able to specify blocks of time that
> are assigned specific autovacuum settings. For example we might define
> a maintenance window of Sunday morning from 1AM - 8AM, during that time
> all autvacuum thresholds will be dropped to .01, that way everything
> will get vacuumed that needs it during that window. Outside of the
> window default autovacuum settings apply.

Changing thresholds is not a viable solution for all the cases. If I
have a huge table with many indexes, I still don't want to vacuum it
unless there are a significant amount of dead pages so that the
sequential scan of it and it's indexes pays off. In this case dropping
the autovacuum threshold would be totally counterproductive even at
night. This solution would only rule out really static tables, which
don't change almost at all. In real life there are many more possible
data access scenarios...

>From all the discussion here I think the most benefit would result from
a means to assign tables to different categories, and set up separate
autovacuum rules per category (be it time window when vacuuming is
allowed, autovacuum processes assigned, cost settings, etc). I doubt you
can really define upfront all the vacuum strategies you would need in
real life, so why not let the user define it ? Define the categories by
assigning tables to them, and the rules per category. Then you can
decide what rules to implement, and what should be the defaults...

Cheers,
Csaba.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hannes Dorbath 2006-12-21 17:38:08 Re: TSearch2 Changeset 25387
Previous Message Steve Atkins 2006-12-21 17:34:59 Re: Password strength requirements

Browse pgsql-hackers by date

  From Date Subject
Next Message org 2006-12-21 17:36:02 SPAR Simple PostgreSQL AddOn Replication System
Previous Message Tom Lane 2006-12-21 17:24:18 Re: pgsql: Initial SQL/XML support: xml data type and initial set of