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

Re: pg_autovacuum: short, wide tables

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>,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 18:17:39
Message-ID: 20050708181739.GF1915@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-bugs
On Fri, Jul 08, 2005 at 01:29:03PM -0400, Tom Lane wrote:
> "Matthew T. O'Connor" <matthew(at)zeut(dot)net> writes:
> > Tom Lane wrote:
> >> In particular, I think people would like to be able to use the stats
> >> views to see how much toast-related I/O is going on, and not have that
> >> smushed together with main-table I/O.
> 
> > Fair enough, but how are you planning to display the data,
> 
> At the moment I'm thinking "just like a regular table" --- see my
> last message to Alvaro.  The existing backend code will count each
> toast-chunk insert or delete just like a normal row insert or delete,
> so AFAICS this will produce sane-looking stats that autovac could
> use the same way as for a plain table.
> 
> The main bit of additional logic that might be needed is an awareness
> that firing a VACUUM on a main table will implicitly fire one on its
> toast table, and so you'd not want to go and issue the toast table
> VACUUM separately.

I don't see any reason why this wouldn't work.  I even think it'd be
very easy to implement (from the autovacuum POV -- not sure about the
stat system).  Furthermore, the awareness you mention is also very easy
to implement: we just need to make sure the pg_autovacuum tuple for the
toast table is updated when it is vacuumed, which is just an additional
function call.

Have you looked at the autovacuum integration patch?  There are some
ugly points on which I'd like your comments.  For example, currently
vacuum() takes a RangeVar parameter; it'd be cleaner if it used a Relid
instead, and we provided a RangeVar wrapper.  Also I'm not sure about
the shutdown sequence, mainly the bit about delaying the bgwriter shut
down until after autovac has already shut down.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"La conclusiĆ³n que podemos sacar de esos estudios es que
no podemos sacar ninguna conclusiĆ³n de ellos" (Tanenbaum)

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-07-08 18:35:14
Subject: Re: pg_autovacuum: short, wide tables
Previous:From: Tom LaneDate: 2005-07-08 17:29:03
Subject: Re: pg_autovacuum: short, wide tables

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