Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
long text (or varchar) or bytea values are not stored inside the actual row. –  a_horse_with_no_name Jul 5 at 19:55
    
@a_horse_with_no_name Thank you! So their ordering doesn't matter? –  user32234 Jul 5 at 20:07

1 Answer 1

up vote 0 down vote accepted

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 pointer) with pg_column_size() on the whole row.

Also as mentioned, 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 pointer per tuple in the data page. Details in this related answer:
Configuring PostgreSQL for read performance

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.