Hello all,
Am I right that an (un)optimized column order in regard to storage (bigger types first, smaller ones last) is irrelevant as soon as it gets to compression due to the switch to column-oriented storage?
Tests seem to confirm this assumption. Uncompressed chunks at the optimized table need less storage, compressed chunks the same amount.
results, e.g. _hyper_2_1_chunk compared to _hyper_5_36_chunk
SELECT * FROM chunks_detailed_size('unoptimized_column_order');
chunk_schema | chunk_name | table_bytes | index_bytes | toast_bytes | total_bytes | node_name
-----------------------+-------------------+-------------+-------------+-------------+-------------+-----------
_timescaledb_internal | _hyper_2_25_chunk | 6610944 | 1376256 | 0 | 7987200 |
_timescaledb_internal | _hyper_2_27_chunk | 4800512 | 999424 | 0 | 5799936 |
_timescaledb_internal | _hyper_2_13_chunk | 65536 | 24576 | 630784 | 720896 |
_timescaledb_internal | _hyper_2_15_chunk | 65536 | 24576 | 647168 | 737280 |
_timescaledb_internal | _hyper_2_17_chunk | 65536 | 24576 | 622592 | 712704 |
_timescaledb_internal | _hyper_2_19_chunk | 65536 | 24576 | 630784 | 720896 |
_timescaledb_internal | _hyper_2_1_chunk | 16384 | 24576 | 139264 | 180224 |
_timescaledb_internal | _hyper_2_21_chunk | 65536 | 24576 | 630784 | 720896 |
_timescaledb_internal | _hyper_2_23_chunk | 65536 | 24576 | 630784 | 720896 |
_timescaledb_internal | _hyper_2_3_chunk | 65536 | 24576 | 614400 | 704512 |
_timescaledb_internal | _hyper_2_5_chunk | 65536 | 24576 | 647168 | 737280 |
_timescaledb_internal | _hyper_2_7_chunk | 65536 | 24576 | 638976 | 729088 |
_timescaledb_internal | _hyper_2_9_chunk | 65536 | 24576 | 630784 | 720896 |
_timescaledb_internal | _hyper_2_11_chunk | 65536 | 24576 | 622592 | 712704 |
(14 rows)
SELECT * FROM chunks_detailed_size('optimized_column_order');
chunk_schema | chunk_name | table_bytes | index_bytes | toast_bytes | total_bytes | node_name
-----------------------+-------------------+-------------+-------------+-------------+-------------+-----------
_timescaledb_internal | _hyper_5_39_chunk | 6119424 | 1376256 | 0 | 7495680 |
_timescaledb_internal | _hyper_5_40_chunk | 4440064 | 999424 | 0 | 5439488 |
_timescaledb_internal | _hyper_5_31_chunk | 65536 | 24576 | 622592 | 712704 |
_timescaledb_internal | _hyper_5_32_chunk | 65536 | 24576 | 630784 | 720896 |
_timescaledb_internal | _hyper_5_33_chunk | 65536 | 24576 | 647168 | 737280 |
_timescaledb_internal | _hyper_5_34_chunk | 65536 | 24576 | 622592 | 712704 |
_timescaledb_internal | _hyper_5_35_chunk | 65536 | 24576 | 630784 | 720896 |
_timescaledb_internal | _hyper_5_36_chunk | 16384 | 24576 | 139264 | 180224 |
_timescaledb_internal | _hyper_5_37_chunk | 65536 | 24576 | 630784 | 720896 |
_timescaledb_internal | _hyper_5_38_chunk | 65536 | 24576 | 630784 | 720896 |
_timescaledb_internal | _hyper_5_41_chunk | 65536 | 24576 | 614400 | 704512 |
_timescaledb_internal | _hyper_5_42_chunk | 65536 | 24576 | 647168 | 737280 |
_timescaledb_internal | _hyper_5_43_chunk | 65536 | 24576 | 638976 | 729088 |
_timescaledb_internal | _hyper_5_44_chunk | 65536 | 24576 | 630784 | 720896 |
(14 rows)
Thanks and best regards,
Paul