Re: Column storage positions

On 2/21/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> I'd expect the system being able to reoder the columns to the most
> efficient order possible (performance-wise and padding-saving-wise),
> automatically.  When you create a table, sort the columns to the most
> efficient order; ALTER TABLE ADD COLUMN just puts the new columns at the
> end of the tuple; and anything that requires a rewrite of the table
> (ALTER TABLE ... ALTER TYPE for example; would be cool to have CLUSTER
> do it as well; and do it on TRUNCATE also) again recomputes the most
> efficient order.

That's exactly what I'm proposing.  On table creation, the system
chooses an efficient column order for you.  The next time an ALTER
TABLE operation forces a rewrite, the system would recompute the
column storage order.  I hadn't thought of having CLUSTER also redo
the storage order, but that seems safe since it takes an exclusive
lock on the table.  I'm less sure about whether it's safe to do this
during a TRUNCATE.

phil