Re: logical column ordering

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: David Steele <david(at)pgmasters(dot)net>, Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: logical column ordering
Date: 2015-02-27 01:56:58
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 27/02/15 14:08, David Steele wrote:
> I agree with Jim's comments. I've generally followed column ordering
> that goes something like:
> 1) primary key
> 2) foreign keys
> 3) flags
> 4) other programmatic data fields (type, order, etc.)
> 5) non-programmatic data fields (name, description, etc.)
> The immediate practical benefit of this is that users are more likely to
> see fields that they need without scrolling right. Documentation is
> also clearer because fields tend to go from most to least important
> (left to right, top to bottom). Also, if you are consistent enough then
> users just *know* where to look.
> I wrote a function a while back that reorders columns in tables (it not
> only deals with reordering, but triggers, constraints, indexes, etc.,
> though there are some caveats). It's painful and not very efficient,
> but easy to use.
> Most dimension tables that I've worked with are in the millions of rows
> so reordering is not problem. With fact tables, I assess on a
> case-by-case basis. It would be nice to not have to do that triage.
> The function is attached if anyone is interested.
I've never formally written down the order of how I define
fields^H^H^H^H^H^H columns in a table, but David's list is the same
order I use.

The only additional ordering I do: is to put fields that are likely to
be long text fields, at the end of the record.

So I would certainly appreciate my logical ordering to be the natural
order they appear.


In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2015-02-27 02:03:52 Re: GSoC idea - Simulated annealing to search for query plans
Previous Message Fabrízio de Royes Mello 2015-02-27 01:50:21 Re: GSoC idea - Simulated annealing to search for query plans