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 8506B9FB2F0 for ; Thu, 10 Aug 2006 11:17:14 -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 19386-08 for ; Thu, 10 Aug 2006 11:17:05 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 1E8439FB2DA for ; Thu, 10 Aug 2006 11:17:04 -0300 (ADT) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Thu, 10 Aug 2006 14:16:59 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 10 Aug 2006 09:16:59 -0500 Subject: Re: vacuumdb exclude tables option? From: Scott Marlowe To: adey Cc: Joel Stevenson , pgsql-admin@postgresql.org In-Reply-To: <1c66bda80608092101m272dc6d3pd0145d4fd640cc3@mail.gmail.com> References: <1155148097.20252.106.camel@state.g2switchworks.com> <1c66bda80608092101m272dc6d3pd0145d4fd640cc3@mail.gmail.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1155219418.20252.137.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Thu, 10 Aug 2006 09:16:59 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.1 tagged_above=0 required=5 tests=AWL, SPF_PASS, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200608/134 X-Sequence-Number: 22677 On Wed, 2006-08-09 at 23:01, adey wrote: > Does autovacuum replace the need for a FULL vacuum please (to recover > free space, etc)? In most cases, yes. The idea is that autovacuum should put the database into a "steady state" where there is some % of each table that is free space and being recycled without the table growing or shrinking due to vacuuming. But it doesn't always work out that way. If you're updating the table very quickly, then you might outrun auto vacuum and get into a sticky situation. The somewhat bad situation is that your FSM settings are high enough to store all the empty bits for reuse, but there's a LOT of them in some often updated table. That's a bit bothersome. The worst case scenario is that your FSM settings are not high enough, and you've not got a table which has an increasing number of empty spaces in it, even though the amount of data is steady state. Generally, there are autovacuum settings and fsm settings that will keep this from happening, but once you've got bloat, the only fix is vacuum full, cluster, or reindex. I.e. something that copies the data into a new file for you. All three of those are nasty in terms of the locks they have to take out, and they block other access on the table while running.