Sort keys and projections that make the scan small

Overview

An industrial IoT operator had ClickHouse tables that performed well when created and degraded steadily as sensor data accumulated. Dashboard queries were reading most of each table because the sort key did not match how the data was filtered. AceMQ redesigned the schema around the actual query mix.

Challenge

ClickHouse performance is largely determined by how much data a query can skip, which comes down to sort key order, partitioning, and index granularity. The existing tables had been created with a sort key that put a high-cardinality identifier first, so the sparse primary index provided almost no skipping for the time-range filters every dashboard applied.

Environment

On-premises ClickHouse cluster storing multi-year sensor telemetry for plant monitoring dashboards.

Approach

AceMQ profiled the real query mix and measured, per query, how many granules were being read versus how many contained matching rows. That gap drove the sort key and partitioning redesign. Codec and compression choices were then made per column based on measured data characteristics rather than defaults.

Solution

• Profiled the dashboard query mix and measured granules read versus granules containing matches
• Reordered sort keys to lead with the columns queries actually filter on, restoring index skipping
• Chose partition granularity that bounds part counts while still enabling partition pruning on time ranges
• Applied per-column codecs matched to measured data characteristics for time series and low-cardinality columns
• Introduced materialized views and aggregate tables for the recurring dashboard rollups
• Defined TTL policies to move aged data to cheaper storage and expire it on a documented schedule

Outcome

Technologies