Re: Autovacuum ideas

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux