Autovacuum loose ends - Mailing list pgsql-hackers

I've applied Alvaro's latest integrated-autovacuum patch.  There are
still a number of loose ends to be dealt with before beta, though:

* Not all the functionality of the current contrib code seems to have
made it in.  In particular I noted the "sleep scaling factor" is
missing, as well as the options to use nondefault vacuum_cost_delay
settings.  (I'm not sure how important the sleep scale factor is,
but the vacuum cost options seem pretty critical for practical use.)
There may be other stuff to move over; Matthew or someone more familiar
than I with the contrib version needs to take a look.  (I have refrained
from removing the contrib module until we're sure we have extracted
everything from it.)

* The code does not make a provision to ignore temporary tables.
Although vacuum.c and analyze.c will disregard the request to touch
such tables, it'd probably be better to recognize the situation further
upstream.  In particular it seems that autovacuum will continually throw
ANALYZE requests for a temp table due to lack of stats.

* ANALYZE also refuses to do anything with pg_statistic itself, which
is another case that may need special treatment to avoid useless cycles.

* For that matter I'm unconvinced that it's a good idea to try to force
the pgstat DB to pick up every table in every database.  If there's no
entry it's because the table is not getting modified, and therefore it
seems to me that we can just leave well enough alone.  The code really
is not very good about doing nothing where nothing is called for ;-)

* The code ignores datallowconn and therefore will periodically vacuum
template0.  I've got mixed emotions about this --- it could save
someone's bacon if they failed to properly VACUUM FREEZE a template
database, but in 99.99% of installations it's just wasted cycles.
Maybe it'd make sense to perform XID-wraparound-prevention vacuuming,
but not anything more, in a template DB.  Thoughts?

* Or actually, it would vacuum template0, except that since no regular
backend ever connects to template0, there will be no stats DB entry for
it and so the loop in AutoVacMain will ignore it.  This is definitely
BAD as it means that a database that's not been touched since postmaster
start will never be vacuumed, not even for XID wraparound prevention.
That test needs to be weakened.

* I'm still pretty concerned about the handling of shared catalogs.
AFAICS the current pgstats infrastructure simply gets this wrong,
meaning that shared catalogs will not get adequate vacuuming.  We need
to fix that.

* As Alvaro noted, the default parameter settings need a lookover.
What is in the patch is not what was the default in the contrib module,
but the contrib defaults seem awfully passive.

* The documentation badly needs work.  I committed some minimal
additions to runtime.sgml and catalogs.sgml, but the chapter about
routine maintenance needs a section added about how to use autovac.
        regards, tom lane