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: 42CEA904.6060605@zeut.net (view raw or flat)
Thread:
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.

Matt


In response to

Responses

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-2014 The PostgreSQL Global Development Group