Re: Autovacuum loose ends

Tom Lane wrote:

>"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 was thinking of users that might not want the vacuum delay settings on
small tables that will normally be vacuumed very quickly. This isn't a
very strong argument, but I thought I should mention it. Also, given
the projects tenancy towards not giving knobs to users unless we are
sure they need them, I think GUC only would be OK.

>>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.
>

Ok, so without reworking the stats system, I don't see an easy answer to
this other than autovacuum trying to sum up all the activity it finds in
all the different databases it looks at, but that seems rather ugly.
Any thoughts on improving the stats situation here?