X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id ADA6FD918F for ; Fri, 16 Sep 2005 13:59:21 -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 15737-08 for ; Fri, 16 Sep 2005 16:59:18 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id B2216D9189 for ; Fri, 16 Sep 2005 13:59:17 -0300 (ADT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 16 Sep 2005 11:59:16 -0500 Subject: Re: 7.4 <-> 8.0 From: Scott Marlowe To: snelling@email.marc.usda.gov Cc: gbarosio@gmail.com, pgsql-admin@postgresql.org In-Reply-To: <1126886236.12350.106.camel@localhost> References: <1126811405.12350.29.camel@localhost> <1126886236.12350.106.camel@localhost> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1126889956.30120.34.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Fri, 16 Sep 2005 11:59:16 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 required=5 tests=[AWL=0.005] X-Spam-Level: X-Archive-Number: 200509/188 X-Sequence-Number: 18925 On Fri, 2005-09-16 at 10:57, Warren Snelling wrote: > Guido, > > Thanks for the pointers. I have routinely vacuumed the db, but not > using full. > > By itself, the "vacuum full" run overnight did not free the space I was > expecting - du still showed the old database taking about 12 Gb disk, > and the fresh databases taking about 6.5 Gb. Running "reindex" on one > large table freed ~5 Gb, so the old db now takes 7 Gb. Reindexing the > remaining tables should get the databases to the same size. > > Would you suggest scripting the vacuum full / reindex process to run > periodically (once a week or so)? Right now disk space on this machine > is not an issue, but vacuuming and reindexing occasionally should keep > the db from consuming too much excess space. Assuming that you run regular vacuums often enough, and your fsm settings are high enough to hold on to all the references to the deleted tuples, you shouldn't need to run either full vacuums or reindexes. There are some usage patterns that can result in indexes with very sparsely populated blocks, such as when you create indexes to the numbers 1 to 1000, then delete 95% or so of those references, and all the new ones will be >1000, then repeat, increasing the number all the time. But that type of situation is the only that might require occasional reindexing.