6 ms·

I really like Gitlab's explanation on the matter! https://docs.gitlab.com/ee/development/ordering_table_columns.html https://docs.gitlab.com/ee/development/orde

I really like Gitlab's explanation on the matter! https://docs.gitlab.com/ee/development/ordering_table_columns.html https://docs.gitlab.com/ee/development/ordering_table_column...

MichaelApproved 4y ago
What about UPDATE performance? 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? [0] https://news.ycombinator.com/item?id=32055596 https://news.ycombinator.com/item?id=32055596
- singron 4y ago
  An update in postgres will always copy the tuple for MVCC, so it can't take advantage of an optimization like this to modify it in place.
  - jhgb 4y ago
    MVCC could use delta records instead. Doesn't Firebird do that?
    - CodeWriter23 4y ago
      > MVCC could use delta records instead. 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.
    - citrin_ru 4y ago
      Surprised to see Firebird being mentioned on HN. Worked with Firebird in 2000s (for a short time) and it left a good impression. How come almost no-one uses this DB anymore? At that time it was comparable to Postgres but looks like FB struggled to attract developers and users and without them it started to lag behind.
mattashii 4y ago
I just skimmed the document, and it uses wrong data to start with: the type sizes may be correct, but the table with alignment data [0] is wrong: byte, smallint and int align to 1, 2 and 4 bytes, respectively. The 'real' datatype also aligns to 4 bytes, as opposed to the 'one word' aka '8 bytes' in that doc. [0] https://docs.gitlab.com/ee/development/ordering_table_columns.html#type-sizes https://docs.gitlab.com/ee/development/ordering_table_column...
- samokhvalov 4y ago
  I don't think I understand you. 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.
  - ghusbands 4y ago
    The linked table claims that integers and smallints always require an alignment of one word, which is untrue, as you also point out. There is no sense in which a two-byte smallint 'requires' an alignment of four or eight bytes, as listed. 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.

I really like Gitlab's explanation on the matter! https://docs.gitlab.com/ee/development/ordering_table_columns.html https://docs.gitlab.com/ee/development/ordering_table_column...

MichaelApproved 4y ago

What about UPDATE performance? 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? [0] https://news.ycombinator.com/item?id=32055596 https://news.ycombinator.com/item?id=32055596

singron 4y ago
An update in postgres will always copy the tuple for MVCC, so it can't take advantage of an optimization like this to modify it in place.
- jhgb 4y ago
  MVCC could use delta records instead. Doesn't Firebird do that?
  - CodeWriter23 4y ago
    > MVCC could use delta records instead. 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.
  - citrin_ru 4y ago
    Surprised to see Firebird being mentioned on HN. Worked with Firebird in 2000s (for a short time) and it left a good impression. How come almost no-one uses this DB anymore? At that time it was comparable to Postgres but looks like FB struggled to attract developers and users and without them it started to lag behind.

mattashii 4y ago

I just skimmed the document, and it uses wrong data to start with: the type sizes may be correct, but the table with alignment data [0] is wrong: byte, smallint and int align to 1, 2 and 4 bytes, respectively. The 'real' datatype also aligns to 4 bytes, as opposed to the 'one word' aka '8 bytes' in that doc. [0] https://docs.gitlab.com/ee/development/ordering_table_columns.html#type-sizes https://docs.gitlab.com/ee/development/ordering_table_column...

samokhvalov 4y ago
I don't think I understand you. 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.
- ghusbands 4y ago
  The linked table claims that integers and smallints always require an alignment of one word, which is untrue, as you also point out. There is no sense in which a two-byte smallint 'requires' an alignment of four or eight bytes, as listed. 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.