Using ClickHouse for High‑Velocity IoT Telemetry: Schema Design and Query Optimization

Hook: Why IoT telemetry teams are choosing ClickHouse — and what trips them up

You need sub-second analytics on millions of device events per second, low operational cost, and strong retention guarantees — but you still face data silos, exploding cardinality, and hard-to-predict query latency. In 2026, many engineering teams pick ClickHouse for high-velocity IoT telemetry because it delivers columnar speed, affordable compression, and a rich ingestion ecosystem. Yet success depends on schema design, indexes, and query patterns tuned specifically for time-window analytics.

Why ClickHouse for IoT in 2026

ClickHouse’s adoption accelerated through 2025 — the company’s late-2025 funding round (a high-profile indicator of momentum across OLAP and real-time analytics) brought extra ecosystem investment in connectors, managed services, and server-side tooling. For IoT teams, ClickHouse offers three practical benefits:

• High ingestion throughput via Kafka/MQTT engines, fast local merges, and compact codecs.
• Cost-effective storage with columnar compression and TTL-based downsampling/movement to colder tiers.
• Flexible real-time aggregation using materialized views and AggregatingMergeTree patterns for immediate windowed analytics.

Core schema design principles for IoT telemetry

The single most important rule: design the physical layout (ORDER BY, partitioning, codecs) to match your most common time-window queries. If your queries predominantly filter by recent time ranges and device identity, your schema must make that fast and cheap.

1) Choose the right primary key (ORDER BY)

In ClickHouse, ORDER BY is how data is physically sorted and determines how efficiently range scans and merges work. For telemetry, typical ORDER BY patterns are:

• ORDER BY (device_id, ts) — excellent for per-device timelines, latest-value queries, and small device-specific windows.
• ORDER BY (toDate(ts), device_id, ts) — balances fast time-range drops (by partition) and device scans but increases partition churn if devices report frequently across date boundaries.
• ORDER BY (ts, device_id) — good if most queries are pure time-series aggregations across devices in windows (e.g., global 1-min rollups).

Pick the ORDER BY that minimizes scanned data for your hottest queries. If you have mixed patterns, you can use multiple materialized views with different ORDER BYs to pre-shape data for each pattern.

2) Narrow, typed columns and LowCardinality

IoT rows should be compact. Use explicit column types (UInt32/64 for device IDs where possible; Float32 for sensor values). Convert repeated tags or small enumerations to LowCardinality(String) to compress dictionary-encoded values and speed group-by on those keys.

3) Avoid wide JSON blobs for hot queries

Store raw JSON payloads in a recycle column only when needed for re-processing. Promote frequently queried fields to native columns so queries can use vectorized engines and compression effectively.

MergeTree variants and engine choices

ClickHouse offers several MergeTree engines tuned to different dedupe, aggregation, and update patterns. Pick an engine that matches your ingestion guarantees and retention semantics.

Common engine choices

• MergeTree — the base engine for append-only telemetry where you never mutate rows.
• ReplacingMergeTree — use when you need idempotent ingestion and want later events to override earlier duplicates using a version column (or timestamp).
• CollapsingMergeTree — useful for explicit insert/cancel patterns, e.g., device heartbeats and deletions via sign columns.
• AggregatingMergeTree / SummingMergeTree — ideal for storing pre-aggregated windows (minute/hour) produced by materialized views.

Example: high-throughput telemetry table

