Time-Series Databases — InfluxDB, TimescaleDB, ClickHouse

Overview

Time-series databases (TSDBs) are optimized for workloads where data is indexed primarily by time: metrics, IoT sensor readings, application logs, financial tick data, and system monitoring. Unlike general-purpose databases, TSDBs exploit the immutable, append-only nature of time-series data to achieve massive ingestion throughput and efficient time-range queries. Specialized engines like InfluxDB, TimescaleDB, and ClickHouse each trade ease of use, SQL compatibility, and raw performance differently.

When to Use

• You ingest millions of data points per second, each carrying a timestamp
• Queries are predominantly time-range scans (WHERE time > now() - interval '1 day')
• Data is append-only and rarely updated after insertion
• You need retention policies and downsampling to control storage growth
• Aggregation queries (avg, sum, count) over sliding windows are common

Do not reach for a TSDB when records get updated in place, when joins and referential integrity matter, or when a few thousand rows cover your history — plain PostgreSQL handles those better.

Comparison

Feature	InfluxDB	TimescaleDB	ClickHouse
Base engine	Custom (TSM/TSI)	PostgreSQL extension	Columnar OLAP
SQL support	InfluxQL / Flux	Full SQL	SQL dialect
Ease of setup	Single binary	PostgreSQL + extension	More complex
Ingestion rate	Very high	High	Extremely high
Compression	Good	Good	Excellent
Best for	DevOps metrics, IoT	Apps already on Postgres	Analytics, big data

Two neighbors worth knowing: VictoriaMetrics targets the Prometheus-compatible monitoring niche (drop-in remote storage, fast ingestion, simpler operations than a general TSDB), and QuestDB speaks line protocol while answering SQL at ClickHouse-like speed. The three engines above are still the ones you’re most likely to inherit or evaluate first.

Data Model (InfluxDB Line Protocol)

measurement,tag1=value1,tag2=value2 field1=42.0,field2="text" 1465839830100400200

• Measurement: logical container (like a table)
• Tags: indexed metadata dimensions (device ID, region) — expensive at high cardinality
• Fields: actual values (temperature, CPU usage) — not indexed
• Timestamp: nanosecond precision (often more than you need)

TimescaleDB Example

-- Enable TimescaleDB extension and create hypertable
CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INT NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION
);

-- Convert to hypertable with 1-day chunks
SELECT create_hypertable('sensor_data', by_range('time', INTERVAL '1 day'));

-- Continuous aggregate for hourly averages
CREATE MATERIALIZED VIEW hourly_avg
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) as bucket,
    sensor_id,
    AVG(temperature) as avg_temp,
    AVG(humidity) as avg_humidity
FROM sensor_data
GROUP BY bucket, sensor_id;

-- Retention policy: drop chunks older than 90 days
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

ClickHouse Example

-- Create a MergeTree table for time-series data
CREATE TABLE events (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    value Float64
) ENGINE = MergeTree()
ORDER BY (event_type, event_time);

-- Efficient time-range query
SELECT
    toStartOfHour(event_time) as hour,
    count() as event_count,
    avg(value) as avg_value
FROM events
WHERE event_time > now() - INTERVAL 7 DAY
GROUP BY hour
ORDER BY hour;

The ORDER BY key is the single most important decision in ClickHouse: data is physically sorted by it, so queries filtering on the leading columns skip most of the table. Put the selective filter first (event_type above) and time second.

Ingestion at Scale

The fastest way to break a TSDB is to insert rows one at a time. Every engine wants large, ordered batches — what changes is how big they should be and how you deliver them.

Engine	Write path	Batch size	Notes
InfluxDB	Line protocol over HTTP	5K–10K lines	Enable gzip; batch by measurement to amortize parsing
TimescaleDB	INSERT / COPY	10K–50K rows per transaction	COPY is the fast path for backfills; commit per batch, never per row
ClickHouse	Native or HTTP insert	10K–1M rows	Small inserts create tiny parts — merges fall behind until you hit “too many parts”

Two rules apply everywhere:

