Saving space in ClickHouse using low cardinality types

Low Cardinality Types

ClickHouse allows us to define certain columns as LowCardinality(Data_type), these columns apply dictionary encoding to the data. This can massively reduce the space occupied by categorical columns on disk. Instead of storing the actual data it replaces it with short integer codes, integer more often than not require less space and compress way better than strings. You will be able to get the data, or join with any other table using the underlying value without making any changes.

This can significantly speed up our queries, with LowCardinality, the query engine can often perform operations like filtering, grouping, and counting directly on the small integer keys instead of the full string values. This reduces the amount of data read from disk and the CPU work needed to process it.

Low cardinality types are more versatile than enums because you do not need to pre define values and any new value is automatically handled. According to the docs low cardinality types more often than not are more efficient when there are fewer than ~10,000 unique values; beyond that, use the ordinary data type.

You can define the following low cardinality types in your table like this:

CREATE TABLE category_data
(
    `product_type` LowCardinality(String),
    `product_sku_id` LowCardinality(FixedString(16))
)
ENGINE = MergeTree()
ORDER BY product_sku_id

You can also use LowCardinality types for numbers (not decimal) and date also, but for data types with fixed size 8 bytes or less you will have to enable allow_suspicious_low_cardinality_types setting. For small values LowCardinality is usually inefficient and not worth it.

There is some added cost to maintain low cardinality types - ClickHouse needs to maintain a dictionary to map the values, and bring this in memory to encode and decode values. There will also be a similar overhead when joining a table on low cardinality types.

Checking how much space we can save when using low cardinality types

We will do a comparison for 10,100, 1000, 10000 unique string values in 1 million, 10 million and 100 million rows. All values will have a uniform distribution.

Sample table:

CREATE TABLE orders
(
    order_id     UInt64,
    amount       Float64,

    status_str   String,          -- 10 unique
    status_lc    LowCardinality(String),

    country_str  String,           -- 100 unique
    country_lc   LowCardinality(String),

    brand_str    String,           -- 1,000 unique
    brand_lc     LowCardinality(String),

    sku_str      String,           -- 10,000 unique
    sku_lc       LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY order_id;

Adding example data to the table:

INSERT INTO orders
SELECT
       order_id, amount,
       status,  status,
       country, country,
       brand,   brand,
       sku,     sku
FROM
(
    SELECT
        number AS order_id,
        round(cityHash64(number, 0) % 1000000 / 100, 2) AS amount,
        concat('st_', lpad(toString(cityHash64(number, 1) % 10), 5, '0')) AS status,
        concat('co_', lpad(toString(cityHash64(number, 2) % 100), 5, '0')) AS country,
        concat('br_', lpad(toString(cityHash64(number, 3) % 1000), 5, '0')) AS brand,
        concat('sk_', lpad(toString(cityHash64(number, 4) % 10000), 5, '0')) AS sku
    FROM numbers(1000000)
);

You can see all the queries on the ClickHouse fiddle.

You can check how much disk space we are saving (compressed bytes)

Compressed size per column

Given the way I am generating example data - all consecutive values are non repeating, in actual data sets that may not be case and that might further help the compression and the columns will occupy even less space. Also these are for tables whose parts have not been merged so the numbers might not be exact.

Also using LowCardinality types can make the queries more efficient and take up less time. Here is another comparison for query speed using where clause and group by on country_str and country_lc - ClickHouse fiddle

Read Query data is not deterministic and can depend on several factors, but we can still see, low cardinality types can improve our query performance. The exact number might not be correct and can vary.

Converting an existing column to Low Cardinality type

Migrating an existing column to LowCardinality is a single ALTER TABLE statement:

ALTER TABLE events
MODIFY COLUMN status LowCardinality(String);

This triggers a background mutation that rewrites the entire data - in large tables this can be a very expensive option - better to schedule it accordingly.