Re: Column storage positions

Phil Currier wrote:
> On 2/21/07, Alvaro Herrera <alvherre(at)commandprompt(dot)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.

I think you'd want to have a flag per field that tell you if the user
has overridden the storage pos for that specific field. Otherwise,
the next time you have to chance to optimize the ordering, you might
throw away changes that the admin has done on purpose. The same hold
true for a pg_dump/pg_reload cycle. If none of the fields had their
storage order changed manually, you'd want to reoder them optimally
at dump/reload time. If, however, the admin specified an ordering, you'd
want to preserve that.

greetings, Florian Pflug