Skip site navigation (1) Skip section navigation (2)

Re: pg_autovacuum: short, wide tables

From: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: mark reid <mail(at)markreid(dot)org>, pgsql-bugs(at)postgresql(dot)org,Jan Wieck <JanWieck(at)Yahoo(dot)com>
Subject: Re: pg_autovacuum: short, wide tables
Date: 2005-07-08 16:25:40
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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.


In response to


pgsql-bugs by date

Next:From: Renato GondimDate: 2005-07-08 16:33:13
Subject: Cast
Previous:From: Thanh Q LamDate: 2005-07-08 15:46:30
Subject: Re: postgresSQL data directory

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group