X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0B10A56832 for ; Thu, 17 Mar 2005 22:35:15 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 03803-09 for ; Thu, 17 Mar 2005 22:35:10 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 01270565B6 for ; Thu, 17 Mar 2005 22:35:07 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2HMZ6S3027986; Thu, 17 Mar 2005 17:35:06 -0500 (EST) To: Gaetano Mendola Cc: Scott Marlowe , "pgsql-admin@postgresql.org" Subject: Re: Vacuum full on a big table In-reply-to: <4239FC4D.40501@bigfoot.com> References: <4239F22C.4040401@bigfoot.com> <1111094421.28555.244.camel@state.g2switchworks.com> <4239FC4D.40501@bigfoot.com> Comments: In-reply-to Gaetano Mendola message dated "Thu, 17 Mar 2005 22:53:17 +0100" Date: Thu, 17 Mar 2005 17:35:06 -0500 Message-ID: <27985.1111098906@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/274 X-Sequence-Number: 16772 Gaetano Mendola writes: > Scott Marlowe wrote: >> Is there a reason you're doing a full vacuum? > Because I'm only running pg_autovacuum since one month now, but I see > that for same table is a disaster do not vacuum full once in a day. You need to find out why regular vacuum isn't getting the job done, not look for a bigger hammer. What is growing, exactly --- the table, its indexes (all of them, or only some), the TOAST table or index? Do you have adequate FSM space according to what VACUUM VERBOSE says? It's much more likely that we'd remove VACUUM FULL entirely than do major work on it (like try to make it work on part of a table, which is something I don't believe could work anyway). That entire approach is fundamentally broken when it comes to major rearrangements of huge tables: it requires exclusive lock, it takes forever, and it bloats the table's indexes. regards, tom lane