Kanishga Subramani

Posted on Jul 13

Day 76/100 - Working with ClickHouse® LowCardinality and Enum Types

#clickhouse #devops #database #dataengineering

Introduction

Choosing the right data types is one of the simplest yet most effective ways to optimize a ClickHouse® database. While query tuning and hardware upgrades often receive the most attention, selecting appropriate column types can significantly reduce storage requirements, improve compression, accelerate query execution, and lower memory consumption—all without changing application logic.

Two powerful optimization features available in ClickHouse® are LowCardinality and Enum. Although both are designed to store repeated values efficiently, they address different use cases and should not be considered interchangeable.

LowCardinality uses dictionary encoding to optimize columns containing many repeated string values, while Enum stores predefined string values as compact integer codes. Choosing the right one depends on factors such as cardinality, schema flexibility, and how frequently values change over time.

In this article, we'll explore how both data types work, compare their advantages and limitations, examine common use cases, and discuss best practices for designing efficient ClickHouse® schemas.

Why Data Types Matter in ClickHouse®

ClickHouse® is a column-oriented analytical database.

Because each column is stored independently, the selected data type directly influences:

Storage utilization
Compression efficiency
Query execution speed
Memory usage
Network transfer during distributed queries
CPU utilization

Even small schema improvements can have a significant impact.

For columns containing highly repetitive string values, replacing a standard String type with an optimized alternative often improves compression ratios by 3× to 5× while accelerating GROUP BY operations by 2× to 4×.

These improvements become increasingly valuable as datasets grow into hundreds of millions or billions of rows.

Understanding LowCardinality

LowCardinality is a special wrapper type that applies dictionary encoding to columns containing many repeated values.

Instead of storing the complete string in every row, ClickHouse stores each unique value once in a dictionary and replaces repeated occurrences with compact integer identifiers.

For example:

