ClickHouse vs TimescaleDB

Quick verdict

This page is for engineers and architects deciding where building, plant or fleet telemetry should be analysed: BMS points such as AHU supply-air temperature, chiller kW and zone CO2, meter readings every 1 to 60 seconds, and the equipment history that a CAFM or CMMS uses to plan maintenance. For the bigger picture of how time-series databases fit between devices and dashboards, start with the time-series and IoT data hub. For the general analytics-versus-transactions question (MergeTree versus row storage, JOIN behaviour, multi-statement transactions) see ClickHouse vs PostgreSQL; this page does not repeat it and concentrates on time-series workloads.

ClickHouse is an open source (Apache 2.0) column-oriented database for real-time analytics, also sold as the managed ClickHouse Cloud service. Its documentation has a dedicated time-series guide covering time functions, gap filling, codecs and materialized views. On 27 October 2026 the latest stable release on GitHub was v26.10.2 (published 24 October 2026), while the newest entry in the documentation changelog was release 26.9 (21 September 2026), so the changelog lags the GitHub releases; the newest long-term support (LTS) release in that changelog was 26.8. See ClickHouse for telemetry for a design walkthrough.

TimescaleDB, maintained by Tiger Data, is a PostgreSQL extension. Hypertables split a table into chunks by time, older chunks can move to the hypercore columnstore, continuous aggregates keep rollups current and retention policies drop whole chunks. The latest release on GitHub when checked was 2.31.0 (20 October 2026). See the TimescaleDB guide and TimescaleDB vs PostgreSQL.

Side by side

Key differences

Table design: sort key versus time chunks

In ClickHouse the most important choice for a telemetry table is the ORDER BY key, because data on disk is sorted by it and the sparse index is built over it. ClickHouse's time-series guide shows queries that filter by the leading key columns running far faster than on a table sorted by time alone, and it recommends low-cardinality string types and delta codecs for timestamp columns. A BMS table keyed by site, asset and time might look like this:

-- Illustrative DDL, not run here: pattern from the ClickHouse documentation; check every
-- setting against the documentation for your version before use.
CREATE TABLE ahu_reading
(
    ts              DateTime CODEC(Delta, ZSTD),
    site            LowCardinality(String),
    asset_id        LowCardinality(String),
    supply_air_temp Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (site, asset_id, ts)
TTL ts + INTERVAL 6 MONTH;  -- illustrative retention period

In TimescaleDB the table is an ordinary PostgreSQL table converted to a hypertable. Tiger Data's documentation describes hypertables as PostgreSQL tables that automatically partition data by time, and queries only touch the chunks that match the time range. You design indexes, keys and foreign keys exactly as in PostgreSQL, which is why the asset register and work orders can live in the same schema. The plain PostgreSQL part of such a schema, and a query that joins hourly averages to open work orders, ran as shown on PostgreSQL 18.6; on TimescaleDB you would normally use time_bucket instead of date_bin and make ahu_reading a hypertable:

-- PostgreSQL: run on PostgreSQL 18.6 (PGlite)
CREATE TABLE asset (
  asset_id   text PRIMARY KEY,
  site       text NOT NULL,
  asset_type text NOT NULL
);
CREATE TABLE ahu_reading (
  asset_id        text NOT NULL REFERENCES asset,
  ts              timestamptz NOT NULL,
  supply_air_temp double precision,
  PRIMARY KEY (asset_id, ts)
);
CREATE TABLE work_order (
  wo_id     int PRIMARY KEY,
  asset_id  text NOT NULL REFERENCES asset,
  status    text NOT NULL,
  opened_at timestamptz NOT NULL
);
-- sample rows: two AHUs, six readings, one open work order on ahu-01

SELECT date_bin('1 hour', r.ts, TIMESTAMPTZ '2026-01-01 00:00:00+00') AS hour,
       a.site, r.asset_id,
       round(avg(r.supply_air_temp)::numeric, 2) AS avg_sat,
       (SELECT count(*) FROM work_order w
         WHERE w.asset_id = r.asset_id AND w.status = 'open') AS open_work_orders
FROM ahu_reading r
JOIN asset a USING (asset_id)
GROUP BY 1, 2, 3
ORDER BY 1, 3;

--  hour                   | site | asset_id | avg_sat | open_work_orders
--  2026-10-27 08:00:00+00 | hq   | ahu-01   |   13.50 |                1
--  2026-10-27 08:00:00+00 | hq   | ahu-02   |   12.80 |                0
--  2026-10-27 09:00:00+00 | hq   | ahu-01   |   17.10 |                1
--  2026-10-27 09:00:00+00 | hq   | ahu-02   |   13.00 |                0

The same question in ClickHouse is possible, but the usual design copies the asset attributes into the telemetry table or into a dictionary, because ClickHouse does not enforce foreign keys and its documentation favours denormalised analytical tables. See narrow vs wide tables for the schema trade-offs that apply to both.

Rollups and late data: materialized views versus continuous aggregates

This is the difference that matters most for BMS and meter data, where readings arrive late after a gateway outage and are sometimes corrected. A ClickHouse incremental materialized view is an insert trigger: it processes each inserted block and writes the result to a target table, often a SummingMergeTree or AggregatingMergeTree. The time-series guide notes that such a target table is only populated when new records are inserted, so existing data needs a backfill, and the CREATE VIEW reference states that changes to existing source data (update, delete, drop partition) do not change the materialized view. ClickHouse also offers refreshable materialized views, which rerun a query on a schedule instead of reacting to inserts.

-- Illustrative DDL, not run here: pattern from the ClickHouse documentation; check every
-- setting against the documentation for your version before use.
CREATE TABLE ahu_hourly
(
    hour     DateTime,
    asset_id LowCardinality(String),
    sum_sat  Float64,
    n        UInt64
)
ENGINE = SummingMergeTree
ORDER BY (asset_id, hour);

CREATE MATERIALIZED VIEW ahu_hourly_mv TO ahu_hourly AS
SELECT toStartOfHour(ts) AS hour, asset_id,
       sum(supply_air_temp) AS sum_sat, count() AS n
FROM ahu_reading
GROUP BY hour, asset_id;

TimescaleDB continuous aggregates are refreshed in the background as new data is added or old data is modified, within the window that the refresh policy covers. According to that description, a reading that arrives two hours late is picked up on the next refresh if its bucket is still inside the refresh window, without extra application logic (not run here):

-- Illustrative DDL, not run here: from the Tiger Data documentation; check every
-- setting against the documentation for your version before use.
CREATE MATERIALIZED VIEW ahu_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts) AS bucket, asset_id,
       avg(supply_air_temp) AS avg_sat
