Re: logical column ordering

Josh Berkus wrote:
> On 02/26/2015 10:49 AM, Jim Nasby wrote:

> > 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.

The changes in this patch aren't really optimizations -- they are a
complete rework on the design of storage of columns. In the current
system, columns exist physically on disk in the same order as they are
created, and in the same order as they appear logically (i.e. SELECT *,
psql's \d, etc). This patch decouples these three things so that they
can changed freely -- but provides no user interface to do so. I think
that trying to only decouple the thing we currently have in two pieces,
and then have a subsequent patch to decouple again, is additional
conceptual complexity for no gain.

A future patch can implement physical ordering optimization (group
columns physically to avoid alignment padding, for instance, and also
put not-nullable fixed-length column at the start of the physical tuple,
so that the "attcacheoffset" thing can be applied in more cases), but I
think it's folly to try to attempt that in the current patch, which is
already hugely complicated.

> 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.

The current patch will clear the road for such experimentation.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services