CREATE TABLE events
(
    event_time DateTime,
    country LowCardinality(String),
    browser LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY event_time;

Internally, the dictionary might look like:

Dictionary ID	Value
1	India
2	Germany
3	United States
4	France

Rows store only the integer IDs instead of repeatedly storing full strings.

This significantly reduces storage and improves query performance.

The 10,000-Value Rule

LowCardinality performs best when a data part contains fewer than approximately 10,000 unique values.

When the number of unique values becomes significantly larger, dictionary encoding becomes less effective.

In such situations, ClickHouse may internally fall back to treating the column similarly to a regular String, reducing the performance benefits.

This makes LowCardinality particularly well suited for columns such as:

Country names
Browser names
Device types
Product categories
Application names
Status values

Late Deserialization

One of the major reasons LowCardinality improves query performance is a technique known as late deserialization.

Instead of immediately converting dictionary IDs back into strings, ClickHouse performs filtering, grouping, and aggregation directly on the compact integer values.

Only when returning the final query results does ClickHouse translate the IDs back into human-readable strings.

The workflow looks like this:

Rows
   │
   ▼
Dictionary IDs
   │
   ▼
Filtering / GROUP BY / Aggregation
   │
   ▼
Dictionary Lookup
   │
   ▼
Final Output

Processing integers instead of long strings reduces CPU usage, memory bandwidth, and comparison costs.

How LowCardinality Improves Performance

Dictionary encoding provides several advantages:

Smaller storage footprint
Better compression ratios
Reduced memory consumption
Faster integer comparisons
Improved GROUP BY performance
Lower disk I/O
Better cache efficiency

These improvements become increasingly noticeable as data volume grows.

Understanding Enum Types

Enum stores string values internally as fixed integer codes.

Unlike LowCardinality, every possible value must be defined when the table is created.

ClickHouse provides two Enum types:

Enum8

Uses 1 byte per value.

Supports up to 256 unique string mappings.

Enum16

Uses 2 bytes per value.

Supports up to 65,536 unique string mappings.

Example:

CREATE TABLE orders
(
    order_id UInt64,
    status Enum8(
        'Pending' = 1,
        'Processing' = 2,
        'Completed' = 3,
        'Cancelled' = 4
    )
)
ENGINE = MergeTree
ORDER BY order_id;

Although integer values are stored internally, queries remain easy to read.

SELECT *
FROM orders
WHERE status = 'Completed';

Developers continue working with descriptive strings while ClickHouse stores compact numeric values behind the scenes.

LowCardinality vs Enum

Although both optimize repeated values, they solve different problems.

Feature	LowCardinality	Enum
Dictionary encoding	Yes	No
Stores integer IDs	Yes	Yes
Schema flexibility	High	Low
Easy to add new values	Yes	Requires ALTER
Best suited for	Medium-cardinality strings	Small fixed value sets
Compression	Excellent	Excellent
Query readability	Excellent	Excellent

A simple guideline is:

Use LowCardinality when values change over time.
Use Enum when values are fixed and rarely change.

Choosing Between LowCardinality and Enum

Good candidates for LowCardinality

Countries
Cities
Browsers
Device models
Product categories
Application names
Languages

Good candidates for Enum

Order status
Payment status
User roles
Approval states
Workflow stages
Boolean-like states

Selecting the correct type improves both performance and long-term schema maintainability.

When Not to Use LowCardinality

Although powerful, LowCardinality isn't suitable for every column.

Avoid using it for values that are almost always unique.

Examples include:

UUIDs
Email addresses
Session IDs
Customer IDs
Transaction IDs
Random strings

When nearly every value is unique, maintaining a dictionary introduces overhead without meaningful benefits.

When Not to Use Enum

Enum works best when values remain stable over time.

It's usually a poor choice for:

Product names
Customer names
Tags
Dynamic categories
Free-text labels
User-generated values

If new values are introduced frequently, schema modifications become inconvenient.

In these cases, LowCardinality is generally the better option.

Operational Considerations

One important aspect of Enum involves schema modifications.

Adding a new value to the end of an existing Enum definition is typically a lightweight metadata operation.

For example:

ALTER TABLE orders
MODIFY COLUMN status Enum8(
    'Pending' = 1,
    'Processing' = 2,
    'Completed' = 3,
    'Cancelled' = 4,
    'Refunded' = 5
);

However, modifying or reordering existing integer mappings forces ClickHouse to rewrite underlying data parts.

On large production tables, this can become an expensive operation.

Careful planning of Enum values helps avoid unnecessary maintenance.

Measuring the Impact

The effectiveness of these optimizations depends on your workload.

Useful metrics include:

Column storage size
Compression ratio
Query latency
GROUP BY execution time
Memory consumption
CPU utilization

ClickHouse system tables make it easy to evaluate storage efficiency.

Example:

SELECT
    name,
    type,
    formatReadableSize(data_compressed_bytes) AS compressed_size,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed_size,
    round(
        data_uncompressed_bytes /
        data_compressed_bytes,
        2
    ) AS compression_ratio
FROM system.columns
WHERE table = 'events'
  AND database = 'default';

Always benchmark representative production workloads before applying schema changes broadly.

Best Practices

When designing ClickHouse® schemas:

Use LowCardinality for repeated string values.
Use Enum only for small, stable value sets.
Avoid wrapping high-cardinality columns with LowCardinality.
Benchmark before deploying schema changes.
Monitor storage savings using system.columns.
Review cardinality as datasets evolve.
Plan Enum definitions carefully to minimize future ALTER operations.

Small schema decisions can produce substantial long-term performance improvements.

Common Decision Guide

If your column contains...	Recommended Type
Countries	LowCardinality
Browsers	LowCardinality
Device types	LowCardinality
Product categories	LowCardinality
Order status	Enum
Payment status	Enum
User role	Enum
Workflow state	Enum
UUIDs	String
Email addresses	String

This simple checklist can help when designing new schemas.

Conclusion

Both LowCardinality and Enum are valuable optimization features in ClickHouse®, but they address different challenges. LowCardinality provides flexible dictionary encoding for columns containing repeated string values, reducing storage requirements while accelerating filtering and aggregation. Enum, on the other hand, offers highly compact storage for predefined value sets by mapping strings to fixed integer codes.

Rather than treating these data types as interchangeable, evaluate each column based on its cardinality, expected growth, and schema stability. Applying the appropriate type can significantly improve compression, reduce memory usage, and speed up analytical queries—all without requiring changes to application logic.

As with any optimization, benchmarking representative workloads and monitoring system metrics remain the best way to validate performance improvements and ensure your schema continues to scale efficiently as your data grows.

DEV Community