• Buffer client-side, flush on size or time. A collector that writes every 10K points or 5 seconds keeps the write path hot without stalling producers.
• Keep timestamps monotonic inside a batch. Out-of-order points are accepted, but they force the engine to merge into older chunks or parts — tolerable in moderation, expensive as a steady state.

If you ingest through Kafka or a queue, batch at the consumer, not the producer: the queue absorbs the bursts so the database sees steady, well-sized writes.

ClickHouse has a cheat code here: async_insert=1 buffers small inserts server-side and flushes them as properly-sized parts, which saves you from writing the batching layer yourself at moderate load. On TimescaleDB, keep the hypertable’s chunk_time_interval aligned with your write rate as well — churning through half-empty chunks wastes the same memory you’re trying to save on the read side. And when batches still arrive out of order at scale, a quick look at system.parts tells you whether merges are keeping up before query latency does.

The Same Query in Three Engines

Ask for the hourly average per sensor over the last 24 hours and each engine answers in its own dialect:

-- TimescaleDB: time_bucket does the grouping
SELECT time_bucket('1 hour', time) AS hour, sensor_id, avg(temperature)
FROM sensor_data
WHERE time > now() - INTERVAL '24 hours'
GROUP BY hour, sensor_id;

-- ClickHouse: toStartOfHour is the equivalent
SELECT toStartOfHour(event_time) AS hour, user_id, avg(value)
FROM events
WHERE event_time > now() - INTERVAL 24 HOUR
GROUP BY hour, user_id;

-- InfluxDB: InfluxQL groups by time interval natively
SELECT mean("temperature") FROM "sensor_data"
WHERE time > now() - 24h
GROUP BY time(1h), "sensor_id";

The second query you will write constantly — the latest value per series — shows their personalities even better:

-- TimescaleDB: DISTINCT ON, the Postgres idiom
SELECT DISTINCT ON (sensor_id) sensor_id, time, temperature
FROM sensor_data
ORDER BY sensor_id, time DESC;

-- ClickHouse: argMax does the same in one pass
SELECT user_id, argMax(value, event_time) AS latest
FROM events
GROUP BY user_id;

InfluxDB answers that one for free: SELECT last("temperature") FROM "sensor_data" GROUP BY "sensor_id". DISTINCT ON is elegant at low series counts but scans more as cardinality grows; argMax stays flat because the sort key does the work. Beyond these idioms, standard SQL query optimization still applies — chunk pruning and plain indexes do most of the work on TimescaleDB.

Retention and Downsampling

Strategy	How	Trade-off
Raw retention	Keep all data for N days	Highest fidelity, highest cost
Downsampling	Aggregate to lower resolution after N days	Saves storage, loses granularity
Tiered storage	Move old chunks to cold storage	Slower old queries, cheaper

-- InfluxDB: continuous query for downsampling
CREATE CONTINUOUS QUERY "hourly_cpu" ON "monitoring"
BEGIN
    SELECT mean("usage") INTO "downsampled"."autogen"."cpu_1h"
    FROM "monitoring"."autogen"."cpu"
    GROUP BY time(1h),*
END;

A typical pipeline keeps hot raw data for days, minute aggregates for months, and hourly aggregates for years — the same pre-aggregation trade-off a metrics pipeline makes upstream of the database:

Compression is where the engines quietly pay for themselves. TimescaleDB converts old chunks to a columnar layout with ALTER TABLE ... SET (timescaledb.compress) and typically shrinks them to a tenth of their size; ClickHouse lets you pick per-column codecs (Delta, DoubleDelta, Gorilla, ZSTD) and reaches similar ratios on time-sorted data; InfluxDB compresses its TSM blocks automatically, no knobs required. Compression is also the reason “keep raw forever” is sometimes affordable — run the math on your own data before assuming you need aggressive downsampling.

High Cardinality Is the Real Enemy

Every TSDB failure story starts the same way: someone puts a UUID, a request ID, or a user ID into an indexed tag. Cardinality — the number of distinct tag combinations — determines index and memory size. Ten sensors × three metrics is trivial; ten million unique tag sets can take the engine down.

Rules that keep you safe:

