Re: pgstattuple, vacuum and free_space

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