FROM ahu_reading
GROUP BY bucket, asset_id;

In our view, ClickHouse rollups suit append-only streams where late data is rare or can be handled by re-inserting a correcting row; TimescaleDB is simpler when corrections are routine. See continuous aggregates vs materialized views and late-arriving and out-of-order sensor events.

Retention, tiering and duplicates

ClickHouse TTL rules can delete rows or columns, roll older rows up into aggregates with GROUP BY, or move parts between disks or volumes for a hot, warm and cold layout (the volume moves do not apply in ClickHouse Cloud). TTL actions run during merges, which by default are retried at least every four hours, and the documentation recommends partitioning by the same date column so whole partitions can be dropped.

TimescaleDB retention works on whole chunks: add_retention_policy drops chunks whose data is entirely older than the threshold. Combined with continuous aggregates, raw one-second data can be dropped after a few months while hourly summaries are kept for years (illustrative periods; set them from your reporting, audit and diagnostic needs). Tiger Cloud adds tiered storage on object storage; self-hosted users manage disks themselves.

Duplicates behave differently. A PostgreSQL primary key rejects a second copy of the same reading, and INSERT ... ON CONFLICT DO NOTHING makes a sink idempotent (see the PostgreSQL 23505 duplicate key error). A ClickHouse primary key is a sort key and does not enforce uniqueness; ReplacingMergeTree removes rows with the same sorting key during background merges, so queries that must not double count use FINAL or aggregate defensively. See handling duplicate telemetry events and retention and downsampling design.

Ingestion and the streaming layer

Neither database is a message broker. Devices usually publish through gateways to an MQTT broker or Kafka, and a consumer writes to the database (see Kafka vs MQTT). ClickHouse documents three ways to read from Kafka: ClickPipes (managed, ClickHouse Cloud), the ClickHouse Kafka Connect sink, and the Kafka table engine bundled with open source ClickHouse; its own comparison lists ClickPipes and the table engine as at-least-once and describes the Connect sink as one that "Can be configured to support exactly-once semantics". That is ClickHouse's description of the sink under the conditions in its documentation; it was not verified here, and it does not make the pipeline as a whole exactly-once, so design the target tables to tolerate duplicates as described above. For TimescaleDB, any client that can write to PostgreSQL works: a Kafka Connect JDBC sink, Telegraf's PostgreSQL output, or a small service that batches rows and uses COPY.

For write-heavy telemetry, ClickHouse prefers fewer, larger inserts because each insert creates a data part that is merged later; TimescaleDB inherits PostgreSQL's write path, so batching, COPY and connection pooling matter. Neither vendor publishes a neutral like-for-like benchmark against the other, so measure with your own point list and query mix; see time-series database performance.

Scaling, operations and security

ClickHouse scales out with shards and asynchronous replicas coordinated by ClickHouse Keeper, and ClickHouse Cloud separates compute from object storage. In our view that suits a large portfolio of buildings, but a self-managed cluster is a distributed system to operate: replication, Keeper, merges and disk tiers all need monitoring.

