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

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