Exploring Schema Optimization and Compression in ClickHouse

To see exactly how much this matters, we built two versions of the same 1,000,000-row event log table: one using ClickHouse’s default types, and one applying Enum8, LowCardinality, specialized codecs, and HyperLogLog. Below are the exact storage numbers, what broke along the way, and the settings that mattered most.

ClickHouse Storage Internals: How Compression Choices Cut Disk Usage by 3 to 5x

ClickHouse’s query speed doesn’t come only from reading data in columns. It comes from how aggressively it compresses those columns once they’re on disk. Pick the wrong data type or skip the right compression codec, and the same table can end up 3 to 5 times larger than it needs to be, with every extra byte adding to the I/O your queries have to push through.

To see exactly where that difference comes from, I built benchmark tables using several data schemas, comparing standard types against optimizations like Enum8, LowCardinality, specialized codecs, arrays, and HyperLogLog (AggregateFunction). Below are the practical observations and the exact storage numbers from that comparison.

Similar schema-design tradeoffs show up across most big data consulting engagements, since the right column types and codecs one team picks rarely transfer cleanly to another team’s workload.

Concepts Explored

Practical Hands-on

I created a benchmarking table events_naive to test standard data types, alongside an optimized version events_optimized. I loaded both with exactly 10,00,000 rows of identical simulated event log data.

Here is the unoptimized baseline schema:

CREATE TABLE default.events_naive
(
    event_id UInt64,
    status String,         -- Repeated values ('success', 'failed', 'pending')
    city String,           -- Semi-repeated values (approx 50 unique cities)
    metric_value Float64,  -- Slow-changing time-series float values
    user_tags Array(String)
)
ENGINE = MergeTree
ORDER BY event_id;

And here is the heavily optimized schema applying specialized types and compression structures:

CREATE TABLE default.events_optimized
(
    event_id UInt64 CODEC(DoubleDelta, LZ4),
    status Enum8('success' = 1, 'failed' = 2, 'pending' = 3),
    city LowCardinality(String),
    metric_value Float64 CODEC(Gorilla, ZSTD),
    user_tags Array(String)
)
ENGINE = MergeTree
ORDER BY event_id;

Storage Footprint Comparison (Visual Proof)

After running the batch inserts, I queried the system.columns table to see the precise number of bytes each column consumed on the disk.

SELECT 
    column, 
    type, 
    data_compressed_bytes, 
    data_uncompressed_bytes,
    round(data_uncompressed_bytes / data_compressed_bytes, 2) AS compression_ratio
FROM system.columns 
WHERE table IN ('events_naive', 'events_optimized')
ORDER BY column ASC;

Problems and Observations

The LowCardinality Threshold Trap

While testing LowCardinality(String) on the city column, it worked beautifully because there were only around 50 unique cities. However, when I tried applying LowCardinality to a high-cardinality column like user_id (with over 800,000 unique values), the table size actually increased, and search performance degraded. ClickHouse had to build a massive, inefficient dictionary overhead file in the background.

The Mechanics of Array Columns

When exploring the data directory under ./data/default/events_optimized/, I noticed that for the user_tags column, ClickHouse generated two separate physical files: user_tags.bin (storing raw strings flattened out) and a user_tags.mrk2 / sizes file. This proved why nested or large array structures can cause heavy disk seeks if arrays are deeply layered.

HyperLogLog (HLL) Cardinality Savings

To count unique users without storing heavy strings, I experimented with the HyperLogLog probabilistic structure:

-- Storing raw strings for unique counting vs HLL state
CREATE TABLE default.hll_test (
    user_set AggregateFunction(uniqCombined(14), String)
) ENGINE = MergeTree ORDER BY tuple();

Check how a similar tiered-storage strategy helped a telecom client cut ClickHouse storage costs by 40% in this case study.

Key Learnings

• Enum8 is for Static Sets, LowCardinality is for Dynamic Sets: Use Enum8 when values are fixed and unlikely to change (e.g., status codes). Use LowCardinality when strings are repeated but can grow over time, keeping unique values well under 10,000.
• Codecs Outperform Plain Compression: Never settle for default LZ4 on sequential keys or time-series metrics. Combining DoubleDelta for monotonically increasing IDs or Gorilla for smooth floating-point values significantly reduces disk usage and accelerates query execution speed.
• Trade Accuracy for Scale with HLL: For massive dashboards where trend analysis matters more than exact counts, storing data as HLL states via AggregateFunction(uniqCombined, ...) saves vast amounts of storage and eliminates heavy in-memory distinct counts.

If you’re still weighing whether ClickHouse is the right engine at all, this comparison of ClickHouse against Snowflake for OLAP workloads breaks down where each platform wins on cost and performance.

Getting This Right at Production Scale

Benchmarking schema choices on a single table is one thing; keeping codec strategy, LowCardinality dictionaries, and merge behavior consistent across a sharded, multi-replica ClickHouse cluster under real write load is another. That’s the gap most teams hit once ClickHouse moves from a proof of concept to a production analytics platform.

Ksolves provides ClickHouse consulting and 24×7 enterprise support: cluster setup, schema and compression audits, performance tuning, and migration support for teams running ClickHouse at scale. If storage costs or query latency are creeping up, explore our ClickHouse support services.