Re: pg_autovacuum: short, wide tables

Tom Lane wrote:

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

I think I'm missing something here. If I have a table t1 with a long
text column, and I do an update on that text column, doesn't that show
up as an update on table t1? And when there are enough upd/del
autovacuum will issue a VACUUM against t1, which will clean up the
associated toast table, right? So I think I must be missing something.
Could you please explain the problem in a little more detail.

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

Ok, maybe I get it now, are you saying that if I do:
update t1 set "long text column" = "some very long text value"
and the update doesn't touch any non-toast columns that the stats system
will not show that update against t1? If so, that is a problem.

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

Shouldn't the update to the toast table just be considered an update to
table t1? The fact that there is an underlying toast table is an
implementation detail that I don't think should show up in the stats system.

Matt