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