• Tags are for dimensions you filter or group by, with bounded value sets: region, host, service, status
• Never tag by request ID, session ID, user ID, email, or timestamps
• Fields carry the high-cardinality values instead — they are stored, not indexed
• Measure series count regularly (influxdb reports it; TimescaleDB approximates via pg_class chunk stats)

If you need per-user queries, that data belongs in a relational table, not in the metric stream.

Operating a TSDB Day to Day

Four numbers deserve a dashboard of their own:

• Series cardinality trend — the earliest warning of a tagging mistake; graph it weekly
• Ingested points per second and rejected batches — rejections spike when clients send the wrong timestamp precision or oversized payloads
• Disk usage per retention tier — raw data should decay predictably; when it doesn’t, retention is misaligned with the chunk interval
• Continuous aggregate lag — when the refresh falls behind, dashboards silently serve stale aggregates

Wire them into your metrics pipeline like any other service — the observability guide covers that side of the setup.

Backups follow the engine’s grain. TimescaleDB keeps you inside PostgreSQL — pg_dump, WAL archiving, and point-in-time recovery all work as usual. InfluxDB ships its own backup tooling, and the open-source edition snapshots per database rather than the whole cluster. ClickHouse has BACKUP ... TO Disk() plus clickhouse-backup for S3 targets — test your restores, because a MergeTree backup is only as trustworthy as its part metadata.

Upgrades differ too: a TimescaleDB upgrade is ALTER EXTENSION timescaledb UPDATE plus a sanity check on your continuous aggregates, while a ClickHouse cluster wants a rolling, node-by-node upgrade with the Distributed table absorbing the gap.

Troubleshooting

• Memory usage explodes after adding a tag: classic cardinality explosion — check the tag’s distinct count, drop it, and rebuild the index. In InfluxDB use SHOW SERIES CARDINALITY; in TimescaleDB inspect chunk row estimates.
• Queries scan far more data than expected: chunk interval is misaligned with query windows — a hypertable chunked at 1 day forces full-day scans for 10-minute dashboards. Recreate with an interval matching your most common range, or add a continuous aggregate.
• Continuous aggregates fall behind: the refresh policy window is too small for write bursts, or a batch backfill landed outside the refresh interval. Widen the window or refresh explicitly with CALL refresh_continuous_aggregate(...).
• Points silently dropped on ingest: malformed line protocol, timestamps in the wrong unit (seconds vs nanoseconds), or write batch too large. Log rejected batches and validate precision before blaming the engine.
• Retention policy does not free disk: retention drops chunks, not rows — if the chunk contains any non-expired data it survives. Align chunk interval with retention period so whole chunks expire together.

Common Mistakes

• Using a TSDB for transactional workloads — no ACID guarantees, no updates, no referential integrity
• High-cardinality tags — too many unique tag values explode memory and index size (see above)
• No retention policy — time-series data grows indefinitely; always set retention or archiving
• Wrong timestamp precision — nanosecond precision is often overkill and wastes storage
• Backfilling through the hot path — bulk historical loads need batching and (in TimescaleDB) a temporarily relaxed refresh window
• Copying a comparison table as a decision — the right engine depends on your existing stack, not benchmark headlines

Advanced Topics

Detailed Scenario: IoT Monitoring with TimescaleDB

A system monitoring 10,000 IoT sensors on TimescaleDB over PostgreSQL: 100,000 readings per second, 8.6 billion readings a day, real-time alerts, historical dashboards, and downsampling. Runnable versions of this setup live in the companion repo.

Schema and hypertable — 6-hour chunks aligned with the dashboard query windows:

CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE sensor_readings (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INT NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    pressure DOUBLE PRECISION,
    battery_level DOUBLE PRECISION
);

SELECT create_hypertable('sensor_readings', 'time',
    chunk_time_interval => INTERVAL '6 hours');

CREATE INDEX idx_sensor_time ON sensor_readings (sensor_id, time DESC);

Simulated burst ingest of 10,000 readings — reaches roughly 100K rows/sec on commodity hardware:

