Re: pg_autovacuum next steps

Matthew T. O'Connor wrote:
> * Inability to customize thresholds on a per table basis

I ran headlong into this one. IMHO fixing this is critical.

> * Inability to set default thresholds on a per database basis
> * Inability to exclude specific databases / tables from pg_autovacuum
> monitoring

These would be nice to have, but less critical than #1 I think.

> * Inability to schedule vacuums during off-peak times

This would be *really* nice to have. In my recent case, if pg_autovacuum
could work for say 3 minutes, and then back off for 2 minutes or so
while the batch transactions hit, it would be ideal.

> I'm not sure how to address all of these concerns, or that they all
> should be addressed right now. One of my big questions is backend
> integration. I am leaning towards leaving pg_autovacuum as a client
> application in contrib for one more release. During this time, I can
> continue to tweak and improve pg_autovacuum so that we will have a very
> good idea what the final product should be before we make it a standard
> backend process.

I really think pg_autovacuum ought to get folded into the backend now,
for 7.5. I haven't had time yet to read the entire thread, but I saw
others making the same comment. It would make some of the listed
problems go away, or at least become far easier to deal with.

> For PostgreSQL 7.5, I plan to implement these new features:
>
> 1.Per database defaults and per table thresholds (including total
> exclusion)

Great!

> 2.Persistent data
> 3.Single-Pass Mode (external scheduling from cron etc...)
> 4.Off peak scheduling

Great again!

> 1. Per Database defaults and Per table Thresholds:
>
> 1.Store config data inside a special pg_autovacuum table inside
> existing databases that wants custom settings.

A natural if folded into the backend.

> 3.Single-Pass Mode (External Scheduling):
>
> I have received requests to be able to run pg_autovacuum only on request
> (not as a daemon) making only one pass over all the tables (not looping
> indefinately). The advantage being that it will operate more like the
> current vacuum command except that it will only vacuum tables that need
> to be vacuumed. This feature could be useful as long as pg_autovacuum
> exists outside the backend. If pg_autovacuum gets integrated into the
> backend and gets automatically started as a daemon during startup, then
> this option will no longer make sense.

It still might make sense. You could have a mode where the daemon
essentially sleeps forever, until explicitly woken up by a signal. When
woken, it makes one pass, and goes back to infinite sleep. Then provide
a simple way to signal the autovacuum process -- maybe an extension of
the current VACUUM syntax.

> 4.Off-Peak Scheduling:
>
> A fundamental advantage of our vacuum system is that the work required
> to reclaim table space is taken out of the critical path and can be
> moved to and off-peak time when cycles are less precious. One of the
> drawbacks of the current pg_autovacuum is that it doesn't have any way
> to factor this in.
>
> In it's simplest form (which I will implement first) I would add the
> ability to add a second set of thresholds that will be active only
> during an “off-peak” time that can be specified in the pg_autovacuum
> database, perhaps in a general_settings table.

I don't know how this would work, but it is for sure important. In the
recent testing I found that pg_autovacuum (well, lazy vacuum in general,
but I was using pg_autovacuum to control it) made a huge difference in
performance of batch transactions. They range from 4-5 seconds without
vacuum running, to as high as 15 minutes with vacuum running. With the
vacuum delay patch, delay = 1, pagecount = 8, I still saw times go as
high as 10 minutes. Backing vacuum off any more than that caused it to
fall behind the transaction rate unrecoverably. But as I said above, if
the transactions could complete without vacuum running in 4-5 seconds,
then vacuuming resumes for the 3-to-4 minutes between batches, all would
be well.

Joe