X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D74B39DD741 for ; Fri, 13 Jan 2006 15:18:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67246-09 for ; Fri, 13 Jan 2006 15:18:52 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 2306F9DD73F for ; Fri, 13 Jan 2006 15:18:50 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 5C1563982E; Fri, 13 Jan 2006 13:18:47 -0600 (CST) Date: Fri, 13 Jan 2006 13:18:47 -0600 From: "Jim C. Nasby" To: Jaime Casanova Cc: Szabolcs BALLA , pgsql-admin@postgresql.org Subject: Re: Vacuum is needed or not? Message-ID: <20060113191847.GK9017@pervasive.com> References: <1136463798.4221.35.camel@bh> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.088 required=5 tests=[AWL=0.088] X-Spam-Score: 0.088 X-Spam-Level: X-Archive-Number: 200601/163 X-Sequence-Number: 20251 On Thu, Jan 12, 2006 at 11:21:19PM -0500, Jaime Casanova wrote: > On 1/5/06, Szabolcs BALLA wrote: > > Hi, > > > > How do I know vacuum is need or not? I mean, are there any query to know > > status of statistics, tables, indexes, etc? > > at least, once every billion transactions to be safe... > > > (7.4.7) > > Our database size bigger than just go and do it. :( > > > > ahh... then execute it once a week or once a day, for avoid excessive growing... Even once a day can be awefully conservative for many databases. Keep in mind that every single row that is updated or deleted will hang around in the database until you vacuum; on a busy system that can result in a lot of wasted space. And once the bloat is there, it can be hard to get rid of. 7.4.7 has contrib/pg_autovacuum, and I highly recommend it. Also, http://lnk.nu/pervasive-postgres.com/7m4.asp and http://lnk.nu/pervasive-postgres.com/7jd.asp might be interesting reads for you. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461