wierd_compression

wierd_compression.sql

CREATE TABLE t
(
    key_zstd1    Int64 CODEC(ZSTD(1)),
    key_lz4      Int64 CODEC(LZ4),
    key_t64_zstd Int64 CODEC(T64,ZSTD(1)),
    key_int8     Int8 CODEC(ZSTD(1)),
    r Int32
)
ENGINE = MergeTree
ORDER BY (key_zstd1, r);

insert into t
select [0,-1,-4,6,-3][number%5] key,  key, key, key, number%3333
from numbers(100000000);

optimize table t final;

SELECT
    column,
    formatReadableSize(sum(column_data_compressed_bytes) AS size) AS compressed,
    formatReadableSize(sum(column_data_uncompressed_bytes) AS usize) AS uncompressed
FROM system.parts_columns
WHERE (active = 1) AND (database LIKE '%') AND (table LIKE 't') and column like 'key%'
GROUP BY
    column
ORDER BY size DESC;

┌─column───────┬─compressed─┬─uncompressed─┐
│ key_zstd1    │ 10.14 MiB  │ 762.94 MiB   │
│ key_lz4      │ 3.42 MiB   │ 762.94 MiB   │  lz4 is better than zstd1
│ key_t64_zstd │ 758.32 KiB │ 762.94 MiB   │
│ key_int8     │ 67.07 KiB  │ 95.37 MiB    │  the same data in int8 is 154 times smaller
└──────────────┴────────────┴──────────────┘