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

Re: pg_autovacuum: short, wide tables

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Mark Reid <mail(at)markreid(dot)org>,"Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: pg_autovacuum: short, wide tables
Date: 2005-07-11 18:09:36
Message-ID: 42D2B5E0.5070801@Yahoo.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On 7/8/2005 12:57 PM, Tom Lane wrote:

> Mark Reid <mail(at)markreid(dot)org> writes:
>> 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. 
> 
> Exactly.  If autovac were looking at the properties of the toast table
> it would think a vacuum pass was warranted sooner than it thinks from
> just looking at the main table.
> 
> Admittedly this doesn't come into play unless you have a fairly large
> number of toast chunks per main-table row, so the rows in question have
> to be really wide (dozens of KB even after compression) before it gets
> to be a big deal.

I think this only becomes an issue if the toasted columns not only tend 
to have vastly different data sizes, but in addition that most of the 
updates actually happen on wide rows. Otherwise, the percentage of dead 
tuples in the main and toast table should be fairly similar.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck(at)Yahoo(dot)com #

In response to

pgsql-bugs by date

Next:From: rmkmlDate: 2005-07-11 21:39:33
Subject: Freebsd and postgresql 748: pg_dump compile error
Previous:From: Dennis BjorklundDate: 2005-07-11 13:27:55
Subject: Re: [BUGS] BUG #1745: Unable to delete data from the

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