Re: pg_autovacuum: short, wide tables

"Matthew T. O'Connor" <matthew(at)tocr(dot)com> writes:
> mark reid wrote:
>> What I believe is happening is that the main table doesn't meet the
>> minimum activity level for pg_autovacuum based on size / update
>> frequency, but the toast table would, though it isn't specifically
>> checked by pg_autovacuum.

> I don't think the problem has to do with toast, or pg_autovacuum missing
> the fact that the toast table has been updated. Rather I think the
> problem is that autovacuum believes that all updates are created equal.

I think Mark is probably on to something. The activity in the toast
table will show as deletes *in the toast table* ... and that activity
fails to show at all in the pg_stat_activity view, because it shows
only plain relations! So unless autovacuum is ignoring the stats views
and going directly to the underlying stats functions, it cannot see
at all that there is excessive activity in the toast table.

It strikes me that this is a definitional bug in the stats views.
Either we should change the filter to be "regular and toast tables",
or we should add columns to show activity in a table's toast table,
or we should just add the activity in the toast table to the parent
table's activity columns.

The first of these would be easiest but it seems quite likely to break
applications (eg, if unmodified, autovacuum would probably try to issue
vacuums against toast tables). And the last seems to be confusing.
So I think I favor adding columns.

regards, tom lane