ClickHouse for Time-Series Data: Optimizing Schema Design and Compression for IoT Telemetry

Managing massive volumes of IoT telemetry requires a database engine that can handle high-throughput ingestion while maintaining low-latency reads. ClickHouse has emerged as a leading columnar database for this purpose, but its performance is heavily dependent on schema design and compression strategies. In this post, we explore how to structure your ClickHouse tables to maximize efficiency for time-series data.

Choosing the Right Table Engine and Ordering

The foundation of an efficient time-series workload in ClickHouse is the MergeTree family of table engines. For most IoT scenarios, ReplicatedMergeTree or ReplicatedCollapsingMergeTree (if you need mutable data via versioning) are the go-to choices. The critical component here is the ORDER BY clause. Unlike traditional databases, ClickHouse physically sorts data on disk based on the primary key. For telemetry, you want to order by the device identifier and the timestamp. This ensures that data for a specific device is contiguous, allowing for efficient range scans.

CREATE TABLE iot_telemetry (
    device_id UUID,
    event_time DateTime,
    sensor_type String,
    temperature Float64,
    humidity Float64,
    battery_level UInt8
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/iot_telemetry', '{replica}')
ORDER BY (device_id, event_time);

Notice the use of device_id and event_time in the order key. If you query for all readings from device_123 between 2 PM and 3 PM, ClickHouse can skip most of the data blocks on disk, drastically reducing I/O.

Leveraging Compression and Granularity

Columnar storage allows ClickHouse to apply different compression codecs to different columns. High-cardinality columns like sensor_type or string IDs benefit from LZ4 or ZSTD compression, while low-cardinality columns like battery_level (which likely repeats values) might benefit from dictionary encoding or simpler codecs. Additionally, tuning the granularity is crucial. The default granule size is 8192 rows. For high-frequency telemetry where you often query small time windows, a smaller granule size (e.g., 4096) can improve read performance at the cost of slightly higher metadata overhead.

To explicitly set compression for specific columns, you can use the CODEC parameter in the column definition:

ALTER TABLE iot_telemetry
MODIFY COLUMN sensor_type CODEC(LZ4);

ALTER TABLE iot_telemetry
MODIFY COLUMN temperature CODEC(ZSTD);

ZSTD typically offers better compression ratios than LZ4 for numeric data but is slower. For real-time dashboards where speed is paramount, LZ4 is often preferred for frequently accessed columns.

Partitioning for Data Lifecycle Management

Partitioning by time is essential for IoT data to facilitate TTL (Time-To-Live) policies and efficient data pruning. By partitioning on event_time, you can easily delete old data or move it to cold storage without scanning the entire table. A common pattern is to partition by month or day, depending on the data volume.

ALTER TABLE iot_telemetry
PARTITION BY toMonth(event_time);

ALTER TABLE iot_telemetry
MODIFY TTL event_time + INTERVAL 90 DAY;

This setup ensures that data older than 90 days is automatically deleted, keeping your hot storage costs manageable. Combining this with the ORDER BY key creates a highly efficient physical layout where data is both sorted for fast lookups and partitioned for easy management.

Conclusion

Optimizing ClickHouse for IoT telemetry is not just about choosing the right database; it's about aligning your schema design with your query patterns. By carefully selecting the ORDER BY key, tuning compression codecs, and implementing strategic partitioning, you can build a robust system capable of handling millions of data points per second with sub-second query times. Start with these foundational principles, and monitor your query logs to identify further optimization opportunities.