ClickHouse — A Columnar Analytics Database from the Inside

Store the Same Values with Different Types and Codecs, and Measure the Size

Goal

You build a table with default types and default codecs and benchmark tables that hold the same values with only the type or codec changed, and confirm from the post-compression bytes in system.columns which choice wins. At the end you see when the size changes if you change the codec of a table in production.

Why it matters

The disk, memory and read cost of an analytics database ultimately comes down to bytes. Even with the same data, the size differs several-fold depending on type and codec, and which codec wins is decided by the shape of the data — in this lab too, one specialized codec actually loses. So comparisons must always change only one thing with the same data and the same sort. The benchmark tables in this lab are that method. The grader does not trust the numbers you wrote and reads system.columns directly to compare, and the judgment is by bytes, not speed.

Steps

Create the database codecs and the table codecs.plain — columns ts DateTime, host String, status String, cpu Float64, bytes_total UInt64, latency_ms UInt32, err_code Nullable(UInt16) (in this order, with no codecs), engine MergeTree, ORDER BY (host, ts).
Run /opt/lab/fixtures/codecs/metrics.sql once to insert 1 million rows, and make it one part with OPTIMIZE TABLE codecs.plain FINAL.
Create the table codecs.status_bench (host String, ts DateTime, s_str String, s_lc LowCardinality(String), s_enum Enum8('ok' = 1, 'warn' = 2, 'error' = 3, 'timeout' = 4)) with ORDER BY (host, ts), put plain's status in all three columns, merge into one part, and write the post-compression bytes of the three columns and the name of the smallest column (smallest) to /root/ch/codecs/types.json.
Create the table codecs.ts_bench (host String, ts DateTime, ts_zstd DateTime CODEC(ZSTD(1)), ts_delta DateTime CODEC(Delta, ZSTD(1)), ts_dd DateTime CODEC(DoubleDelta, ZSTD(1))) with ORDER BY (host, ts), put plain's ts in all four timestamp columns, merge into one part, and write the post-compression bytes of the four columns and the name of the smallest column (best) to /root/ch/codecs/time.json.
Create the table codecs.num_bench (host String, ts DateTime, cnt UInt64 CODEC(ZSTD(1)), cnt_delta UInt64 CODEC(Delta, ZSTD(1)), lat UInt32 CODEC(ZSTD(1)), lat_t64 UInt32 CODEC(T64, ZSTD(1)), cpu Float64 CODEC(ZSTD(1)), cpu_gorilla Float64 CODEC(Gorilla, ZSTD(1))) with ORDER BY (host, ts), put plain's bytes_total, latency_ms and cpu into each pair, merge into one part, and write the post-compression bytes of the six columns and codec_lost, the array of names of specialized-codec columns that actually became larger than their pair, to /root/ch/codecs/numbers.json.
Create the table codecs.null_bench (host String, ts DateTime, err_null Nullable(UInt16), err_zero UInt16) with ORDER BY (host, ts), put plain's err_code in err_null and the value with NULL filled with 0 in err_zero, and merge into one part. Create /root/ch/codecs/q_isnull.sql, which counts the rows where it is NULL, and write null_uncompressed (the uncompressed bytes of err_null), isnull_bytes_read (the bytes_read of q_isnull.sql), and avg_null and avg_zero (the avg of the two columns) to /root/ch/codecs/nullable.json.
Create the table codecs.tuned (ts DateTime CODEC(Delta, ZSTD(1)), host LowCardinality(String), status LowCardinality(String), cpu Float64 CODEC(ZSTD(1)), bytes_total UInt64 CODEC(Delta, ZSTD(1)), latency_ms UInt16 CODEC(T64, ZSTD(1)), err_code UInt16) with ORDER BY (host, ts), move plain's rows into it (NULL becomes 0), merge into one part, and write the sums of post-compression bytes of the two tables to /root/ch/codecs/tuned.json as plain_total and tuned_total.
Create codecs.legacy with the same columns and engine as plain, insert plain's rows, merge into one part, and measure the post-compression bytes of ts (before). Measure again right after ALTER TABLE codecs.legacy MODIFY COLUMN ts CODEC(Delta, ZSTD(1)) (after_alter), and again after OPTIMIZE TABLE codecs.legacy FINAL (after_optimize), and write the three values to /root/ch/codecs/alter.json.

Notes

The server is already running when the Pod starts. Just typing clickhouse-client connects. If it has stopped, run ch-up.
Per-column size: SELECT name, data_compressed_bytes, data_uncompressed_bytes FROM system.columns WHERE database = 'codecs' AND table = '...'. The codec is shown in the compression_codec column of the same table (an empty string for the default codec).
To get numbers in JSON without quotes, --output_format_json_quote_64bit_integers 0. You may also save one JSONEachRow line to a file as it is.
Before measuring size, always OPTIMIZE TABLE ... FINAL. With several parts, the compression block boundaries differ and the numbers wobble, and the grader accepts only when there is one part (this is a lab procedure — in production, leave merging to the server).
Common mistakes: inserting plain twice into a benchmark table (2 million rows). In that case, TRUNCATE TABLE and insert again. If you write only a specialized codec and leave out the general-purpose codec (CODEC(Delta)), the server rejects it or the size does not shrink well.
Official docs: CODEC · Compression in ClickHouse · LowCardinality · system.columns

A table made without thinking

Create the database codecs and the table codecs.plain. The columns are, in order (with no codecs), ts DateTime, host String, status String, cpu Float64, bytes_total UInt64, latency_ms UInt32, err_code Nullable(UInt16), the engine is MergeTree, and the sorting key is ORDER BY (host, ts).

You deliberately build the shape you get by carrying over a source database schema as it is — strings as String, empty values as Nullable, and no codecs written, so the default (LZ4) applies. This table becomes the baseline for the comparisons in the later steps.

