1

I read this excellent answer on optimal column ordering and noticed the summation:

Generally speaking, if you put the 8-byte-types first then the 4-byte-types and the 2-byte-types last you can't go wrong. Text or boolean do not have alignment restrictions like that, some other types do. In the end, you may save a couple of bytes per row at best. So, none of this is necessary for most people in most cases. But in your case it might save a couple of Gigabytes easily.

Does that mean that columns should be ordered from most space occupation to least?

If so, for bytea columns that always have constant 16-byte, 32-byte, or 64-byte lengths, do the same rules apply? What about a text column that varies between 1kb to 5mb, heavily skewed to 1kb?

The bytea columns are the variables used in all read conditions.

The upper limit on this table's length is tens of billions of rows per year.

Community
  • 1
  • long text (or varchar) or bytea values are not stored inside the actual row. –  Jul 05 '14 at 19:55
  • @a_horse_with_no_name Thank you! So their ordering doesn't matter? –  Jul 05 '14 at 20:07

1 Answers1

1

Does that mean that columns should be ordered from most space occupation to least?

No, not necessarily. You can play "column tetris" to minimize padding and thereby save some space. The rule of thumb I gave and you quoted is one simple strategy for basic types that require alignment.

As I mentioned in the quoted answer, you can test the actual storage size (excluding item identifier) with pg_column_size() on the whole row.

text and related varchar and char types do not require padding, so there is nothing to gain. The same is true for your bytea columns.

Concerning storage size for:

bytea columns that always have constant 16-byte, 32-byte, or 64-byte lengths

The manual page on bytea tells us : 

Storage Size
1 or 4 bytes plus the actual binary string

That means, the actual space required for a bytea column of 16-byte, 32-byte, or 64-byte length is 17 or 20 byte, 33 or 36 byte etc. respectively.

As demonstrated in this SQL Fiddle, a bytea variable always has an overhead of 4 bytes. When stored in a column, however, it starts out with just 1 byte of overhead and switches to 4 bytes for values of 127 bytes length or more.
24 bytes of overhead are added for the row type.
Another 4 bytes are needed for the item identifier per tuple in the data page. Details in this related answer:

As for alignment requirements of bytea, per documentation:

Values with single-byte headers aren't aligned on any particular boundary, either.

I would suggest you read that whole chapter - probably a couple of times, it's a tough read.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600