CREATE TABLE telemetry (
  ts DateTime64(3),
  device_id UInt64,
  sensor_type LowCardinality(String),
  value Float32,
  raw_payload String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(ts)
ORDER BY (device_id, ts)
SETTINGS index_granularity = 8192;

Partitioning and TTL strategies for time-window analytics

Partitions control how ClickHouse prunes whole data blocks for queries and how you drop data cheaply. For IoT, partition by time granularity that matches deletion and compaction needs: daily or monthly partitions are common. Combine partitions with TTLs to implement retention and automated downsampling.

Retention and downsampling patterns

• Hot tier — keep raw per-event data for recent N days (e.g., 7–30 days) for troubleshooting and anomaly detection.
• Warm tier — downsample older data into minute/hour aggregates and optionally move to cheaper volumes or disks.
• Cold tier — delete or archive raw events older than retention window.

Using TTL to move and downsample

TTL lets you define rules that either delete old rows or push them to disk/volume. A common pattern is to keep full resolution for 30 days and convert to 1-minute aggregates for 1–12 months.

ALTER TABLE telemetry
MODIFY COLUMN ts DateTime64(3) TTL
  ts + INTERVAL 30 DAY TO DISK 'warm',
  ts + INTERVAL 365 DAY DELETE;

-- Or use a materialized view to create downsampled table and TTL raw rows after 30 days

Data skipping indices and cardinality control

ClickHouse has zero-cost default summary indices (min/max per granule) and supports explicit data skipping indices to avoid scanning irrelevant blocks. Use indices that reflect filter predicates used in WHERE clauses.

Index types to consider

• minmax — default; good for numeric/timestamp ranges.
• bloom_filter — great for high-cardinality string lookups (e.g., device serial numbers) where predicate selectivity is high.
• set — efficient when column cardinality is moderate and you test membership against small sets.

Example: bloom filter index on device_serial

CREATE TABLE telemetry (
  ts DateTime64(3),
  device_id UInt64,
  device_serial String,
  value Float32
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(ts)
ORDER BY (device_id, ts)

-- Add data skipping index
ALTER TABLE telemetry
ADD INDEX idx_serial device_serial TYPE bloom_filter(0.01) GRANULARITY 4;

Note: tune GRANULARITY and false-positive rate to balance memory and pruning effectiveness.

Materialized views for real-time aggregation

To deliver low-latency windows (1s/1m/5m) without scanning raw data, use materialized views that populate pre-aggregated tables. This pattern reduces CPU for repetitive analytics and keeps hot queries fast.

Example: per-device 1-minute aggregations using AggregatingMergeTree

CREATE TABLE telemetry_minute
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(minute)
ORDER BY (device_id, minute)
AS SELECT
  toStartOfMinute(ts) AS minute,
  device_id,
  anyHeavy(sensor_type) AS sensor_type,
  avgState(value) AS avg_value_state,
  minState(value) AS min_value_state,
  maxState(value) AS max_value_state
FROM telemetry
GROUP BY minute, device_id;

CREATE MATERIALIZED VIEW mv_telemetry_minute TO telemetry_minute AS
SELECT
  toStartOfMinute(ts) AS minute,
  device_id,
  avgState(value) AS avg_value_state,
  minState(value) AS min_value_state,
  maxState(value) AS max_value_state
FROM telemetry
GROUP BY minute, device_id;

Consumers query telemetry_minute with state functions finalized: SELECT device_id, minute, avgMerge(avg_value_state) as avg FROM telemetry_minute ...

Ingestion patterns, idempotency, and at-least-once guarantees

IoT ingestion often comes through Kafka or MQTT bridges. ClickHouse offers a native Kafka engine and third‑party ingestion tools. The important operational matters are deduplication, ordering assumptions, and backpressure handling.

Idempotent ingestion patterns

• Use message keys where possible and a version or sequence column in the payload.
• Use ReplacingMergeTree with a version/timestamp column to drop duplicates on merges.
• For delete/update patterns, consider CollapsingMergeTree with a sign column.

Example Kafka-to-ClickHouse pipeline

-- Create Kafka engine table
CREATE TABLE kafka_telemetry (
  ts DateTime64(3),
  device_id UInt64,
  value Float32,
  msg_offset UInt64
) ENGINE = Kafka('kafka:9092', 'telemetry', 'group1', 'JSONEachRow');

-- Materialized view to ingest from Kafka into ClickHouse table
CREATE MATERIALIZED VIEW kafka_to_main TO telemetry AS
SELECT * FROM kafka_telemetry;

If your Kafka consumer may re-deliver, use ReplacingMergeTree with msg_offset or version to dedupe.

Query optimization: practical patterns for time-window analytics

Design queries to maximize data skipping and minimize merges. Here are concrete optimizations:

1) Push time filters early

Always filter by ts in the WHERE clause as the first predicate. ClickHouse prunes partitions/granules by time ranges quickly.

2) Match ORDER BY to GROUP BY when possible

If the query groups by device_id and ts window, having ORDER BY (device_id, ts) helps reduce intermediate work and memory usage during aggregation.

3) Use arrayJoin sparingly and pre-explode arrays in materialized views

arrayJoin can inflate row counts dramatically. When you need exploded tags for downstream queries, create a dedicated exploded table at ingest time or use edge pre-processing in micro-regions to avoid shipping wide payloads centrally.

4) Limit result cardinality with sampling or approximate functions

For exploratory dashboards, use approximateCountDistinct or sampling for fast results. ClickHouse supports sampling via a SAMPLE BY clause if you define a sampling key.

