[0] https://docs.gitlab.com/ee/development/ordering_table_column...
1 word is 8 bytes, and if you use two columns, the first one having size 2 bytes, and the second – 8 bytes, you'll end up spending 8 + 8 = 16 bytes, because the first one (2byte in length) will be aligned with 6 zeroes to fill the whole "8-byte word". For example, with smalling (which has size 2 bytes but might be aligned, depending on the "column tetris" situation we have) + int8:
test=# select pg_column_size(1::int2);
pg_column_size
----------------
4
(1 row)
test=# create table tttt1 as select 1::int2 as c1, 2::int8 as c2;
SELECT 1
test=# create extension pageinspect;
CREATE EXTENSION
test=# select lp_len from heap_page_items(get_raw_page('tttt1', 0));
lp_len
--------
40
(1 row)
-- the total length of the tuple in this example is 40 bytes. Why 40? Because the tuple header is 23 bytes aligned to 3 "words", hence 24 bytes – so total is 24 + 8 + 8 = 40; though, effective data size us just 23 + 2 + 8 = 33; and 7 bytes are "wasted". And our int2 column was aligned by 6 additional bytes, to whole 8-byte word.Of course, if the second column is of size 4, then both columns fit into a single word – let's try real + int4:
if we'd have 2 int2 columns, followed by a int4 one, we'd use just one word (8 bytes to store this data, plus 24 bytes for tuple header):
test=# create table tttt2 as select 1::int2, 2::int2, 3::int4;
SELECT 1
test=# select lp_len from heap_page_items(get_raw_page('tttt2', 0));
lp_len
--------
32
(1 row)
-- in this case, the tuple length now is 32 bytes (24 bytes tuple header + 8 bytes for the data). Alignment was not needed.Thus, I don't see what's wrong with the document – probably I just didn't understand you.
Instead, the "alignment needed" column is a self-fulfilling prophecy that tells you the required alignment for the succeeding column if the succeeding column is of at least a size requiring that alignment, and then it's not a required alignment for that type but instead that for a different type.
E.g. attributes of types (in order) byte, smallint, smallint, int, bigint would have alignment padding of 0 (offset=0), 1 (o = 1 + 1 padding=2), 0 (o=4), 2 (o=6 +2p=8), and 4(o=12+4p=16), respectively, when all those attributes are filled with data.
[0] 'must' is the case most of the time, because variable length attributes have special handling rules when working with alignment of attributes -- but that's not important right now.
Your article and the parent one are explaining performance improvements related to INSERT and SELECT but what about the improved performance for UPDATE?
I wrote a long comment[0] on yesterday’s PostgreSQL post related to column order optimization with regards to UPDATE.
My information was 20+ years old and I figured it was horribly outdated but these articles make me think it could still be true.
The TLDR is you want to put variable length columns at the end because it makes UPDATE more efficient. My theory was it’ll be less likely the DB would need to move data around when updating the variable column contents.
Less data being moved = improved performance.
Seeing these articles means I was right with regards to improved performance of INSERT/SELECT but I wonder if I’m right about improved UPDATE performance.
Anyone know?
Every design choice is trading on thing for another. In your proposed case, you get storage savings in exchange for complex data structure design. And if the performance is a wash or not depends on how many columns and the types of the columns.
Probably better to understand the limitations of the tools you're using. If you can live in those boundaries, that's awesome. If you can't but can find a tool with different constraints, also awesome. If neither, your choice becomes understanding the limitations of your tools or infinite recursive optimization.