Re: FSM search modes

On Sep 18, 2009, at 1:09 AM, Simon Riggs wrote:
> On Fri, 2009-09-18 at 10:47 +0900, Itagaki Takahiro wrote:
>> Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
>>
>>> * compact - page selection specifically attempts to find the lowest
>>> numbered blocks, so that the table will naturally shrink over time.
>>
>> We cannot shrink the table if one tuple remains at the end of table
>> and the tuple is always HOT-updated, because we put a new tuple into
>> the same page where the old tuple is placed if possible.
>>
>> In addition to your intelligent FSM search modes,
>> do we need another algorithm to make the compaction to work better?
>
> Perhaps we can have an additional piece of information about a table.
> Something like target_size, so that normal updaters that attempt HOT
> updates on blocks greater than target_size would know to avoid that
> block and to seek a new location for the row lower in the table.
> Perhaps
> such information could be reset and then sent via invalidation
> mechanisms.
>
> I'm thinking along the lines of a "fire alarm". An occasional
> mechanism
> by which we can inform users of the need to evacuate certain blocks.

It might be better to not beat around the bush and provide a vacuum
mode that explicitly tries to free the last X percent of the table.
That's especially handy for a very large table, because you probably
don't want to be forced into scanning the whole thing in vacuum just
to free some space at the end. This mode could also be more
aggressive about trying to acquire the lock that's needed to trim the
file on disk.

That said, *any* step that improves dealing with table bloat is
extremely welcome, as right now you're basically stuck rebuilding the
table.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828