Re: Autovacuum Improvements

Russell Smith wrote:
> Alvaro Herrera wrote:
>> I intend to work on the maintenance window idea for 8.3. I'm not sure
>> if I'll be able to introduce the worker process stuff in there as well.
>> I actually haven't done much design on the stuff so I can't say.
>>
>>
> What does a maintenance window mean? I am slightly fearful that it as
> other improvements to vacuum are made, it will change it's meaning.

The maintenance window design as I understand it (Alvaro chime in if I
get this wrong) is that we will be able to specify blocks of time that
are assigned specific autovacuum settings. For example we might define
a maintenance window of Sunday morning from 1AM - 8AM, during that time
all autvacuum thresholds will be dropped to .01, that way everything
will get vacuumed that needs it during that window. Outside of the
window default autovacuum settings apply.

> There has been discussion about a bitmap of dirty pages in a relation
> for vacuum to clean. Do that effect what maintenance means? eg.
> Does maintenance mean I can only scan the whole relation for XID wrap
> in maintenance mode and not during non-maintenance time. Does it mean
> we don't vacuum at all in non-maintenance mode. Or do we just have a
> different set of thresholds during maintenance.

Different thresholds during maintenance window.

> Further to this was a patch a long time ago for partial vacuum, which
> only vacuumed part of the relation. It was rejected on grounds of not
> helping as the index cleanup is the expensive part. My view on this
> is, if with a very large table you should be able to vacuum until you
> fill your maintenance work mem. You are then forced to process
> indexes. Then that is a good time to stop vacuuming. You would have
> to start the process again effectively. This may also change the
> meaning of maintenance window. Again, only full relation scans in
> maintenance times, possibly something else entirely.

I'm not sure how partial vacuums will effect autovacuum, but I'm not
going to worry about it until it gets accepted which I don't think is
going to happen anytime soon.

BTW, when a vacuum starts during a maintenance window but doesn't finish
before the window closes, I think it should continue running, however I
believe the default vacuum delay setting will apply which could be setup
to help reduce the impact that vacuum has outside the maintenance window.

> I am unsure of what the long term goal of the maintenance window is.
> I understand it's to produce a time when vacuum is able to be more
> aggressive on the system. But I do not know what that means in light
> of other improvements such as those listed above. Coming up with a
> method for maintenance window that just used a separate set of
> thresholds is one option. However is that the best thing to do. Some
> clarity here from others would probably help. But I also think we
> need to consider the big picture of where vacuum is going before
> inventing a mechanism that may not mean anything come 8.4

I think for now all we are talking about are different thresholds. As
newer vacuuming options become available we should consider how they
apply, but we aren't there yet.