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 EC8B3329EF7 for ; Wed, 8 Sep 2004 05:42:04 +0100 (BST) 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 04565-03 for ; Wed, 8 Sep 2004 04:41:59 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 5D6AD329EBB for ; Wed, 8 Sep 2004 05:41:58 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i884fu0M015659; Wed, 8 Sep 2004 00:41:56 -0400 (EDT) To: Christopher Browne Cc: pgsql-admin@postgresql.org Subject: Re: When will my database crash? In-reply-to: References: <20040908020214.M78932@eg-1.com> Comments: In-reply-to Christopher Browne message dated "Tue, 07 Sep 2004 23:22:31 -0400" Date: Wed, 08 Sep 2004 00:41:56 -0400 Message-ID: <15658.1094618516@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/71 X-Sequence-Number: 14774 Christopher Browne writes: > Not vacuuming for four months was a severe error, which means that you > now need to run a really full maintenance cycle. At the next possible > opportunity, you really need to do a VACUUM FULL on the whole > database. Dump, initdb, reload would very likely be less painful ... Something that hasn't ever been very clearly documented is that the VACUUM FULL mechanism is designed for compacting out relatively small amounts of dead space, like maybe 10-20%. When you've got a table that's got so much dead space that VACUUM FULL is going to move nearly all the live rows, the proposition is a loser. CLUSTER or dump/reload will be better, because those approaches don't bother to try to maintain the existing indexes incrementally: they throw away the indexes and start from scratch. regards, tom lane