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: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>,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 17:28:04
Message-ID: 42CEB7A4.8070509@zeut.net (view raw or flat)
Thread:
Lists: pgsql-bugs
Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:

>>Hmm.  With integrated autovacuum, we could set something up to issue
>>vacuums separately to TOAST tables and the main table.  It'd probably be
>>a tad easier if the toast stats are separate from the main table; and an
>>autovac of the main table not necessarily would invoke vacuuming the
>>toast table.
>>    
>>
>>I'm not proposing it for 8.1 though ...
>>    
>>
>
>Well, why not?  Arguably Mark's problem is a bug, and it's not too late
>to address bugs.
>
>I checked what actually happens if you try to vacuum a toast table:
>
>regression=# vacuum pg_toast.pg_toast_169901;
>WARNING:  skipping "pg_toast_169901" --- cannot vacuum indexes, views, or special system tables
>VACUUM
>regression=#
>
>So it's not as bad as I thought.  Maybe just weakening the filter in the
>stats tables views isn't a bad idea after all.  Furthermore, we could
>allow VACUUM on a toast table to go through (is there any good reason
>to disallow it?) and then autovacuum would do more or less the right
>things with no further changes.
>  
>

That would be a nice improvement.  Any increase in the granularity of 
vacuum can't be a bad thing.

>I'm not sure about the idea of not vacuuming the toast table when we
>decide to vacuum the main table.
>

Perhaps by default issuing a vacuum against a table will also vacuum 
it's associated toast tables (can there be more than one toast table?) 
but add an option to the vacuum command not to vacuum the toast table. 
Or, perhaps this shouldn't be made totally public, ie. proving sql 
commands for it, perhaps it should only be available internally, that is 
to autovacuum and other tools that might issue a vacuum at the same low 
level as autovacuum.

Matt



In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-07-08 17:29:03
Subject: Re: pg_autovacuum: short, wide tables
Previous:From: Matthew T. O'ConnorDate: 2005-07-08 17:21:21
Subject: Re: pg_autovacuum: short, wide tables

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