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.
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.
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.
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.
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 metrics
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.