From: Brendan Duddridge <brendan@clickspace.com>
To: Chris Hoover <revoohc@gmail.com>
Cc: Anthony Ransley <anthonyr@aurema.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Autovacuum ideas
Date: Wed, 12 Apr 2006 23:56:47 -0600
Message-ID: <F8AC66B7-3E32-4DE3-BC25-E2AB5B341600@clickspace.com> (raw)
In-Reply-To: <1d219a6f0604121353t1e997628i6e10d00bed4b2354@mail.gmail.com>
References: <1d219a6f0604111602m62445e60h425c5dd59219606e@mail.gmail.com>
<443C39ED.6050706@aurema.com>
<1d219a6f0604120753h1cabb4c2n60f53403225dcf81@mail.gmail.com>
<20060412151553.GD23855@surnet.cl>
<1d219a6f0604121353t1e997628i6e10d00bed4b2354@mail.gmail.com>
What I'd like to see is a table exclusion list. I have a few very
large history tables that are never updated or deleted, only inserts
and selects.
____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.com
On Apr 12, 2006, at 2:53 PM, Chris Hoover wrote:
> I love this idea. I think on initial consideration it might have a
> lot of merit. You could have a setup something like:
>
> pg_av_grp -- table of group names
> avgrpname varchar(50) -- group name
>
> pg_av_grp_window -- time windows when not to vacuum the group of
> tables
> avgrpwinrelid = pg_av_group.oid
> no_run_start time - starting of exclusion block
> no_run_stop time - stopping of exclusion block
> no_run_day <not sure of format> - day(s) of this exclusion
>
> pg_av_grp_rel -- the list of tables in a group
> avgrprelid = pg_av_grp.oid
> avgrptabid = pg_class.oid
>
> Something like this structure would allow you a lot of flexibility
> in building your auto vacuum groups. You can define your groups
> with the ability to have multiple windows per group.
>
> This would allow you to basically have rules like this:
> big_group - table_a, table_b, table_f
> no_run_start 08:00
> no_run_stop 17:00
> no_run_day (mon-fri)
> -- don't vacuum the big group tables during the M-F work hours
>
> load_group - table_c, table_d, table_e
> no_run_start 06:00
> no_run_stop 08:00
> no_run_day (mon-sun)
>
> no_run_start 18:00
> no_run_stop 20:00
> no_run_day (mon-sun)
>
> -- don't vacuum the main tables we load during the 2 load windows.
>
> Tables not in a group would be in a default group that could be
> vacuumed at any time as needed.
>
> I'm not really a C programmer, so I don't know what the internals
> would take to accomplish this, but maybe it can spur some thought
> and development.
>
> Chris
>
>
>
> On 4/12/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
>
> I don't think autovacuum will be on by default until we have a way to
> configure rush hours, maintenance windows, etc.
>
> The system I envision has tables grouped in some way, and maintenance
> windows would operate on table groups. For example you will be
> able to
> include the fat table in a (possibly otherwise empty) group, and have
> that group not be processed during "work hours"; the rest of the
> tables
> would continue to be vacuumed at all times.
>
> --
> Alvaro Herrera http://
> www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: brendan@clickspace.com, revoohc@gmail.com, anthonyr@aurema.com
Subject: Re: Autovacuum ideas
In-Reply-To: <F8AC66B7-3E32-4DE3-BC25-E2AB5B341600@clickspace.com>
References: <1d219a6f0604111602m62445e60h425c5dd59219606e@mail.gmail.com> <443C39ED.6050706@aurema.com> <1d219a6f0604120753h1cabb4c2n60f53403225dcf81@mail.gmail.com> <20060412151553.GD23855@surnet.cl> <1d219a6f0604121353t1e997628i6e10d00bed4b2354@mail.gmail.com> <F8AC66B7-3E32-4DE3-BC25-E2AB5B341600@clickspace.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox