APPENDIX · P1 · COMPRESSION · FOR SLIDE 6
← Back to Slide 6
P1. How much does each column weigh?
SELECT name,
formatReadableSize(data_compressed_bytes) AS disk,
round(data_uncompressed_bytes
/ data_compressed_bytes, 1) AS ratio
FROM system.columns
WHERE database = currentDatabase()
AND table = 'events'
ORDER BY data_compressed_bytes DESC;
What the result should be
ts 25.46 MiB 1.5
match_id 18.08 MiB 2.1
player_id 16.44 MiB 1.2
damage 4.32 MiB 2.2
mode 2.62 MiB 1.8
region 2.61 MiB 1.8
event_type 23.13 KiB 211.5
event_type ×211
player_id ×1.2
ts — the heaviest
4 values, LowCardinality, and first place in ORDER BY: long runs of identical values.
Random numbers within a day show almost no compression: the order of the rows is just as important as the type.
Milliseconds: DateTime64(3). If seconds are sufficient, use DateTime; for time, there are the Delta and DoubleDelta codecs.
Compression depends on the order of the rows. Change the ORDER BY clause, and the column sizes will change. The number of bytes may vary slightly between ClickHouse versions.