Insert 1 million rows and make one part

Run /opt/lab/fixtures/codecs/metrics.sql once to insert 1,000,000 rows into codecs.plain, and make the active part one with OPTIMIZE TABLE codecs.plain FINAL.

Just pass it to clickhouse-client with --queries-file. The script builds metrics reported every 10 seconds by 50 servers with a hash, so the same rows come out however many times you run it. To compare sizes, there must be one part so that the numbers do not wobble.

String · LowCardinality · Enum8

Create codecs.status_bench (host String, ts DateTime, s_str String, s_lc LowCardinality(String), s_enum Enum8('ok' = 1, 'warn' = 2, 'error' = 3, 'timeout' = 4)) with ORDER BY (host, ts), put plain's status in all three columns and merge into one part, and write the post-compression bytes of the three columns (s_str, s_lc, s_enum) and the name of the smallest column (smallest) to /root/ch/codecs/types.json.

If you put the same value in three times, like INSERT ... SELECT host, ts, status, status, status, the difference comes only from the type. String stores characters per row, LowCardinality stores a dictionary and numbers, and Enum8 stores a 1-byte number. Use data_compressed_bytes in system.columns — not data_uncompressed_bytes.

The timestamp column — LZ4 · ZSTD · Delta · DoubleDelta

Create codecs.ts_bench (host String, ts DateTime, ts_zstd DateTime CODEC(ZSTD(1)), ts_delta DateTime CODEC(Delta, ZSTD(1)), ts_dd DateTime CODEC(DoubleDelta, ZSTD(1))) with ORDER BY (host, ts), put plain's ts in all four timestamp columns and merge into one part, and write the post-compression bytes of the four columns (ts, ts_zstd, ts_delta, ts_dd) and the name of the smallest column (best) to /root/ch/codecs/time.json.

Because the sorting key is (host, ts), within one server the timestamp grows by a steady 10 seconds. A general-purpose codec cannot find repetition in an ever-growing integer, but if a preprocessing codec turns the values into differences from the neighbors, the situation changes. A codec chain is applied from the left, so write the preprocessing first and the general-purpose compression after.

A specialized codec does not always win

Create codecs.num_bench (host String, ts DateTime, cnt UInt64 CODEC(ZSTD(1)), cnt_delta UInt64 CODEC(Delta, ZSTD(1)), lat UInt32 CODEC(ZSTD(1)), lat_t64 UInt32 CODEC(T64, ZSTD(1)), cpu Float64 CODEC(ZSTD(1)), cpu_gorilla Float64 CODEC(Gorilla, ZSTD(1))) with ORDER BY (host, ts), put bytes_total in the cnt pair, latency_ms in the lat pair and cpu in the cpu pair and merge into one part, and write the post-compression bytes of the six columns and codec_lost, an array of names of specialized-codec columns that actually became larger than their pair (the ZSTD-only column), to /root/ch/codecs/numbers.json.

One side of each pair is ZSTD only and the other is specialized codec + ZSTD, so the difference is the share the specialized codec added. A cumulative counter always grows, latency has a narrow range, and CPU is a floating-point number to two decimal places. First guess and write down which will win, then check against the numbers. If there is no column that grew, it is an empty array.

Nullable places one more file

Create codecs.null_bench (host String, ts DateTime, err_null Nullable(UInt16), err_zero UInt16) with ORDER BY (host, ts), put plain's err_code in err_null and the value with NULL filled with 0 in err_zero, and merge into one part. Create /root/ch/codecs/q_isnull.sql, which counts the rows where err_null is NULL, and write null_uncompressed (data_uncompressed_bytes of err_null), isnull_bytes_read (statistics.bytes_read of q_isnull.sql), and avg_null and avg_zero (the avg of the two columns) to /root/ch/codecs/nullable.json.

A Nullable column keeps a separate value file and a 1-byte-per-row null map. A condition that asks only whether it is NULL reads only the null map, so the bytes read should equal the row count. If you mix in a condition that compares values, it reads the value file too. The mean leaves out NULL but includes 0 — you should be able to explain why the two avg values differ.

A table that gathers the winning choices

Create codecs.tuned (ts DateTime CODEC(Delta, ZSTD(1)), host LowCardinality(String), status LowCardinality(String), cpu Float64 CODEC(ZSTD(1)), bytes_total UInt64 CODEC(Delta, ZSTD(1)), latency_ms UInt16 CODEC(T64, ZSTD(1)), err_code UInt16) with ORDER BY (host, ts), move plain's rows into it (NULL in err_code becomes 0) and merge into one part, and write the sums of post-compression bytes of plain and tuned to /root/ch/codecs/tuned.json as plain_total and tuned_total.

It gathers the results of the earlier steps — it picked the types first (LowCardinality, a UInt16 that fits the range, 0 instead of NULL) and attached the codecs after. Gorilla lost in the earlier step, so cpu uses only ZSTD. You can get the sum for each table by grouping system.columns by table and adding.

When does it shrink if you change the codec of a production table

Create codecs.legacy with the same columns and engine as plain, insert plain's rows, merge into one part, and measure the post-compression bytes of ts (before). Measure again right after ALTER TABLE codecs.legacy MODIFY COLUMN ts CODEC(Delta, ZSTD(1)) (after_alter), and again after OPTIMIZE TABLE codecs.legacy FINAL (after_optimize), and write the three values to /root/ch/codecs/alter.json.

CREATE TABLE ... AS another_table copies the columns and engine as they are. Once a part is written it does not change, so ALTER changes only the rule for parts to be written in the future. Think about when an existing part is rewritten with the new codec. Measure all three times with the same system.columns query.