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 A2FEB9DCCFC for ; Mon, 28 Nov 2005 17:22:40 -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 48119-01-8 for ; Mon, 28 Nov 2005 17:22:37 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id C132D9DD6DD for ; Mon, 28 Nov 2005 16:32:53 -0400 (AST) Received: by flake.decibel.org (Postfix, from userid 1001) id 922051525F; Mon, 28 Nov 2005 14:32:54 -0600 (CST) Date: Mon, 28 Nov 2005 14:32:54 -0600 From: "Jim C. Nasby" To: hubert depesz lubaczewski Cc: Colton Smith , pgsql-admin@postgresql.org Subject: Re: pgstattuple, vacuum and free_space Message-ID: <20051128203254.GR78939@pervasive.com> References: <43860F20.5080709@skio.peachnet.edu> <9e4684ce0511260239n1dd62c5cq3abdb3eee69705ec@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <9e4684ce0511260239n1dd62c5cq3abdb3eee69705ec@mail.gmail.com> X-Operating-System: FreeBSD 4.11-RELEASE-p10 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/321 X-Sequence-Number: 19694 On Sat, Nov 26, 2005 at 11:39:22AM +0100, hubert depesz lubaczewski wrote: > On 11/24/05, Colton Smith wrote: > > > > My question: when you vacuum a table and generate 'free_space', who is > > allowed to consume this 'free_space'? Is it released to the OS for > > general use? Or is it reserved just for the database? If the latter, is > > it reserved just for 'wind' (in this case)? > > > > > as far as i know this space is mapped as free witinh data-files of table > "wind". > thus effectivelyu - your OS doesnt get more free space, nor is (generally > speaking) the database. > benefit of this "free space" is that next inserts to wind table will fit > inside of this free space - thus stopping bloat of datafiles of this table. Keep in mind that the free space needs to be recorded in the Free Space Map for it to be re-used. If your FSM is too small then some of the free space will not be used until you vacuum again. Also, it is possible over time for tables to shrink. Vacuum will try to remove any pages from the end of the table that are empty. But it's difficult to make it so that pages at the end of the table are empty, and AFAIK indexes can never shrink, so ultimately something like a vacuum full/reindex or a cluster are your best bets for reclaiming disk space. -- 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