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 CB7D8528CE for ; Wed, 15 Jun 2005 12:18:39 -0300 (ADT) 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 25130-03 for ; Wed, 15 Jun 2005 15:18:33 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 5148B52952 for ; Wed, 15 Jun 2005 12:18:31 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id j5FFIUFc025518 for ; Wed, 15 Jun 2005 15:18:30 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id j5FF43ZD022488 for pgsql-admin@postgresql.org; Wed, 15 Jun 2005 15:04:03 GMT (envelope-from news) From: Chris Browne X-Newsgroups: pgsql.admin Subject: Re: Vacuum full takes forever Date: Wed, 15 Jun 2005 10:34:30 -0400 Organization: cbbrowne Computing Inc Lines: 43 Message-ID: <60aclreljd.fsf@dba2.int.libertyrms.com> References: <42B026AB.9030907@barclab.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.17 (Jumbo Shrimp, linux) Cancel-Lock: sha1:I7Yach5IiMWB90h1tX7Q6HM6o74= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200506/100 X-Sequence-Number: 17855 pieterjan.savat@barclab.com (Pieter-Jan Savat) writes: > I'm facing the following problem. > > I have a postgres 8.0 DB with a table 'results' containing 6.000.000 > records. > This table has 16 indexes. Each one basically created to speed up > different queries. > > Because of some glitch in the system there has never been a VACUUM > FULL on this table. > When I try to do a full vacuum (on a dual-processor, 2GB RAM, ...) it > takes forever. I started the > vacuum at 6pm and 15 hours later it was still going on. > Just before starting vacuum full, I did a vacuum analyze (which took > about 15 minutes). I also > checked the amount of diskspace used for the indexes => 33% of 11 > available GigaBytes. > After killing the vacuum full my diskspace for the indexes has > increased to 41% of the 11 available GB. > > So does anyone know what I can do to fully vacuum my table? Or to at > least decrease the amount of diskspace used? Two choices offer themselves: 1. Drop all indices. Then VACUUM FULL the table. Then recreate the indices. 2. CLUSTER the table based on one of the indices. None of this is going to be pretty; it'll take hours. 1. and 2. are nearly equivalent; the conspicuous difference is that 1. will give you feedback along the way as it completes one step or another. -- (format nil "~S@~S" "cbbrowne" "acm.org") http://www.ntlug.org/~cbbrowne/sap.html Rules of the Evil Overlord #78. "I will not tell my Legions of Terror "And he must be taken alive!" The command will be: ``And try to take him alive if it is reasonably practical.''"