5) Use appropriate compression codecs

For hot ingestion, LZ4 provides low CPU latency. For colder tiers or archival data, ZSTD at higher levels yields much better storage ratios. For monotonic integer telemetry (timestamps, sequence numbers) use delta/double-delta codecs to improve compression — techniques that also appear in AI training pipelines that minimize memory footprint designs.

Operational considerations and monitoring

High-velocity telemetry workloads strain merges, background merges, and storage IO. Monitor these metrics closely:

• Background merges (number and duration)
• Part sizes per partition / granule distribution
• RAM usage and query memory overflows
• Disk throughput and latency, especially for compaction

Tune index_granularity to control part sizes; smaller granularity gives better pruning but more metadata overhead.

Advanced strategies & future-proofing (2026 and beyond)

As IoT ecosystems move to hybrid edge-cloud patterns in 2026, plan for multi-tier ingestion, short-term edge aggregation, and cloud ClickHouse for central analytics. Recent ecosystem trends include better managed ClickHouse cloud offerings, more robust streaming connectors, and tighter integration with time-series stream processors.

Edge-first ingestion

Implement lightweight edge aggregators that emit minute-level summaries to reduce load on central ClickHouse clusters. Keep raw events for a short window on edge nodes and ship only deltas for longer retention.

Multi-table strategies

Use raw_event tables for troubleshooting, aggregated tables for dashboards, and an events-index table for fast lookups. That separation keeps queries efficient and teams focused on the right data granularity. For geo-distributed setups, pair local pre-aggregation with edge personalization and regional aggregates to keep latency low for dashboards.

Cross-cluster and geo-distributed analytics

Distributed tables with sharding and replication help scale ingestion regionally. Use asynchronous replication for cross-region copying and local aggregates to keep latency low for regional dashboards. For very latency-sensitive use cases, consider patterns from edge-first live production to minimize cross-region hops.

Actionable checklist: Implement in your next sprint

1. Audit your hottest queries; log time filters, group keys, and cardinality.
2. Choose ORDER BY that optimizes those queries (device-first or time-first).
3. Move frequently queried JSON fields to native columns; use LowCardinality for enums.
4. Set daily/monthly partitions; implement TTL for hot/warm/cold tiers.
5. Create materialized views for 1s/1m aggregates using AggregatingMergeTree.
6. Use ReplacingMergeTree or dedupe logic on ingestion for idempotency.
7. Add data skipping indices (bloom_filter/set) for high-selectivity filters.
8. Monitor merges, query memory, and disk IO; tune index_granularity and codecs.

"ClickHouse is now a mainstream choice for high-scale telemetry, but the difference between good and great implementations is schema and query design tailored to time-window workloads." — Practical takeaway for 2026

Closing: Where to start and next steps

In 2026, ClickHouse provides the performance and cost profile IoT teams need — but only when you align physical schema with query patterns, enforce retention and downsampling early, and use materialized views to pre-aggregate hot windows. Start by profiling your current queries, implement an ORDER BY and partitioning plan that matches them, and add a materialized view for your top dashboard.

If you want a practical starter implementation, here are three next steps you can take today:

• Create a small proof-of-concept: ingest a 24-hour packet via Kafka into ClickHouse with ORDER BY (device_id, ts).
• Build a minute-level materialized view and compare query latencies to raw scans.
• Implement TTL to downsample after 30 days and measure storage savings and query performance impact.

Call to action

Ready to reduce telemetry query latency and storage cost? Start a targeted POC using the patterns above, or get a schema review from our engineering team to map your workloads to an optimal ClickHouse design. Contact us for a tailored audit and a recommended migration plan tuned to your IoT scale and SLAs.

Related Reading

• ClickHouse for Scraped Data: Architecture and Best Practices
• Micro-Regions & the New Economics of Edge-First Hosting in 2026
• Deploying Offline-First Field Apps on Free Edge Nodes — 2026 Strategies
• Edge Personalization in Local Platforms (2026)
• How to Use Points and Miles to Visit 2026’s Hottest Cities
• Model Hallucination Taxonomy and Automated Tests: A Practitioner’s Guide
• Deploying Blockchain Nodes on AWS European Sovereign Cloud: A Practical Guide
• Office Gym on a Budget: Adjustable Dumbbells vs. Full Equipment — A Buyer’s Guide
• From Box Office Booms to Hotel Prices: How Film Hits Drive Local Travel Costs