Boost ClickHouse Query Performance Using LowCardinality Columns

  

In large-scale analytics, repeated string values (like country names, user types, or statuses) appear in millions of rows. Storing and querying such repeated values as raw strings is inefficient. ClickHouse offers the LowCardinality type to optimize performance in such cases through dictionary encoding.

 

What is LowCardinality(T)?

LowCardinality is a wrapper around existing data types like String, Int, Date, etc. It stores distinct values once in a dictionary and replaces actual data with integer references (indexes to the dictionary). This can result in:

 

·      Smaller disk usage

·      Faster filtering, joins, and aggregations

 

Syntax

LowCardinality(data_type)

   

Following are the supported types for LowCardinality.

·      String, FixedString

·      Date, DateTime

·      All numeric types except Decimal

 

Let’s take a real world example and try to understand this. Suppose you are storing 1 million user records in ClickHouse, each with a country field. There are only 200 distinct countries worldwide, and your data includes country values like "USA", "India", "Germany", etc.

 

CREATE TABLE demo_db.users (
    user_id UInt32,
    country LowCardinality(String)
) ENGINE = MergeTree()
ORDER BY user_id;

   

Above snippet tells ClickHouse to:

·      Store user_id as-is.

·      Store country using dictionary encoding via LowCardinality(String).

 

Without LowCardinality: Raw String Storage

·      Each country string (say "India") appears repeatedly across rows.

·      "India" = 5 characters = ~5 bytes per row.

·      With 1 million users, 5 bytes × 1,000,000 = ~5 MB just for "India" (even more with variable length).

·      Multiply that across other countries, storage cost grows linearly.

 

With LowCardinality: Dictionary Encoding

ClickHouse builds a dictionary like below.

 

·      0 for "USA"

·      1 for "India"

·      2 for "Germany"

·      ...

·      199 for "Iceland"

 

Instead of storing full strings in each row, it stores an integer index (1 byte or 2 bytes per row depending on size of dictionary). With this the storage is

 

1M rows × 1 byte per encoded index = ~1 MB + (Some internal overhead).

 

Follow below step by step procedure to understand this better with real time example.

 

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.users (
    user_id UInt32,
    country LowCardinality(String)
) ENGINE = MergeTree()
ORDER BY user_id;

INSERT INTO demo_db.users VALUES
    (1, 'USA'),
    (2, 'India'),
    (3, 'USA'),
    (4, 'Germany'),
    (5, 'India');

krishna :) SELECT * FROM demo_db.users;

SELECT *
FROM demo_db.users

Query id: 77f37210-a58d-4c62-a38c-6579b86ab049

   ┌─user_id─┬─country─┐
1. │       1 │ USA     │
2. │       2 │ India   │
3. │       3 │ USA     │
4. │       4 │ Germany │
5. │       5 │ India   │
   └─────────┴─────────┘

5 rows in set. Elapsed: 0.003 sec.

   

Use LowCardinality with filters, joins, or groupings on string columns to drastically cut query time, but only if the number of unique values is reasonably low.

  

Previous                                                    Next                                                    Home