Re: Autovacuum improvements

Matthew T. O'Connor wrote:

> This still seems ambiguous to me, how would I handle a maintenance 
> window of Weekends from Friday at 8PM though Monday morning at 6AM? My 
> guess from what said is:
> mon dom dow starttime endtime
> null  null    6      20:00      null
> null  null    1      null          06:00
> 
> So how do we know to vacuum on Saturday or Sunday?  I think clearly 
> defined intervals with explicit start and stop times is cleaner.

mon    dom    dow    start    end
null    null    5    20:00    23:59:59
null    null    6    00:00    23:59:59
null    null    7    00:00    23:59:59
null    null    1    00:00    06:00

(1 = monday, 5 = friday)

Now I'm starting to wonder what will happen between 23:59:59 of day X
and 00:00:00 of day (X+1) ...  Maybe what we should do is not specify
an end time, but a duration as an interval:

month        int
dom        int
dow        int
start        time
duration    interval

That way you can specify the above as
mon    dom    dow    start    duration
null    null    5    20:00    (4 hours + 2 days + 6 hours)

Now, if a DST boundary happens to fall in that interval you'll be an
hour short, or it'll last an hour too long :-)


> >I had two ideas: one was to make pg_autovacuum hold default config for
> >all tables not mentioned in any group, so sites which are OK with 8.2's
> >representation can still use it.  The other idea was to remove it and
> >replace it with this mechanism.
> 
> Probably best to just get rid of it.  GUC variables hold the defaults 
> and if we create a default interval / group, it will also have defaults.

Yeah, maybe.

> >My idea was to assign each table, or maybe each group, to a queue, and
> >then have as much workers as there are queues.  So you could put them
> >all in a single queue and it would mean there can be at most one vacuum
> >running at any time.  Or you could put each group in a queue, and then
> >there could be as many workers as there are groups.  Or you could mix.
> >
> >And also there would be a "autovac concurrency limit", which would be
> >a GUC var saying how many vacuums to have at any time.
> 
> Hmm... this seems like queue is nearly a synonym for group.  Can't we 
> just add num_workers property to table groups?  That seems to accomplish 
> the same thing.  And yes, a GUC variable to limits the total number of 
> concurrent autovacuums is probably a good idea.

queue = group of groups.  But I'm not sure about this at all, which is
why I took it away from the proposal.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support