ClickHouse for Telemetry: Real-Time Analytics on Sensor Data

What ClickHouse is, and who this is for

ClickHouse describes itself as a column-oriented SQL database management system for online analytical processing (OLAP), available as open source software and as a cloud service. Its queries are the "scan and aggregate" kind: average supply-air temperature per air handling unit (AHU) per hour over a year, the 95th percentile of chiller kW per site, the count of CO2 readings above 1,000 ppm per zone. It is not a transactional database, and it is not a time-series database in the narrow sense: it is a general analytical engine whose storage happens to fit append-heavy, time-ordered data well.

This guide is for data engineers and developers who already move building management system (BMS), metering or IoT telemetry through a broker and now need analytics over billions of rows. It concentrates on telemetry design decisions. For a head-to-head view against other engines, see ClickHouse vs PostgreSQL, ClickHouse vs TimescaleDB and ClickHouse vs Snowflake; background on time-series storage is in the time-series database overview. On 27 October 2026 the latest release on GitHub was v26.10.2.103-stable, published on 24 October 2026.

Licence. The LICENSE file in the ClickHouse repository is the "Apache License Version 2.0, January 2004"; ClickHouse Cloud is a commercial service sold on its own terms (see the pricing page). This is a factual description, not legal advice: read the licence and the service terms and take advice for your situation.

How MergeTree stores telemetry

Almost every ClickHouse table that holds telemetry uses an engine from the MergeTree family. The documentation describes these engines as designed for high data ingest rates and huge data volumes. The write path explains most of the design advice that follows:

• Each insert creates a part. An insert is sorted by the table's ORDER BY key, split into columns, compressed and written to disk as a new, immutable data part with its own sparse primary index.
• Background merges. ClickHouse merges small parts into larger ones in the background. Parts in different partitions are never merged together, and the merge process does not guarantee that rows with the same key end up in one part.
• Sparse primary index. Rather than one index entry per row, the primary index holds one entry (a "mark") per granule of rows, 8,192 rows by default. A query that filters on the leading ORDER BY columns uses binary search over the marks and reads only the granules that might match.
• Columns. Each column is stored and compressed separately, so a query that averages value for one point reads the key columns and value, not the whole row.

Two consequences matter for sensor data. First, many tiny inserts mean many tiny parts and constant merging, so writes should be batched. Second, because a "primary key" in ClickHouse is a sort order and not a uniqueness constraint, a replayed reading is stored twice unless you design for it (see duplicates below).

Choosing ORDER BY and PARTITION BY for sensor data

ORDER BY (the sorting key). The documentation's rules are to put first the columns most often used in WHERE filters, especially those that exclude many rows, to prefer columns correlated with the rest of the data because similar values compress better, and to keep the key short (four or five columns is typically enough). Ordering keys are defined when the table is created and cannot be added later. For building telemetry, the common filters are site or building, then the point (sensor), then a time range, which gives ORDER BY (building, point_id, ts). Readings from one point are then stored next to each other in time order, which suits both "one point over a month" and "all points in one building" queries.

PARTITION BY. ClickHouse's guidance is blunt: partitioning is primarily a data management technique and not a query optimisation tool. Its main job is retention: an expired partition can be dropped as a metadata operation instead of rewriting parts. Keep the partition key low-cardinality (the documentation suggests fewer than 100 to 1,000 distinct values) because parts are never merged across partitions, and too many partitions lead to the "too many parts" error. The TTL guide recommends partitioning by the date or month of the same time column used in the TTL, so for telemetry kept for months or years toYYYYMM(ts) fits, and for retention measured in days, partitioning by day. Avoid partitioning by sensor or point, which would create one partition per point.

Column types. Repeated strings such as building, point and unit names suit LowCardinality(String), which dictionary-encodes values. The documentation notes it is most efficient below about 10,000 distinct values and can be slower than a plain type above about 100,000, so measure on a sample if a site has tens of thousands of points. Use DateTime (seconds) or DateTime64 (sub-second) for time, and the narrowest numeric type that holds the reading.

