X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 490979F931E for ; Tue, 9 May 2006 18:01:34 -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 23148-09 for ; Tue, 9 May 2006 18:01:25 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 023D09F9C9E for ; Tue, 9 May 2006 18:01:24 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k49L1GMZ017436; Tue, 9 May 2006 17:01:16 -0400 (EDT) To: mark.dingee@cox.net cc: pgsql-admin@postgresql.org Subject: Re: In-reply-to: <15254425.1147204995912.JavaMail.root@eastrmwml05.mgt.cox.net> References: <15254425.1147204995912.JavaMail.root@eastrmwml05.mgt.cox.net> Comments: In-reply-to message dated "Tue, 09 May 2006 16:03:14 -0400" Date: Tue, 09 May 2006 17:01:16 -0400 Message-ID: <17435.1147208476@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/89 X-Sequence-Number: 21621 writes: > I had an odd situation occur this morning with PGSQL 7.4 run on Red Hat Enterprise 4 (update 3) and could really use some wisdom. > ... Single postmaster running. > ... vacuum full is run every night as part of a cron job > ... At start, data files consume about 28 GB > ... This morning I dropped the database and reloaded from current backup > ... New instance consumes about 6 GB > I can only assume that the database was not compacted, but I thought vacuum full performed that function along with tuple maintenance. Can anyone expound on the problem and suggest a solution other than dropping and reloading the database? The evidence is mostly gone now, but what I'd suggest is waiting a while to see if it bloats again, and if so finding out exactly *where* the bloat is. Make some notes now about the sizes of your tables and indexes, and see what's getting larger. My guess offhand is that the problem is index bloat. VACUUM FULL not only doesn't help much with that, it tends to make it worse. If the database size is supposed to be fairly stable, you'd probably be better off with a maintenance regime that doesn't use VACUUM FULL but just plain VACUUM. Make sure your FSM settings are high enough. regards, tom lane