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@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxx> 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
|