CREATE TABLE bms_readings
(
    building  LowCardinality(String),
    point_id  LowCardinality(String),   -- e.g. 'AHU-01.SAT', 'CH-2.KW', 'L3-Z04.CO2'
    ts        DateTime('UTC'),
    value     Float64,
    quality   UInt8                     -- 0 = good, other codes from the BMS
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (building, point_id, ts)
TTL ts + INTERVAL 13 MONTH DELETE;

SELECT toStartOfHour(ts) AS hour,
       avg(value)        AS avg_sat_c,
       max(value)        AS max_sat_c
FROM bms_readings
WHERE building = 'HQ'
  AND point_id = 'AHU-01.SAT'
  AND ts >= '2026-10-01 00:00:00' AND ts < '2026-11-01 00:00:00'
GROUP BY hour
ORDER BY hour;

Retention and rollups: TTL and incremental materialized views

TTL. A table-level TTL deletes rows once ts plus the interval has passed; column TTLs can clear individual columns; TO DISK and TO VOLUME rules move older parts to cheaper storage in self-managed deployments (the documentation notes this hot/warm/cold setup does not apply to ClickHouse Cloud); and a GROUP BY clause in the TTL expression can roll rows up into aggregate columns before deleting them. TTL is applied during merges, not at insert time, so expired rows can remain visible for a while; by default a TTL merge is retried at least every four hours. The documentation recommends partitioning by the same time column used in the TTL so that whole partitions can be dropped.

Incremental materialized views. In ClickHouse a materialized view is a trigger: it runs its query on each block inserted into the source table and writes the result to a target table. That differs from PostgreSQL, where a materialized view is a snapshot refreshed on demand. For telemetry, a common pattern is raw readings kept for a limited period plus an hourly rollup kept much longer; the 13 months and 5 years in the example are illustrative assumptions, to be set from reporting, audit and diagnostic needs and storage cost. Averages cannot simply be summed, so the target uses AggregatingMergeTree with partial aggregate states (avgState, minState, maxState) that are combined with the -Merge functions at query time. The documentation stresses that the view's GROUP BY should match the target table's ORDER BY. Because the view only sees inserted blocks, the CREATE VIEW reference notes that updates, deletes or dropped partitions in the source do not change it.

Chained views. The pipeline on this page chains two views: the Kafka view below inserts into bms_readings, and bms_hourly_mv reads from bms_readings. The ClickHouse documentation describes materialized views that can be chained (cascaded): data inserted into the first table is forwarded to the first view's target table and then on to a second view defined on that target, and what is forwarded is the inserted block, not the merged contents of the table. Its cascade example starts from an ordinary INSERT rather than a Kafka table, and we have not run this pipeline, so test on your version that rows consumed from Kafka reach the hourly rollup. The trade-offs between this approach and refresh-based designs are covered in Continuous Aggregates vs Materialized Views.

CREATE TABLE bms_hourly
(
    building  LowCardinality(String),
    point_id  LowCardinality(String),
    hour      DateTime('UTC'),
    avg_value AggregateFunction(avg, Float64),
    min_value AggregateFunction(min, Float64),
    max_value AggregateFunction(max, Float64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYear(hour)
ORDER BY (building, point_id, hour)
TTL hour + INTERVAL 5 YEAR DELETE;

CREATE MATERIALIZED VIEW bms_hourly_mv TO bms_hourly AS
SELECT building, point_id,
       toStartOfHour(ts) AS hour,
       avgState(value)   AS avg_value,
       minState(value)   AS min_value,
       maxState(value)   AS max_value
FROM bms_readings
GROUP BY building, point_id, hour;

-- reading the rollup: merge the partial states
SELECT hour, avgMerge(avg_value) AS avg_kw, maxMerge(max_value) AS peak_kw
FROM bms_hourly
WHERE building = 'HQ' AND point_id = 'CH-2.KW'
GROUP BY hour
ORDER BY hour;

Real-time data streaming into ClickHouse

Keep the roles separate. MQTT brokers collect readings from gateways and field devices; Apache Kafka buffers, replays and fans out the stream to several consumers; ClickHouse stores the data and answers analytical queries. ClickHouse is not a message broker and does not replace one. Three ingestion routes are documented:

• Kafka table engine plus a materialized view. A table with ENGINE = Kafka is a consumer, not storage. The documentation explains that a plain SELECT on it is mainly for debugging because each message can be read only once; instead, once a materialized view is attached to the Kafka table it starts collecting data in the background and writes it into a MergeTree table. Delivered messages are tracked per consumer group, so each message in a group is counted once; to stop consumption or change the transformation, the documentation says to detach the view. Optional settings cover TLS and SASL (kafka_security_protocol with values such as ssl or sasl_ssl, and kafka_sasl_mechanism) and parse-error handling, including a dead_letter_queue mode that saves error data in system.dead_letter_queue. ClickHouse's Kafka integration overview lists the table engine as at-least-once, so design the target table for occasional duplicates. On ClickHouse Cloud the documentation recommends ClickPipes instead of the table engine.
• Batched inserts from your own consumer or gateway. The documentation recommends batches of at least 1,000 rows, ideally 10,000 to 100,000, and roughly one insert query per second, because every insert creates a part. Retrying a failed batch is safe as long as its contents and order are identical.
• Asynchronous inserts. When many small clients cannot batch (for example hundreds of gateways each posting a few readings), async_insert = 1 moves batching to the server: inserts are buffered in memory and flushed by size or time.

A complete Kafka design for building data, including topic keys, partitions and consumer groups, is in Designing an IoT Data Pipeline with Kafka; this page covers only the ClickHouse side.

CREATE TABLE bms_queue
(
    building String,
    point_id String,
    ts       DateTime('UTC'),
    value    Float64,
    quality  UInt8
)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka-1:9092,kafka-2:9092',
         kafka_topic_list  = 'bms.readings',
         kafka_group_name  = 'clickhouse-bms',
         kafka_format      = 'JSONEachRow';

CREATE MATERIALIZED VIEW bms_queue_mv TO bms_readings AS
SELECT building, point_id, ts, value, quality
FROM bms_queue;

Duplicates and corrections: ReplacingMergeTree, FINAL and mutations

Gateways replay buffered readings after an outage, and at-least-once consumers can deliver the same message twice. A plain MergeTree keeps every copy. ReplacingMergeTree(ver) keeps one row per ORDER BY key during merges, choosing the highest ver (for example an ingested_at column), but the documentation is explicit that deduplication happens only during merges, at an unknown time, and does not guarantee the absence of duplicates. Queries that must be exact add FINAL to the table name (which costs extra work at query time) or aggregate in a way that tolerates duplicates. Exact deduplication patterns for SQL databases are covered in Handling Duplicate Telemetry Events in SQL.

Corrections through ALTER TABLE ... UPDATE or DELETE are mutations: asynchronous background jobs that rewrite every affected part. The documentation advises avoiding frequent or large mutations on high-volume tables and using ReplacingMergeTree, CollapsingMergeTree, lightweight deletes or partition drops instead.

CREATE TABLE bms_readings_dedup
(
    building    LowCardinality(String),
    point_id    LowCardinality(String),
    ts          DateTime('UTC'),
    value       Float64,
    ingested_at DateTime('UTC')
)
ENGINE = ReplacingMergeTree(ingested_at)
PARTITION BY toYYYYMM(ts)
ORDER BY (building, point_id, ts);

SELECT point_id, ts, value
FROM bms_readings_dedup FINAL
WHERE building = 'HQ' AND ts >= '2026-10-27 08:00:00';

The same "latest version wins" rule in plain PostgreSQL

To check the expected result of a replay before loading it, or when the data lives in PostgreSQL, DISTINCT ON keeps the newest ingested row per reading. The 08:01 reading was replayed six minutes later with a corrected value, and only the replayed version survives. This ran on PostgreSQL 18.6; it is a plain SQL illustration of the rule, not ClickHouse code.

SET TimeZone = 'UTC';

CREATE TABLE ahu_supply_temp (
    sensor_id   text         NOT NULL,
    ts          timestamptz  NOT NULL,
    temp_c      numeric(5,2) NOT NULL,
    ingested_at timestamptz  NOT NULL
);

-- the gateway replayed the 08:01 reading after a reconnect, with a corrected value
INSERT INTO ahu_supply_temp VALUES
    ('ahu-01-sat', '2026-10-27 08:00+00', 13.80, '2026-10-27 08:00:02+00'),
    ('ahu-01-sat', '2026-10-27 08:01+00', 13.90, '2026-10-27 08:01:02+00'),
    ('ahu-01-sat', '2026-10-27 08:01+00', 13.95, '2026-10-27 08:06:40+00'),
    ('ahu-01-sat', '2026-10-27 08:02+00', 14.10, '2026-10-27 08:02:01+00'),
    ('ahu-02-sat', '2026-10-27 08:00+00', 12.60, '2026-10-27 08:00:03+00');

SELECT DISTINCT ON (sensor_id, ts) sensor_id, ts::text AS ts, temp_c, ingested_at::text AS ingested_at
FROM ahu_supply_temp
ORDER BY sensor_id, ts, ingested_at DESC;

 sensor_id  |           ts           | temp_c |      ingested_at
------------+------------------------+--------+------------------------
 ahu-01-sat | 2026-10-27 08:00:00+00 |  13.80 | 2026-10-27 08:00:02+00
 ahu-01-sat | 2026-10-27 08:01:00+00 |  13.95 | 2026-10-27 08:06:40+00
 ahu-01-sat | 2026-10-27 08:02:00+00 |  14.10 | 2026-10-27 08:02:01+00
 ahu-02-sat | 2026-10-27 08:00:00+00 |  12.60 | 2026-10-27 08:00:03+00

Sizing, security and deployment in practice

• Row volume. As an illustrative assumption, a campus BMS with 20,000 points read once a minute writes 28.8 million rows a day, roughly 10.5 billion a year. With monthly partitions and a 13-month TTL that is about 13 live partitions, well inside the partition guidance. Estimate storage from a representative sample loaded with your real key order, because compression depends heavily on how well each series sorts together; do not size from generic ratios.
• Batching. 20,000 single-row inserts a minute would create 20,000 parts a minute. Let the Kafka engine form blocks, batch in the consumer, or enable asynchronous inserts.
• Replication. Self-managed clusters replicate with the Replicated MergeTree engines using asynchronous multi-master replication; plan the coordination service and shard layout before data volume forces it.
• Security. ClickHouse manages users, roles and grants in SQL. Give dashboards read-only roles on the rollup tables, keep ingestion credentials separate, and use the Kafka engine's SSL or SASL settings when the broker requires them.
• Cloud pricing. ClickHouse Cloud meters compute and storage separately and can scale idle compute to zero; the pricing page lists Basic, Scale and Enterprise plans with rates that vary by cloud provider and region, plus separate charges for backups, ClickPipes ingestion and data transfer. Use the vendor's calculator for current figures; we do not quote prices here.

When not to use ClickHouse

• CMMS and CAFM records. Work orders, assets, contracts and permits change row by row and need constraints and transactions. In ClickHouse those changes are mutations that rewrite parts; keep this data in PostgreSQL or another relational database and join or replicate summaries into ClickHouse if needed.
• Exact, immediate deduplication or upserts. ReplacingMergeTree is eventually consistent; if every query must see exactly one row per reading without FINAL, dedupe before insert or choose an engine with unique constraints.
• Small or slow data. A few thousand points every 15 minutes is a few hundred thousand rows a day, which, in our editorial view, a partitioned PostgreSQL table is likely to handle with less operational effort, as shown in How to Store IoT Sensor Data.
• "Latest value" serving for many concurrent users. ClickHouse can answer it, but a key-value store, cache or the BMS itself is often the simpler place for a live tile.
• A metrics stack built on PromQL. If dashboards and alerts already depend on Prometheus, a Prometheus-compatible store keeps them unchanged.

Other analytical options for streaming telemetry include Apache Druid; for a broader shortlist see the guide to choosing a time-series database.

Frequently asked questions

Sources

• ClickHouse documentation: What is ClickHouse?
• ClickHouse documentation: MergeTree table engine
• ClickHouse documentation: Choosing a primary key
• ClickHouse documentation: Choosing a partitioning key
• ClickHouse documentation: A practical introduction to primary indexes
• ClickHouse documentation: LowCardinality(T)
• ClickHouse documentation: Manage data with TTL
• ClickHouse documentation: Incremental materialized view
• ClickHouse documentation: Kafka table engine
• ClickHouse documentation: Selecting an insert strategy
• ClickHouse documentation: ReplacingMergeTree table engine
• ClickHouse documentation: Avoid mutations
• ClickHouse Cloud pricing
• ClickHouse repository on GitHub
• ClickHouse repository: LICENSE
• ClickHouse documentation: Cascading materialized views
• ClickHouse documentation: CREATE VIEW
• ClickHouse documentation: Integrating Kafka with ClickHouse

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.