Re: Autovacuum ideas - Mailing list pgsql-admin

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