Re: Simple improvements to freespace allocation

On 9 January 2014 01:38, Jim Nasby <jim(at)nasby(dot)net> wrote:
> On 1/8/14, 1:43 AM, Heikki Linnakangas wrote:
>
> I've wanted the cluster case for a long time. I also see the use for the
> RECENT scenario, especially if we had CLUSTER CONCURRENT that let you shrink
> the head of the table as needed.
>

> But there's probably a more important point to this one: for you to have any
> chance of packing you MUST get everything out of the tail of the table.
> Resetting to zero on every request is one possible way to do that, though it
> might be better to do something like reset only once the pointer goes past
> block X. The other thing you'd want is a way to force tuples off the last X
> pages. Due to a lack of ctid operators that was already hard, and HOT makes
> that even harder

Agreed

> (BTW, related to this you'd ideally want HOT to continue to
> operate on the front of the table, but not the back.)

That's a good idea.

> All that said, I've definitely wanted the ability to shrink tables in the
> past, though TBH I've wanted that more for indexes.
>
> Ultimately, what I really want on this front is:
>
> PACK TABLE blah BACKGROUND;
> CLUSTER TABLE blah BACKGROUND;
> REINDEX INDEX blah BACKGROUND;
>
> where BACKGROUND would respect a throttle setting. (While I'm dreaming, it'd
> be nice to have DATABASE/TABLESPACE/SCHEMA alternate specifications too...)

I like the idea of declarative deprioritisation.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services