Re: pg_autovacuum: short, wide tables

Matthew T. O'Connor wrote:

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

I think the issue is that a single update to the main table causes a
whole bunch of updates to the toast table. So in my case (with the
vacuum output attached previously), a thousand updates to the main table
entails tens of thousands of updates to the toast table.
INFO: "properties": found 1361 removable, 8 nonremovable row versions
INFO: "pg_toast_10043014": found 21052 removable, 24 nonremovable row
versions

based on the default autovacuum thresholds, 21000 updates to a table
with 24 rows should have triggered a vacuum on the toast table, which is
why i pointed the finger that way originally.

-Mark.