TimescaleDB runs inside PostgreSQL. In the self-hosted replication setup that Tiger Data documents, data is written to the primary and mirrored to replicas, which can serve read-only queries to scale reads; the documentation we checked does not describe a way to spread writes for one hypertable across several primaries, so plan write capacity around one primary and check the current Tiger Data documentation for other options. Operations are PostgreSQL operations (vacuum, upgrades, backups, connection pooling) plus the extension version, which teams that already run PostgreSQL will recognise. Security follows PostgreSQL: roles, row-level security, TLS and audit extensions, which helps when tenants or contractors must only see their own sites; how to pass the tenant safely behind a connection pooler is covered in PostgreSQL multi-tenant telemetry. ClickHouse has its own users, roles, row policies and quotas. For backup approaches see backup and restore for time-series data.

Pricing and licensing

ClickHouse open source is under the Apache License 2.0 and carries no licence fee. ClickHouse Cloud meters compute per compute unit-hour (one unit is 8 GiB of RAM and 2 vCPUs) and storage per TB-month of compressed data, separately, and can scale idle compute to zero. For N. Virginia (us-east-1) the pricing page listed storage at USD 25.30 per TB per month on every plan, and compute at USD 0.2181 (Basic), USD 0.2985 (Scale) and USD 0.3903 (Enterprise) per unit-hour; Basic is limited to 1 TB of storage. Data transfer, backups beyond the included one and ClickPipes ingestion are billed in addition. New organisations get USD 300 in credits for 30 days. Bring Your Own Cloud is quoted separately.

TimescaleDB self-hosted carries no licence fee in either edition: Tiger Data's edition page states that "TimescaleDB Community Edition is completely free if you manage your own service", subject to the licence terms below. Tiger Cloud bills compute hourly and storage by average GB used. On 27 October 2026 the pricing page listed storage at USD 0.177 per GB-month on the Performance plan and USD 0.212 per GB-month on the Scale plan, and its FAQ states that "Storage is priced the same across all regions, but compute prices will vary depending on the region"; the compute starting prices on the plan cards do not state a region, so we do not quote them. There is a 30-day free trial, and the page says its storage figures assume 5x compression.

Licensing notes. The ClickHouse repository's LICENSE file is the Apache License 2.0, whose section 2 grants "a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form", subject to its conditions, including those on redistribution in section 4. For TimescaleDB, the repository's LICENSE file puts code outside the tsl directory under Apache 2.0 and code inside it under the Timescale License. Tiger Data's licence page summarises the TSL as: "Roughly speaking, as long as you are not offering TimescaleDB as a hosted Database-as-a-Service, you can use all Community features for free." The TSL agreement is more detailed: its Internal Use grant covers use "solely for Your own internal business purposes in a manner that does not expose or give access to, directly or indirectly (e.g., via a wrapper), the Timescale Data Definition Interfaces or the Timescale Data Manipulation Interfaces" to anyone other than you, your employees and contractors; a separate grant covers Value Added Products or Services; and section 2.2 prohibits "using any TSL Licensed Software to provide time-sharing services or database-as-a-service services". Not legal advice: this is a factual summary of the licence texts as published on 27 October 2026; read the licences for the versions you use and take legal advice for your situation.

The two pricing models are not directly comparable: ClickHouse Cloud bills compressed storage and compute units that can idle to zero, Tiger Cloud bills provisioned compute hours and stored GB. Estimate both from your points, sample rate, retention and dashboard load.

Pricing checked on the vendors' official pages on 27 October 2026. Prices change; confirm before buying.

Where each one leads

Limitations

When to choose each

When neither is right

• Volumes are modest (hundreds of points at minute intervals): plain PostgreSQL with partitioning may be enough; see TimescaleDB vs PostgreSQL.
• The data is infrastructure metrics scraped for alerting: a metrics system fits better; see InfluxDB vs Prometheus.
• You want a dedicated time-series engine with its own ingestion protocol: see TimescaleDB vs InfluxDB and QuestDB vs InfluxDB.
• History is already landed in a warehouse or lakehouse for company-wide reporting: see Snowflake vs Databricks and the time-series database selection guide.

Final recommendation

Frequently asked questions

Sources

• ClickHouse GitHub repository (licence, releases)
• ClickHouse: time-series use case guide
• ClickHouse: time-series storage efficiency
• ClickHouse: time-series query performance
• ClickHouse: manage data with TTL
• ClickHouse: incremental materialized views
• ClickHouse: CREATE VIEW (refreshable materialized views)
• ClickHouse: updating data overview
• ClickHouse: integrating Kafka with ClickHouse
• ClickHouse Cloud pricing
• TimescaleDB GitHub repository
• Tiger Data: understand hypertables
• Tiger Data: continuous aggregates
• Tiger Data: data retention
• Tiger Data: compare TimescaleDB editions
• Tiger Data: software licensing
• Tiger Data pricing
• ClickHouse releases on GitHub
• ClickHouse documentation: 2026 changelog
• ClickHouse: horizontal scaling (shards and ClickHouse Keeper)
• Tiger Data: replication and high availability (self-hosted)
• ClickHouse repository: LICENSE
• Apache License, Version 2.0 (apache.org)
• TimescaleDB repository: LICENSE file
• TimescaleDB repository: Timescale License agreement

Checked October 2026.

How we research comparisons: our editorial method.