Types and Codecs — Storing the Same Values Smaller

In one line

In column-oriented storage there are three knobs that decide disk size — the sorting key, the type and the codec. The type decides the size before compression, and the codec shrinks those bytes in two stages (a preprocessing step that rearranges values, then general-purpose compression). Which one wins is decided by the data, so do not guess; measure with the same data and choose.

Why this was needed

In the first module we saw that the compression ratio differs greatly per column. A column with five site names was almost free, and a timestamp column that keeps growing barely shrank with the default codec (LZ4). Measured with this lab's data, 1 million timestamps at 10-second intervals (4,000,000 bytes) come to 4,017,298 bytes after LZ4 — actually slightly larger. LZ4 is an algorithm that looks for "does the same byte chunk appear again", and an ever-growing integer is a different byte each time, so there is nothing to find.

But this column has a clear structure. The difference between neighboring values is always 10. All you need to do is turn this structure into a shape LZ4 can recognize (10, 10, 10 …). This is why the official documentation (Compression in ClickHouse) names the sorting key, the data type and the codec as the three factors that affect compression, and says "all are decided by the schema". Choose the schema well once, and all the data that comes in every day gets that much smaller.

How it works

A codec is a chain. CODEC(Delta, ZSTD(1)) is applied in order from the left. The official documentation (CODEC) divides codecs in two. General-purpose codecs (LZ4, LZ4HC, ZSTD) compress at the byte level, and specialized codecs use the nature of the data to rearrange values.

Codec	What it does	Suitable data
Delta	Replaces values with differences from the neighbors	Monotonically increasing integers and timestamps
DoubleDelta	The difference of differences, in compressed bits	Time series with regular intervals
T64	Groups 64 values and cuts off the unused upper bits	Integers with a narrow range
Gorilla	XOR with the previous floating-point value	Slowly changing gauges

Delta and DoubleDelta are written in the documentation as "data preparation codec" — used alone, they shrink nothing. The server knows it too. Float64 CODEC(Delta) is rejected with a "does not compress anything" error, and if you reverse the order, like CODEC(ZSTD(1), Delta), you get a "meaningless" error. It makes no sense to apply a transformation after general-purpose compression.

Measured for the same timestamp column with this lab's data, it looks like this (after compression, approximately): LZ4 4.0MB · ZSTD 2.9MB · Delta+ZSTD 5KB · DoubleDelta+ZSTD 5KB · DoubleDelta alone 128KB. When the preprocessing exposes the structure, the general-purpose codec erases almost all the rest. The documentation's advice, "ZSTD as the default, Delta for integer and date sequences", holds exactly.

But a specialized codec does not always win. In the same lab, when I attached Gorilla to the CPU utilization (Float64) column, it became larger than the column using ZSTD alone (3.0MB → 4.6MB). Values cut at two decimal places differ in many bits as binary floating-point, so the XOR does not get small. Conversely, latency (UInt32) in the range 0–1999 became smaller than with ZSTD alone because T64 cut off the unused upper bits. Choosing a codec is not a rule but a measurement.

The type comes before the codec. If you keep status, which has only four values, as String, it is 10MB before compression and about 1.07MB after. LowCardinality(String) writes each value once in a dictionary and keeps only a small number per row, so it shrinks to 1MB even before compression and about 0.30MB after. Enum8 is similar since it is 1 byte per row, but the list of values is fixed in the table definition, so a new value needs an ALTER. The documentation (LowCardinality) says to consider LowCardinality before Enum for strings, and that it is efficient when there are fewer than 10,000 distinct values and can actually get worse above 100,000.

Nullable is not free. Nullable(UInt16) places one more 1-byte-per-row null map next to the value file. So the size before compression is 3 bytes per row, not 2. WHERE err IS NULL reads only the null map (1 byte per row), and sum(err) reads both files. The meaning differs too — avg() computes the mean leaving out NULL but includes 0. The UInt16 column with NULL filled with 0 was mostly 0, so this server stored it with sparse serialization (a method that writes only the non-default rows), and err_zero.sparse.idx was visible in the substreams of system.parts_columns.

What it looks like in the field

The most common is a table brought over as "String for now, Nullable for now". If you carry over the source database's schema as it is, every column becomes Nullable, and columns like status, country and plan become String. In this lab, the post-compression sizes of such a table (plain) and a table with types and codecs chosen (tuned) were about 18.5MB versus 7.7MB. Polishing the schema comes before fixing queries, and its effect applies to everything at once.

The second is changing the codec of a table in production. ALTER TABLE ... MODIFY COLUMN ts CODEC(...) changes only metadata. Parts already written keep the old codec, and the new codec applies from new parts and from parts rewritten by merges. In the lab, the size did not change by a single byte right after the ALTER, and it shrank only after rewriting the parts with OPTIMIZE ... FINAL. By contrast, MODIFY COLUMN host LowCardinality(String), which changes the type, is a mutation that rewrites the parts, so it is heavy on a big table (mutations are covered in the last module).

The third is automatic selection. The documentation says that if you turn on the table setting enable_adaptive_codec_selection, then for columns with the default codec it picks, at merge time, the codec that gives the smallest result for each block. On this Pod's 26.8 server, the default was 0 (off). Whether you turn it on or off, the basis for judging is the same — data_compressed_bytes in system.columns.

What you will do in the next lab

You insert 1 million rows into codecs.plain, with default types and default codecs, and fix it to one part. You put the same status into three columns, String, LowCardinality and Enum8, the same timestamp into four columns, LZ4, ZSTD, Delta and DoubleDelta, and a counter, latency and CPU into pairs of "ZSTD only" and "specialized codec + ZSTD", compare the post-compression bytes, and find the column where the specialized codec actually lost. You confirm Nullable's null map by the bytes read, measure the total size of a table that gathers the winning choices, and finally record when the size changes if you change the codec of a production table with ALTER.