Re: logical column ordering

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: 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-26 19:17:24
Message-ID: 54EF7144.6030901@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 02/26/2015 10:49 AM, Jim Nasby wrote:
> On 2/23/15 5:09 PM, Tomas Vondra wrote:
>> Over the time I've heard various use cases for this patch, but in most
>> cases it was quite speculative. If you have an idea where this might be
>> useful, can you explain it here, or maybe point me to a place where it's
>> described?
>
> For better or worse, table structure is a form of documentation for a
> system. As such, it's very valuable to group related fields in a table
> together. When creating a table, that's easy, but as soon as you need to
> alter your careful ordering can easily end up out the window.
>
> Perhaps to some that just sounds like pointless window dressing, but my
> experience is that on a complex system the less organized things are the
> more bugs you get due to overlooking something.

Yes. Consider a BI table which has 110 columns. Having these columns
in a sensible order,even though some were added after table creation,
would be a significant usability benefit for DBAs.

A second usability benefit is making it easy to keep table columns for
import tables sync'd with COPY.

Neither of these is sufficient to overshadow performance penalties, but
they are both common activities/annoyances, and not speculative in the
least. And I haven't heard that there are any performance issues
associated with this patch. Are there?

> The other reason for this patch (which it maybe doesn't support
> anymore?) is to allow Postgres to use an optimal physical ordering of
> fields on a page to reduce space wasted on alignment, as well as taking
> nullability and varlena into account.

... and that's the bigger reason. I was under the impression that we'd
get LCO in first, and then add the column arrangement optimization in
the next version.

In fact, I would argue that LCO *needs* to be a feature at least one
version before we try to add column ordering optimization (COO). The
reason being that with LCO, users can implement COO as a client tool or
extension, maybe even an addition to pg_repack. This will allow users
to experiment with, and prove, algorithms for COO, allowing us to put in
a tested algorithm when we're ready to add it to core.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2015-02-26 20:13:34 Re: Precedence of standard comparison operators
Previous Message Tom Lane 2015-02-26 19:15:25 Re: plpgsql versus domains