Time Series Databases: TimescaleDB vs ClickHouse vs InfluxDB — Choosing the Right One
Metrics, logs, IoT sensor data, financial ticks — all share a common pattern: timestamped data that arrives continuously and is queried by time ranges. General-purpose databases handle this poorly at scale because they are not optimized for time-based ingestion and range queries. Time series databases solve this with columnar storage, automatic partitioning, and time-aware compression. Therefore, this guide compares TimescaleDB, ClickHouse, and InfluxDB with honest assessments of when each shines and when each struggles.
What Makes Time Series Data Different
Time series workloads have unique characteristics that general databases handle poorly. Writes are append-only — you rarely update historical data. Queries almost always filter by time range. Recent data is accessed frequently while old data is rarely touched. Moreover, the volume is enormous: a fleet of 10,000 IoT sensors reporting every second generates 864 million rows per day.
These characteristics enable specific optimizations. Columnar storage compresses timestamps and repeated values extremely well (90-95% compression ratios). Time-based partitioning ensures range queries scan only relevant chunks. Automatic downsampling reduces storage for old data while preserving recent detail. No general-purpose database delivers all three out of the box.
There is a deeper reason a row store struggles here. In a traditional B-tree-indexed table, every new row triggers index updates scattered across the disk, and the index itself grows until it no longer fits in memory — at which point insert throughput falls off a cliff. Time series engines sidestep this by appending to time-ordered chunks and indexing coarsely. As a result, ingestion stays fast even as the dataset grows into the billions of rows, which is precisely where a vanilla relational table would grind to a halt.
TimescaleDB: PostgreSQL with Time Series Superpowers
TimescaleDB is a PostgreSQL extension — you install it on your existing PostgreSQL server, and your tables gain time series capabilities. Your existing SQL queries, JOINs, indexes, and tools all work unchanged. This is its greatest strength: you do not need to learn a new query language or operate a separate database.
-- Create a hypertable (time-partitioned table)
CREATE TABLE sensor_data (
time TIMESTAMPTZ NOT NULL,
sensor_id INTEGER NOT NULL,
temperature DOUBLE PRECISION,
humidity DOUBLE PRECISION,
pressure DOUBLE PRECISION
);
-- Convert to hypertable — automatically partitions by time
SELECT create_hypertable('sensor_data', 'time',
chunk_time_interval => INTERVAL '1 day');
-- Add compression policy — compress chunks older than 7 days
ALTER TABLE sensor_data SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'sensor_id',
timescaledb.compress_orderby = 'time DESC'
);
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');
-- Continuous aggregates (materialized views that auto-update)
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 hour', time) AS hour,
sensor_id,
AVG(temperature) as avg_temp,
MAX(temperature) as max_temp,
MIN(temperature) as min_temp,
COUNT(*) as readings
FROM sensor_data
GROUP BY hour, sensor_id;
-- Refresh automatically
SELECT add_continuous_aggregate_policy('sensor_hourly',
start_offset => INTERVAL '3 hours',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');
-- Query like normal SQL — joins with regular tables work
SELECT s.hour, s.avg_temp, d.location, d.building
FROM sensor_hourly s
JOIN devices d ON s.sensor_id = d.id
WHERE s.hour > NOW() - INTERVAL '24 hours'
ORDER BY s.hour DESC;TimescaleDB handles 100K-500K inserts/second on a single node with compression achieving 90%+ reduction. However, it is limited by single-node PostgreSQL for writes. Consequently, if you need millions of inserts per second, ClickHouse is a better fit.
The feature that separates TimescaleDB from a hand-rolled partitioned table is the continuous aggregate. Notice the chunk_time_interval choice above: it is a genuine tuning knob, not a formality. A one-day chunk works well for sensors reporting at second resolution, but for high-cardinality, high-frequency data you may want smaller chunks so that each fits in memory during writes — the docs recommend sizing chunks so the most recent one plus its indexes stays within roughly 25% of available RAM. Pick it too large and inserts slow as the active chunk spills to disk; too small and you drown in chunk-management overhead.
ClickHouse: Raw Speed for Analytics at Scale
ClickHouse is a columnar analytical database built for maximum query speed. It was designed at Yandex to analyze billions of rows in real-time. Where TimescaleDB gives you PostgreSQL compatibility, ClickHouse gives you raw performance — 10-100x faster analytical queries than PostgreSQL on the same hardware.
-- ClickHouse table with MergeTree engine
CREATE TABLE events (
timestamp DateTime,
user_id UInt64,
event_type LowCardinality(String),
page_url String,
response_time_ms UInt32,
country LowCardinality(String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (event_type, user_id, timestamp)
TTL timestamp + INTERVAL 90 DAY
SETTINGS index_granularity = 8192;
-- Insert millions of rows per second (ClickHouse is built for this)
INSERT INTO events SELECT
now() - rand() % 86400,
rand() % 1000000,
['pageview', 'click', 'purchase'][rand() % 3 + 1],
concat('/page/', toString(rand() % 1000)),
rand() % 5000,
['US', 'UK', 'DE', 'JP', 'BR'][rand() % 5 + 1]
FROM numbers(10000000); -- 10 million rows
-- Analytical queries that run in milliseconds on billions of rows
SELECT
toStartOfHour(timestamp) AS hour,
event_type,
count() AS events,
avg(response_time_ms) AS avg_response,
quantile(0.95)(response_time_ms) AS p95_response
FROM events
WHERE timestamp > now() - INTERVAL 24 HOUR
GROUP BY hour, event_type
ORDER BY hour DESC;ClickHouse excels at aggregation queries over massive datasets. It processes billions of rows per second for COUNT, SUM, and AVG operations. Additionally, its compression is exceptional — a 1TB raw dataset typically compresses to 100-200GB. The trade-off: ClickHouse is not great for point lookups, does not support UPDATE/DELETE efficiently (mutations are asynchronous batch operations), and has no ACID transactions.
The ORDER BY Key: ClickHouse’s Most Important Decision
The single biggest performance lever in ClickHouse is the ORDER BY clause of your MergeTree, because it defines the primary index — a sparse index that lets the engine skip whole granules of data. In the table above, ORDER BY (event_type, user_id, timestamp) makes queries filtered by event_type extremely fast, since matching rows are physically co-located. However, a query that filters only by timestamp cannot use that index efficiently, because timestamp is the last column in the sort key.
The practical rule is to put low-cardinality, frequently-filtered columns first and the timestamp later. Getting this wrong is the most common reason teams report that “ClickHouse is slow” — the engine is fine, the sort key was just wrong for the query pattern. When two query patterns genuinely conflict, the idiomatic fix is a projection or a second MergeTree table populated by a materialized view, each ordered for its own access pattern.
-- A materialized view maintains a second ordering for time-first queries
CREATE MATERIALIZED VIEW events_by_time
ENGINE = MergeTree()
ORDER BY (timestamp, event_type)
AS SELECT * FROM events;
-- Pre-aggregate at insert time with an AggregatingMergeTree rollup
CREATE MATERIALIZED VIEW events_hourly_mv
ENGINE = AggregatingMergeTree()
ORDER BY (hour, event_type)
AS SELECT
toStartOfHour(timestamp) AS hour,
event_type,
countState() AS events,
avgState(response_time_ms) AS avg_response
FROM events
GROUP BY hour, event_type;The AggregatingMergeTree rollup is the ClickHouse equivalent of a continuous aggregate: it folds raw events into hourly summaries as data arrives, so dashboard queries read thousands of pre-computed rows instead of billions of raw ones. Benchmarks published by the project show this pattern turning multi-second scans into single-digit-millisecond lookups.
InfluxDB: Purpose-Built for Metrics and IoT
InfluxDB 3.0 is a rewrite in Rust using Apache Arrow and DataFusion. It speaks SQL (unlike InfluxDB 2.x which used Flux). InfluxDB is the easiest to get started with for pure metrics use cases — it understands concepts like tags (indexed metadata), fields (values), and measurements (tables) natively.
InfluxDB’s strength is its ecosystem: Telegraf for data collection (200+ input plugins), built-in alerting, and a managed cloud offering that requires zero operational overhead. For teams that want a metrics database without operating database infrastructure, InfluxDB Cloud is the simplest path. However, InfluxDB is less flexible than TimescaleDB or ClickHouse for analytical queries and cannot join with other data sources.
One historical gotcha deserves a warning, because many teams were burned by it. In the InfluxDB 1.x and 2.x line, putting a high-cardinality value — such as a unique request ID or user ID — into a tag caused a “cardinality explosion” that consumed memory until the database fell over. Tags were indexed and the index lived in RAM. The 3.0 columnar engine largely removes this constraint, but the lesson generalizes: in any time series store, be deliberate about which dimensions you index versus store as plain fields.
Decision Framework: When to Choose Each
Choose TimescaleDB when: You already use PostgreSQL and want to add time series capabilities without operating a separate database. You need JOINs between time series data and relational data. Your team knows SQL well. Your ingest rate is under 500K rows/second. You value PostgreSQL’s ecosystem (pg_dump, pg_restore, Patroni, PgBouncer).
Choose ClickHouse when: You need to query billions of rows interactively. Your primary workload is analytical aggregations (not point lookups). You need millions of inserts per second. You are building a user-facing analytics product. You can accept eventual consistency and no ACID transactions.
Choose InfluxDB when: Your use case is pure metrics/monitoring. You want a managed service with zero operational overhead. You need Telegraf’s 200+ data collection plugins. Your team is small and cannot operate database infrastructure. You do not need complex JOINs or analytical queries.
COMPARISON MATRIX (March 2026):
TimescaleDB ClickHouse InfluxDB 3.0
Query Language: Full PostgreSQL ClickHouse SQL SQL + InfluxQL
Insert Rate: 100-500K/s 1-10M/s 100K-1M/s
Compression: 90-95% 95-98% 85-90%
JOINs: Full SQL JOINs Limited JOINs No JOINs
Updates/Deletes: Yes (MVCC) Async mutations Limited
ACID Transactions: Yes No No
Ecosystem: PostgreSQL Growing Telegraf/Grafana
Managed Options: Timescale Cloud ClickHouse Cloud InfluxDB Cloud
Learning Curve: Low (it's PG) Medium Low-Medium
Best For: Mixed workloads Analytics Pure metricsWhen NOT to Use a Time Series Database At All
It is worth saying plainly: many projects reach for a specialized engine before they need one. If your dataset is in the tens of millions of rows rather than the billions, plain PostgreSQL with a BRIN index on the timestamp column and a couple of partitions will serve you well for years, and you avoid operating a second database. Premature adoption of ClickHouse, in particular, punishes you — its weakness at point lookups, single-row updates, and transactional consistency makes it a poor fit if your access pattern is “fetch and edit one record.”
Equally, do not choose ClickHouse for a workload dominated by frequent corrections to historical data; its asynchronous mutations make that painful, and you will fight the engine. Do not choose InfluxDB if you need to join your metrics against rich relational data, because it simply cannot. And do not choose TimescaleDB expecting it to scale writes horizontally like ClickHouse does — it inherits PostgreSQL’s single-primary write ceiling. Match the tool to the dominant access pattern, and when in doubt, start with the database you already run and measure before migrating.
Related Reading:
Resources:
In conclusion, time series databases solve a real problem that general-purpose databases handle poorly at scale. TimescaleDB is the pragmatic choice for PostgreSQL teams. ClickHouse delivers unmatched analytical performance for billion-row datasets. InfluxDB offers the simplest path for pure metrics workloads. Pick based on your existing stack, query complexity, and scale requirements.