Tom Lane wrote:
"Matthew T. O'Connor" <matthew@zeut.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?
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
