X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 66D719FB24C for ; Fri, 18 Aug 2006 12:32:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 64994-06 for ; Fri, 18 Aug 2006 12:32:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from lists.commandprompt.com (host-130.commandprompt.net [207.173.203.130]) by postgresql.org (Postfix) with ESMTP id D71369FA4F0 for ; Fri, 18 Aug 2006 12:32:37 -0300 (ADT) Received: from alvh.no-ip.org (201-221-217-80.bk12-dsl.surnet.cl [201.221.217.80]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id k7IFWPeC010363; Fri, 18 Aug 2006 08:32:26 -0700 Received: by alvh.no-ip.org (Postfix, from userid 1000) id 23F41C2DC66; Fri, 18 Aug 2006 11:32:13 -0400 (CLT) Date: Fri, 18 Aug 2006 11:32:13 -0400 From: Alvaro Herrera To: Bruno Wolff III , Joel Stevenson , pgsql-admin@postgresql.org Subject: Re: vacuumdb exclude tables option? Message-ID: <20060818153213.GM32181@alvh.no-ip.org> Mail-Followup-To: Bruno Wolff III , Joel Stevenson , pgsql-admin@postgresql.org References: <20060818152548.GA24101@wolff.to> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20060818152548.GA24101@wolff.to> User-Agent: Mutt/1.5.12-2006-07-14 X-Virus-Scanned: ClamAV version 0.88.3, clamav-milter version 0.88.3 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Fri, 18 Aug 2006 08:32:26 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.609 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, SARE_MILLIONSOF X-Spam-Level: X-Archive-Number: 200608/223 X-Sequence-Number: 22766 Bruno Wolff III wrote: > On Wed, Aug 09, 2006 at 11:24:03 -0700, > Joel Stevenson wrote: > > 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? > > Take a look at: > http://developer.postgresql.org/docs/postgres/routine-vacuuming.html#AUTOVACUUM > There will be a way to have per table settings in 8.2, though the interface > for setting things up is klunky. > I also seem to remember some discussion about having a separate transaction > for each table being cleaned as a new feature. Hmm, actually a separate transaction is being used for each table since about forever. What's new in 8.2 is the ability to ignore other long-running vacuums. (I think it will help Joel, in that vacuuming the archival tables will not stop dead tuples from being dropped from the "OLTP tables", though long-running SELECTs on the OLAP tables will continue to do so.) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.