Sort keys and projections that make the scan small
GrafanaLinuxDashboard queries went from scanning most of each table to reading a small fraction, and typical response times improved by roughly an order of magnitude. Storage footprint also dropped meaningfully f…
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
- 1Profiled the dashboard query mix and measured granules read versus granules containing matches
- 2Reordered sort keys to lead with the columns queries actually filter on, restoring index skipping
- 3Chose partition granularity that bounds part counts while still enabling partition pruning on time ranges
- 4Applied per-column codecs matched to measured data characteristics for time series and low-cardinality columns
- 5Introduced materialized views and aggregate tables for the recurring dashboard rollups
- 6Defined TTL policies to move aged data to cheaper storage and expire it on a documented schedule
Outcome
Dashboard queries went from scanning most of each table to reading a small fraction, and typical response times improved by roughly an order of magnitude. Storage footprint also dropped meaningfully from the codec and TTL changes.
Technologies
GrafanaLinuxRelated Use Cases
ClickHouse Cluster and Storage Assessment
Assessment of shard and replica topology, storage tiering, query concurrency limits, and merge behavior ahead of a significant data volume increase.
ClickHouse Too Many Parts Remediation
Resolving ingestion failures where frequent small inserts produced parts faster than background merges could retire them, tripping the parts limit.
Need ClickHouse Architecture Guidance?
AceMQ's senior ClickHouse engineers have handled this exact type of engagement before. Whether you need architectural guidance, hands-on remediation, or an ongoing managed partnership, we're ready to help.