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.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
