Skip site navigation (1) Skip section navigation (2)

Re: pgstattuple, vacuum and free_space

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: hubert depesz lubaczewski <depesz(at)gmail(dot)com>
Cc: Colton Smith <smith(at)skio(dot)peachnet(dot)edu>, pgsql-admin(at)postgresql(dot)org
Subject: Re: pgstattuple, vacuum and free_space
Date: 2005-11-28 20:32:54
Message-ID: 20051128203254.GR78939@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-admin
On Sat, Nov 26, 2005 at 11:39:22AM +0100, hubert depesz lubaczewski wrote:
> On 11/24/05, Colton Smith <smith(at)skio(dot)peachnet(dot)edu> 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(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

pgsql-admin by date

Next:From: Gourish SingbalDate: 2005-11-29 13:53:15
Subject: Re: pgstattuple, vacuum and free_space
Previous:From: Christopher SmithDate: 2005-11-28 03:02:38
Subject: Postgresql Replication High Availability

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group