ClickHouse vs TimescaleDB

Architectural shapes

TimescaleDB rides Postgres; recent chunks are row-stored, older chunks compress to columnar in the same database.

Side by side

Hypertables & chunks

A TimescaleDB hypertable looks like a regular Postgres table. Behind the scenes the extension auto-partitions it into chunks by a time range (default 7 days). Each chunk is a child table; queries with a time predicate only touch matching chunks. Conceptually identical to ClickHouse's PARTITION BY toYYYYMM(ts), but it happens automatically.

-- TimescaleDB
CREATE TABLE metrics (
    ts        TIMESTAMPTZ NOT NULL,
    sensor_id INT,
    value     DOUBLE PRECISION
);
SELECT create_hypertable('metrics', 'ts', chunk_time_interval => INTERVAL '1 day');

-- ClickHouse equivalent
CREATE TABLE metrics (
    ts        DateTime,
    sensor_id UInt32,
    value     Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(ts)
ORDER BY (sensor_id, ts);

Continuous aggregates vs materialized views

TimescaleDB continuous aggregates are incremental: they store partial results per time bucket and background workers refresh them when new data arrives in older buckets. The result is queryable as a regular view; it includes both materialized buckets and "live" data not yet rolled up.

CREATE MATERIALIZED VIEW metrics_5m
WITH (timescaledb.continuous) AS
SELECT time_bucket('5 minutes', ts) AS bucket,
       sensor_id,
       avg(value) AS avg_v,
       max(value) AS max_v
FROM metrics
GROUP BY bucket, sensor_id;

ClickHouse's INSERT-triggered MVs achieve the same shape but are eager: every INSERT block fires the SELECT. For tables with mostly-monotonic ingestion both are equivalent; for tables with retroactive inserts, Timescale's continuous-aggregate model is simpler.

Compression

TimescaleDB compresses old chunks by reformatting them into a columnar layout with run-length and delta encoding, segmented and ordered by user-chosen keys. Typical 5–15× reduction. Hot recent chunks remain row-store (for fast point inserts/updates).

ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby   = 'ts DESC'
);
SELECT add_compression_policy('metrics', INTERVAL '7 days');

ClickHouse compresses every column from the first byte. Codecs (Gorilla, DoubleDelta, T64, ZSTD) are tuned to the data type. 10–30× is normal; 100× is achievable on low-cardinality columns with LowCardinality.

Query language and joins

TimescaleDB inherits the entire Postgres SQL surface — window functions, CTEs, recursive queries, JSON, foreign data wrappers, planner hints. Joins to OLTP tables (users, sensors, alerts) work natively because they're in the same database.

ClickHouse SQL is rich for analytics but quirky around joins, transactions, and PG-specific features. Joining a ClickHouse table to a Postgres dimension means a postgresql() table function, an external dictionary, or a replication pipeline.

Throughput

For pure analytical scans on terabyte tables, ClickHouse is 5–20× faster per core than TimescaleDB on the same hardware. The gap shrinks on selective queries with good index alignment, and reverses entirely if you need a row-level INSERT/UPDATE workload (Postgres's bread and butter).

Retention & downsampling

Both engines have first-class retention. TimescaleDB:

SELECT add_retention_policy('metrics', INTERVAL '90 days');
SELECT add_continuous_aggregate_policy('metrics_5m',
    start_offset => INTERVAL '7 days',
    end_offset   => INTERVAL '1 hour',
    schedule_interval => INTERVAL '5 minutes');

ClickHouse uses TTL clauses on tables and columns:

CREATE TABLE metrics (
    ts DateTime,
    sensor_id UInt32,
    value Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (sensor_id, ts)
TTL ts + INTERVAL 90 DAY DELETE,
    ts + INTERVAL 30 DAY TO VOLUME 'cold',
    ts + INTERVAL 7 DAY GROUP BY sensor_id, toStartOfHour(ts)
        SET value = avg(value);

ClickHouse TTL also supports tier-down (move to cold storage) and downsample-in-place (TTL GROUP BY) without external jobs — the policy lives on the table definition itself.

Tradeoffs

• + ClickHouse: higher analytical throughput, better compression, columnar from start.
• + TimescaleDB: full Postgres ecosystem, ACID, joins to OLTP, mature time-series API surface.
• − ClickHouse: no transactions, separate database, weaker SQL on edge cases.
• − TimescaleDB: slower aggregations on huge tables, hot chunks are row-stored (less efficient).

Sizing & scaling

TimescaleDB scales vertically by default. Multi-node TimescaleDB exists but the canonical deployment is one Postgres instance with primary/replica failover. This is fine until the working set exceeds a single machine's RAM/CPU; then you're rebuilding shard logic in application code.

ClickHouse scales horizontally by design — Distributed tables spread queries across shards, ReplicatedMergeTree provides per-shard redundancy. Petabyte deployments are routine. The price: the "single database" mental model you got with Postgres no longer fits; you operate a cluster.

Ingestion ceiling, single node, structured time-series:

• TimescaleDB: ~100k–500k rows/sec sustained.
• ClickHouse: ~1M–10M rows/sec sustained.

FAQ