Re: Improving free space usage (was: Reducing relation locking overhead)

At the risk of editorializing...

* Allow for tables with more than a certain percent of free space to
force new tuple allocation in the last few pages to come only from the
FSM (prefering the earliest pages), with the intention of allowing the
table to shrink

I also believe there's also a second TODO:

* Allow FSM page hand-out on tables to be influenced by the clustered
index. This would help maintain cluster order.

On Thu, Mar 02, 2006 at 10:12:59PM -0500, Bruce Momjian wrote:
>
> Added to TODO:
>
> * Allow FSM to return free space toward the beginning of the heap file, in
> hopes that empty pages at the end can be truncated by VACUUM
>
>
> ---------------------------------------------------------------------------
>
> Jim C. Nasby wrote:
> > On Fri, Dec 09, 2005 at 12:00:14AM +0200, Hannu Krosing wrote:
> > > > Along those lines, I've wondered if it makes sense to add more
> > > > flexibility in how free space is reclaimed in a table. One obvious
> > > > possibility is to have a strategy where new tuples will always look to
> > > > the FSM for space (instead of going into the current page if possible),
> > > > and the FSM will always hand out the earliest page in the table it has.
> > > > This mode would have the effect of moving tuples towards the front of
> > > > the table, allowing for space reclamation. A variation might be that
> > > > this mode will not effect tuples that are generated as part of an UPDATE
> > > > and are in the first x% of the table, since it doesn't make sense to
> > > > move a tuple from page 2 to page 1 in a 1000 page table.
> > >
> > > This % could be depending on some "fill factor" of the table, aiming not
> > > to move tuples, that would end up in the final volume of a balance
> > > table, which, in case of heavily updated table, would probably be 2 to 3
> > > times the volume of densely populated table.
> > >
> > > > Another possibility is to always go to the FSM and to have the FSM hand
> > > > back the page that is closest to the new tuple according to a certain
> > > > index. This would allow for ALTER TABLE CLUSTER to be much more
> > > > self-maintaining. The downside is that you'd have to do a lookup on that
> > > > index, but presumably if the index is important enough to cluster on
> > > > then it should be well-cached. There's probably some other tweaks that
> > > > could be done as well to make this more performant.
> > >
> > > Yes, I agree on all your points about better placement of new tuples,
> > > all they would be useful indeed.
> >
> > Sounds like a TODO, barring objections...
> > --
> > 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
> >
>
> --
> Bruce Momjian http://candle.pha.pa.us
> SRA OSS, Inc. http://www.sraoss.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
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