Re: Autovacuum ideas

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(at)clickspace(dot)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(at)commandprompt(dot)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
>