INSERT INTO sensor_readings (time, sensor_id, temperature, humidity, pressure)
SELECT
    NOW() - (generate_series(1, 10000) * INTERVAL '1 second'),
    (random() * 10000)::INT,
    20 + random() * 15,
    40 + random() * 20,
    1013 + random() * 10;

Continuous aggregates handle the downsampling tiers:

CREATE MATERIALIZED VIEW sensor_1m
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 minute', time) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp,
    AVG(humidity) AS avg_humidity
FROM sensor_readings
GROUP BY bucket, sensor_id
WITH NO DATA;

CREATE MATERIALIZED VIEW sensor_1h
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp,
    COUNT(*) AS sample_count
FROM sensor_readings
GROUP BY bucket, sensor_id
WITH NO DATA;

Retention per tier — raw expires in a week, minute aggregates carry the quarter, hourly covers the long tail:

SELECT add_retention_policy('sensor_readings', INTERVAL '7 days');
SELECT add_retention_policy('sensor_1m', INTERVAL '90 days');
SELECT add_retention_policy('sensor_1h', INTERVAL '5 years');

The queries that run against it:

-- Latest reading per sensor
SELECT DISTINCT ON (sensor_id)
    sensor_id, time, temperature, humidity
FROM sensor_readings
ORDER BY sensor_id, time DESC;

-- Alert: sensors over 35 C in the last 10 minutes
SELECT sensor_id, MAX(temperature) AS max_temp
FROM sensor_readings
WHERE time > NOW() - INTERVAL '10 minutes'
  AND temperature > 35
GROUP BY sensor_id
ORDER BY max_temp DESC;

-- Hourly temperature trend for one sensor, last 24 hours
SELECT bucket, sensor_id, avg_temp, max_temp, min_temp
FROM sensor_1h
WHERE bucket > NOW() - INTERVAL '24 hours'
  AND sensor_id = 42
ORDER BY bucket;

Tier	Data	Retention	Size
Raw	All readings	7 days	50 GB
1-min	Per-minute aggregates	90 days	5 GB
1-hour	Per-hour aggregates	5 years	1 GB

Measured performance on this shape of data:

Query	Time	Rows scanned
Latest reading (1 sensor)	2 ms	1
Real-time alerts (10 min)	15 ms	~6 M
24 h dashboard (1 sensor)	8 ms	24 (from aggregate)
30-day dashboard (all sensors)	120 ms	720 (from aggregate)

Lessons: TimescaleDB gives you TSDB-class ingestion without leaving PostgreSQL, continuous aggregates remove the pre-computation jobs, tiered retention keeps the storage bill sane, and chunk_time_interval has to line up with the query windows you actually run.

The scenario mirrors the retention pipeline diagrammed above: raw points expire after a week, minute aggregates carry the quarter, and hourly aggregates cover the long tail — 56 GB of source data compressed to roughly 1 GB of queryable history.

Scaling Beyond One Node

Single-node setups cover most workloads, but ingestion in the millions of points per second or retention in petabytes eventually forces a cluster — and each engine scales differently:

• TimescaleDB stays single-node: scale up, add read replicas for query load, and use tiered storage for capacity. The old multi-node feature is deprecated in favor of PostgreSQL-native partitioning plus replicas.
• InfluxDB handles clustering in InfluxDB Cloud and Enterprise — the open-source core runs on one node, so horizontal scale means the managed offering or a fronting collector tier that shards by measurement.
• ClickHouse scales natively: ReplicatedMergeTree for redundancy plus Distributed tables for sharding across nodes — the steepest operational curve of the three, and the reason teams adopt it only when volume demands it.

If you’re designing for a cluster from day one, write the schema so partitions and shards align with the dimensions you’ll actually shard by — retrofitting a sharding key is painful.

See Also

• TimescaleDB documentation — hypertables, continuous aggregates, retention policies
• InfluxDB documentation — line protocol, Flux, and retention behavior
• ClickHouse documentation — MergeTree engines and partitioning
• Complete Guide to Redis Caching Strategies — when a cache, not a TSDB, is the right answer
• Metrics Aggregation Pattern — the pre-aggregation trade-offs behind downsampling