Re: Autovacuum loose ends

"Matthew T. O'Connor" <matthew(at)zeut(dot)net> writes:
> Speaking of which, I think I mentioned this to Alvaro, but I guess it
> just didn't make it in. The pg_autovacuum table should have a few
> additional columns that allow setting vacuum delay settings on a per
> table basis. I also think that there should be GUC settings for the
> default autovacuum delay settings which an admin might want to be
> separate from the system wide default vacuum delay settings.

I was thinking GUC settings only; is there a real use-case for
table-specific delay parameters? ISTM the point of the delay parameters
for autovac is to put a lid on its impact on interactive response. Seen
in that light, you do not care exactly which table it's hitting at the
moment.

>> * I'm still pretty concerned about the handling of shared catalogs.

> This was handled in the contrib version by only vacuuming shared
> catalogs inside template1, however it would then analyze those tables in
> each and every database. Is there a reason this solution is not
> adequate?

The problem is that now that we've invented the default postgres
database, it becomes more plausible to think about installations that
haven't got a template1 at all. I'd prefer a solution that does not
assume the presence of any specific database. ISTM reasonable to
process the shared catalogs symmetrically in every DB: look to see
if they need vacuuming or not. The problem (which was also a problem
for the contrib version) is that the stats system fails to maintain
a single set of stats for a shared catalog --- operations get counted
under whichever DB they were issued from. This means that autovac
will underestimate the need for vacuuming of a shared catalog, since
no matter where it looks from, it will see only a portion of the
true update activity.

regards, tom lane