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

Re: pg_autovacuum: short, wide tables

From: Mark Reid <mail(at)markreid(dot)org>
To: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, 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:48:22
Message-ID: 42CEAE56.3070704@markreid.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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.

In response to

Responses

pgsql-bugs by date

Next:From: Alvaro HerreraDate: 2005-07-08 16:55:17
Subject: Re: pg_autovacuum: short, wide tables
Previous:From: Renato GondimDate: 2005-07-08 16:33:13
Subject: Cast

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