Working with ClickHouse® LowCardinality and Enum Types

Choosing the right data types is one of the simplest yet most effective ways to optimize a ClickHouse® database. Features like LowCardinality and Enum can significantly reduce storage requirements, improve query performance, and lower memory consumption when used appropriately. While both are designed to handle columns with repeated values efficiently, they solve different problems and should be applied in different scenarios.

In this article, we'll explore how LowCardinality and Enum types work, compare their strengths and limitations, discuss common use cases, and share best practices for selecting the right option for your analytical workloads.

Why Data Types Matter in ClickHouse®

ClickHouse® is a column-oriented database designed for analytical processing. Since each column is stored independently, the choice of data type directly affects:

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

Selecting an appropriate type can produce noticeable improvements without changing application logic or SQL queries. In production environments, switching high-repetition columns from standard String to optimized types frequently yields a 3x to 5x improvement in compression ratios and accelerates GROUP BY execution by 2x to 4x.

Understanding LowCardinality

LowCardinality is a special wrapper type that uses dictionary encoding for columns containing many repeated values.

Instead of storing the complete string repeatedly, ClickHouse® stores each unique value once in a dictionary and references it using compact integer identifiers.

Example:

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

The 10,000-Value Rule & Late Deserialization

LowCardinality works best when a column contains fewer than 10,000 unique values per data part. If the number of unique values in a part exceeds 10,000, ClickHouse automatically falls back to treating it like an ordinary string, erasing its optimization benefits.

The secret to its query speed is a mechanism called late deserialization. When running a query with a WHERE filter or a GROUP BY clause, ClickHouse performs all operations directly on the small integer identifiers. It only looks up the actual string values in the dictionary at the very last moment when rendering the final output, saving massive amounts of CPU cycles and memory bandwidth.

This approach works especially well for values such as:

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

The result is reduced storage usage and faster filtering, grouping, and aggregation operations.

How LowCardinality Improves Performance

Dictionary encoding provides several advantages:

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

These benefits become more noticeable as datasets grow into hundreds of millions or billions of rows.

Understanding Enum Types

Enum stores string values internally as fixed integer codes.

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

ClickHouse provides two variations of this type depending on the size of your dataset:

• Enum8: Uses 1 byte of storage internally, allowing up to 256 unique string mappings.
• Enum16: Uses 2 bytes of storage internally, scaling 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 continue using readable string values.

SELECT *
FROM orders
WHERE status = 'Completed';

This provides compact storage while maintaining developer-friendly SQL.

LowCardinality vs Enum

Although both reduce storage, they are intended for different scenarios.

As a general guideline:

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

Choosing Between LowCardinality and Enum

Consider LowCardinality for:

• Countries
• Cities
• Browsers
• Device models
• Product categories
• Application names

Consider Enum for:

• Order status
• Payment status
• User roles
• Boolean-like state values
• Workflow stages

Choosing the correct type simplifies schema maintenance while improving performance.

When Not to Use LowCardinality

Although powerful, LowCardinality is not appropriate for every column.

Avoid using it for:

• Unique identifiers
• UUIDs
• Email addresses
• Session IDs
• Random strings
• High-cardinality columns

When nearly every value is unique, dictionary encoding provides little benefit and may introduce unnecessary overhead.

When Not to Use Enum

Enum values are fixed by the schema.

If applications frequently introduce new values, repeated schema modifications may become inconvenient.

Examples where Enum is usually a poor choice include:

• Product names
• Customer names
• Tags
• Dynamic categories
• Free-text labels

In these cases, LowCardinality is generally a better option.

Operational Note on ALTERs: Adding a new value to the end of an Enum string map via ALTER TABLE ... MODIFY COLUMN is a fast, metadata-only operation in ClickHouse. However, altering or re-ordering existing integer-to-string mappings forces ClickHouse to completely rewrite the underlying data parts on disk-a highly expensive operation on large tables.

Measuring the Impact

The effectiveness of LowCardinality and Enum depends on workload characteristics.

Useful metrics include:

• Column storage size
• Compression ratio
• Query latency
• GROUP BY performance
• Memory usage
• CPU utilization

To audit your optimizations, you can query the ClickHouse system tables to calculate exact compression savings. Run the following query to evaluate the physical footprint of your optimized columns:

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';

Testing representative production workloads remains the best way to validate performance improvements.

Best Practices

When designing schemas, keep these recommendations in mind:

• Use LowCardinality for repeated string values.
• Use Enum only for small, fixed value sets.
• Avoid wrapping high-cardinality columns with LowCardinality.
• Benchmark schema changes before deploying to production.
• Monitor storage savings using ClickHouse system tables.
• Review cardinality as datasets evolve over time.

Small schema decisions often produce meaningful long-term performance gains.

Final Thoughts

Both LowCardinality and Enum are valuable optimization features in ClickHouse®, but they solve different problems. LowCardinality provides flexible dictionary encoding for repeated string values, while Enum offers compact storage for predefined value sets. Understanding the characteristics of your data and expected schema evolution is essential when choosing between them.

Rather than treating these types as interchangeable, evaluate the cardinality, stability, and growth of each column before deciding. Applying the appropriate type can reduce storage costs, improve query performance, and simplify analytical workloads without requiring application-level changes.

To learn more about operating and optimizing ClickHouse® environments, explore the CHOps feature page at Features.

References

• ClickHouse® - lowcardinality
• ClickHouse® - Enum
• ClickHouse® - Selecting data types