IoT deployments produce time-series data at a rate that quickly overwhelms traditional databases. A fleet of 10,000 devices reporting every 10 seconds generates 86.4 million readings per day. At 100,000 devices, that's 864 million. At a million devices, you are ingesting billions of rows daily, and you still need to answer questions about current device state, anomalies in the last hour, and fleet-wide trends over the past 90 days, all in under a second.
ClickHouse® handles this workload because it was designed for exactly this pattern: high-throughput append-only writes, columnar storage that compresses time-series data efficiently, and vectorized execution that processes billions of rows to answer aggregation queries fast.
This post covers the device telemetry schema, time-series aggregation patterns, anomaly detection, fleet analytics, data lifecycle management, and the materialized view patterns that keep IoT dashboards fast at scale.
Device telemetry schema
IoT data is append-only by nature. Device readings don't change after they are recorded. The MergeTree engine, sorted by device and time, is the right foundation:
CREATE TABLE device_readings
(
event_time DateTime64(3),
device_id String,
fleet_id LowCardinality(String),
device_type LowCardinality(String), -- 'temperature_sensor', 'pressure_gauge', 'flow_meter'
location_id LowCardinality(String),
metric_name LowCardinality(String), -- 'temperature', 'pressure', 'flow_rate', 'battery_pct'
metric_value Float64,
metric_unit LowCardinality(String), -- 'celsius', 'bar', 'l/min', 'pct'
quality LowCardinality(String), -- 'good', 'uncertain', 'bad'
latitude Nullable(Float32),
longitude Nullable(Float32),
firmware_ver LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (fleet_id, device_type, device_id, metric_name, event_time);
The sort key puts fleet_id and device_type first because most operational queries filter by fleet or device type before device ID. For queries that filter by device ID directly (current state of a specific device), device_id is third in the key so ClickHouse can skip non-matching granules efficiently. LowCardinality on fleet_id, device_type, location_id, metric_name, metric_unit, quality, and firmware_ver keeps GROUP BY on these columns fast. According to ClickHouse's official documentation, LowCardinality provides the most benefit for columns with fewer than 10,000 distinct values, which covers all of these.
Current device state
The most common dashboard query for IoT: what is the current reading for each device in a fleet?
SELECT
device_id,
metric_name,
argMax(metric_value, event_time) AS current_value,
argMax(metric_unit, event_time) AS unit,
argMax(quality, event_time) AS quality,
max(event_time) AS last_seen,
dateDiff('second', max(event_time), now()) AS seconds_since_last
FROM device_readings
WHERE fleet_id = {{ String(fleet_id, required=True) }}
AND event_time >= now() - INTERVAL 1 HOUR
GROUP BY device_id, metric_name
ORDER BY seconds_since_last DESC;
The argMax pattern retrieves the latest value per device-metric pair without needing ReplacingMergeTree or background deduplication. Devices that haven't reported in the last hour, high seconds_since_last values, appear at the top of the result, making offline device detection trivial.
Time-series aggregation and downsampling
Raw readings at 10-second intervals are expensive to query over long time ranges. A materialized view pre-aggregates to 1-minute buckets using AggregatingMergeTree, reducing the data volume for dashboard queries by 6x at the 1-minute level and 360x at the 1-hour level:
CREATE TABLE device_readings_1m
(
minute DateTime,
device_id String,
fleet_id LowCardinality(String),
metric_name LowCardinality(String),
avg_value_state AggregateFunction(avg, Float64),
min_value_state AggregateFunction(min, Float64),
max_value_state AggregateFunction(max, Float64),
p95_value_state AggregateFunction(quantile(0.95), Float64),
reading_count UInt32
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(minute)
ORDER BY (fleet_id, device_id, metric_name, minute);
CREATE MATERIALIZED VIEW device_readings_1m_mv TO device_readings_1m AS
SELECT
toStartOfMinute(event_time) AS minute,
device_id,
fleet_id,
metric_name,
avgState(metric_value) AS avg_value_state,
minState(metric_value) AS min_value_state,
maxState(metric_value) AS max_value_state,
quantileState(0.95)(metric_value) AS p95_value_state,
count() AS reading_count
FROM device_readings
WHERE quality = 'good'
GROUP BY minute, device_id, fleet_id, metric_name;
Querying the pre-aggregated table for trend analysis over 30 days:
SELECT
toStartOfHour(minute) AS hour,
avgMerge(avg_value_state) AS avg_value,
minMerge(min_value_state) AS min_value,
maxMerge(max_value_state) AS max_value,
quantileMerge(0.95)(p95_value_state) AS p95_value,
sum(reading_count) AS total_readings
FROM device_readings_1m
WHERE fleet_id = {{ String(fleet_id, required=True) }}
AND device_id = {{ String(device_id, required=True) }}
AND metric_name = {{ String(metric_name, 'temperature') }}
AND minute >= now() - INTERVAL 30 DAY
GROUP BY hour
ORDER BY hour;
This query scans the 1-minute rollup rather than 30 days of raw 10-second readings. The data volume is 6x smaller and the query returns proportionally faster.
Anomaly detection on sensor data
Z-score against a device's own 30-day baseline identifies sensor readings that are statistically unusual, distinguishing real anomalies from expected variance:
WITH device_baselines AS (
SELECT
device_id,
metric_name,
avgMerge(avg_value_state) AS baseline_avg,
stddevPopMerge(stddev_state) AS baseline_stddev
FROM (
SELECT
device_id,
metric_name,
avgState(metric_value) AS avg_value_state,
stddevPopState(metric_value) AS stddev_state
FROM device_readings
WHERE quality = 'good'
AND event_time >= today() - 30
GROUP BY device_id, metric_name
)
GROUP BY device_id, metric_name
)
SELECT
r.device_id,
r.fleet_id,
r.metric_name,
r.metric_value,
r.event_time,
b.baseline_avg,
round((r.metric_value - b.baseline_avg) / nullIf(b.baseline_stddev, 0), 2) AS z_score
FROM device_readings AS r
INNER JOIN device_baselines AS b USING (device_id, metric_name)
WHERE r.event_time >= now() - INTERVAL 15 MINUTE
AND r.quality = 'good'
AND abs((r.metric_value - b.baseline_avg) / nullIf(b.baseline_stddev, 0)) > 3
ORDER BY abs(z_score) DESC
LIMIT 100;
Fleet-wide operational analytics
For operations teams monitoring a whole fleet in real time:
SELECT
toStartOfHour(event_time) AS hour,
fleet_id,
device_type,
count() AS total_readings,
uniq(device_id) AS active_devices,
countIf(quality = 'bad') AS bad_quality_readings,
round(
countIf(quality = 'bad') / count() * 100, 2
) AS bad_quality_pct,
countIf(
dateDiff('second', event_time, now()) > 60
) AS stale_readings
FROM device_readings
WHERE event_time >= now() - INTERVAL 24 HOUR
GROUP BY hour, fleet_id, device_type
ORDER BY hour DESC, bad_quality_pct DESC;
High bad_quality_pct by device_type points to sensor calibration issues. High stale_readings by fleet_id points to connectivity problems. Both are operationally actionable signals that the query surfaces in milliseconds across millions of readings.
Data lifecycle management with TTL
IoT data has different value at different ages. Raw 10-second readings matter for troubleshooting recent incidents. Older data is useful only for trend analysis, which the 1-minute rollup table already handles. TTL rules automatically tier data to cheaper storage as it ages:
ALTER TABLE device_readings MODIFY TTL
event_time + INTERVAL 7 DAY TO VOLUME 'warm', -- SSD-backed warm tier
event_time + INTERVAL 90 DAY TO VOLUME 'cold', -- S3-backed cold tier
event_time + INTERVAL 2 YEAR DELETE; -- delete raw after 2 years
The 1-minute rollup table retains indefinitely since it is already compact. Raw readings older than 90 days move to object storage where the cost per GB is a fraction of SSD-backed storage. Queries against the 1-minute rollup table for historical trend analysis are unaffected by the TTL policy on the raw table.
Geospatial fleet tracking
For mobile IoT deployments (vehicles, shipping containers, field equipment), tracking last known position and computing distance-based metrics:
SELECT
device_id,
fleet_id,
argMax(latitude, event_time) AS last_lat,
argMax(longitude, event_time) AS last_lon,
max(event_time) AS last_seen,
dateDiff('minute', max(event_time), now()) AS minutes_offline,
count() AS readings_24h
FROM device_readings
WHERE fleet_id = {{ String(fleet_id, required=True) }}
AND latitude IS NOT NULL
AND event_time >= now() - INTERVAL 24 HOUR
GROUP BY device_id, fleet_id
ORDER BY minutes_offline DESC;
For geofence alerting, devices that have transmitted a position outside an expected boundary:
SELECT
device_id,
argMax(latitude, event_time) AS last_lat,
argMax(longitude, event_time) AS last_lon,
max(event_time) AS last_seen
FROM device_readings
WHERE fleet_id = {{ String(fleet_id, required=True) }}
AND latitude IS NOT NULL
AND event_time >= now() - INTERVAL 15 MINUTE
GROUP BY device_id
HAVING last_lat NOT BETWEEN {{ Float32(min_lat, 40.0) }} AND {{ Float32(max_lat, 42.0) }}
OR last_lon NOT BETWEEN {{ Float32(min_lon, -74.0) }} AND {{ Float32(max_lon, -72.0) }};
The parameterized bounding box means one endpoint serves multiple geofenced zones, with the zone coordinates passed as query parameters. At query time, ClickHouse evaluates this against the argMax result for each device in under 100ms even across thousands of devices.
Tinybird for IoT
IoT analytics has the same two problems at every scale: getting data in fast enough and querying it fast enough. The ingestion problem and the query problem usually require separate infrastructure choices, which is why IoT analytics architectures often end up with MQTT brokers, Kafka topics, stream processors, and analytical databases all requiring separate operation.
For real-time data ingestion at IoT scale, Tinybird's Events API accepts batched NDJSON over HTTP, making readings queryable within seconds of device transmission. For higher-volume deployments, the Kafka connector consumes from your existing broker topics. Either path writes directly to Tinybird datasources without a separate stream processing layer.
The clickhouse create materialized view example pattern applies directly: define the 1-minute rollup as a materialized view in Tinybird, and the pre-aggregation happens automatically as data arrives. Your dashboard queries read the rollup. Backfilling materialized views from historical raw data, when you add a new rollup after the raw table already has months of data, is a common operational challenge that Tinybird handles with built-in tooling.
The anomaly detection query becomes a Tinybird Pipe published as an HTTP endpoint. Your alerting system calls it every minute, gets the list of devices with Z-score above 3, and fires alerts. The fleet dashboard query becomes another Pipe. Real-time data visualization tools like Grafana, Metabase, or your own frontend call these endpoints directly.
For building real-time apps on top of IoT data, where device owners need to see their own equipment's performance, each endpoint enforces fleet_id or device_id parameter scoping. One endpoint serves all tenants, with the SQL enforcing data isolation. No application-level row filtering required.
Tinybird is SOC 2 Type II certified. Resend, processing 100TB per month on Tinybird, measured 62ms p90 query latency in production without caching. Your fleet dashboard, current-state endpoint, and anomaly detector all run inside that latency envelope. Tinybird handles cluster scaling, replication, TTL execution, and rollup maintenance. Your team writes SQL and ships product features.