X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 755C79FB29F for ; Thu, 10 Aug 2006 01:01:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 37147-10 for ; Thu, 10 Aug 2006 04:01:49 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id E4D549FB271 for ; Thu, 10 Aug 2006 01:01:48 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id m3so349431ugc for ; Wed, 09 Aug 2006 21:01:47 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=pgoFC9qVYWgKm12YZGgtsSKC1YinsUdHsHM8338Z7Z3cbbv5UjmSf4VxcCEG2xfRTtJTWQeLWN+8A496bybAmfIwhRIxlEaexl5z1PiwjeBKFAXroPBc00NMJ5bcEYoKdKFNwCYVHDvyEM3Wj5lPgbf/EYtwfrTAafpw/Z2f004= Received: by 10.67.93.7 with SMTP id v7mr1989269ugl; Wed, 09 Aug 2006 21:01:47 -0700 (PDT) Received: by 10.66.234.17 with HTTP; Wed, 9 Aug 2006 21:01:47 -0700 (PDT) Message-ID: <1c66bda80608092101m272dc6d3pd0145d4fd640cc3@mail.gmail.com> Date: Thu, 10 Aug 2006 16:01:47 +1200 From: adey To: "Scott Marlowe" Subject: Re: vacuumdb exclude tables option? Cc: "Joel Stevenson" , pgsql-admin@postgresql.org In-Reply-To: <1155148097.20252.106.camel@state.g2switchworks.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_63228_18645993.1155182507231" References: <1155148097.20252.106.camel@state.g2switchworks.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.515 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_MESSAGE, SARE_MILLIONSOF, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/124 X-Sequence-Number: 22667 ------=_Part_63228_18645993.1155182507231 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Does autovacuum replace the need for a FULL vacuum please (to recover free space, etc)? On 8/10/06, Scott Marlowe wrote: > > On Wed, 2006-08-09 at 13:24, Joel Stevenson wrote: > > Hi, > > > > I have a database that includes both highly transactional tables and > > archive tables - OLTP and OLAP mixed together. Some of the archival > > tables, which only experience inserts and reads, not updates or > > deletes, contain many millions of rows and so they take a *long* time > > to vacuum. Is there currently any means to exclude only these tables > > from vacuumdb operations other than explicitly vacuuming all the > > other tables and explicity skipping the archival tables? > > > > If not, are there any plans to add a command line switch to vacuumdb > > to exclude tables from it's operation, ala 'vacuumdb -d mytest -x > > archive_tab1 -x archive_tab2 -x archive_tab3'? I searched the list > > archives but didn't see anything about being able to do this. > > Something like this would save many hours of useless vacuuming in > > this particular setup. > > Does autovacuum fail you in this quest? I've found it's pretty good > about leaving along tables that don't need vacuuming. It's usually the > other way around that people have problems with, autovacuum NOT > vacuuming tables that need vacuuming often enough. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > ------=_Part_63228_18645993.1155182507231 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Does autovacuum replace the need for a FULL vacuum please (to recover free space, etc)?

On 8/10/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
On Wed, 2006-08-09 at 13:24, Joel Stevenson wrote:
> Hi,
>
> I have a database that includes both highly transactional tables and
> archive tables - OLTP and OLAP mixed together.  Some of the archival
> tables, which only experience inserts and reads, not updates or
> deletes, contain many millions of rows and so they take a *long* time
> to vacuum.  Is there currently any means to exclude only these tables
> from vacuumdb operations other than explicitly vacuuming all the
> other tables and explicity skipping the archival tables?
>
> If not, are there any plans to add a command line switch to vacuumdb
> to exclude tables from it's operation, ala 'vacuumdb -d mytest -x
> archive_tab1 -x archive_tab2 -x archive_tab3'?  I searched the list
> archives but didn't see anything about being able to do this.
> Something like this would save many hours of useless vacuuming in
> this particular setup.

Does autovacuum fail you in this quest?  I've found it's pretty good
about leaving along tables that don't need vacuuming.  It's usually the
other way around that people have problems with, autovacuum NOT
vacuuming tables that need vacuuming often enough.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

------=_Part_63228_18645993.1155182507231--