Time Series Data: InfluxDB, TimescaleDB, and Handling High-Cardinality Metrics

Editorial Team · · 3 min read

Time Series Data Is Different

Time series data — metrics, sensor readings, financial ticks, log events — behaves fundamentally differently from transactional data. Writes are append-only. Reads are almost always range-based (show me the last hour, the last week). Individual data points are rarely updated or deleted. And the volume can be staggering: a fleet of 10,000 IoT sensors reporting every second generates 864 million data points per day.

General-purpose databases can store time series data, but they struggle at scale. PostgreSQL with a timestamp index works fine for a few million rows. Past that, query times degrade, storage bloats, and maintenance windows grow.

TimescaleDB: PostgreSQL with Superpowers

TimescaleDB is a PostgreSQL extension that adds time series optimizations while keeping full SQL compatibility. If you're already running PostgreSQL, it's the easiest upgrade path.

The core concept is hypertables — tables automatically partitioned by time into chunks:

-- Install the extension
CREATE EXTENSION timescaledb;

-- Convert a regular table into a hypertable
CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    device_id TEXT NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    battery_pct SMALLINT
);

SELECT create_hypertable('metrics', 'time',
    chunk_time_interval => INTERVAL '1 day');

-- Create an index on device_id within each time chunk
CREATE INDEX idx_metrics_device ON metrics (device_id, time DESC);

Queries look like regular SQL, but they're fast because TimescaleDB prunes chunks that don't match the time range:

-- Average temperature per hour for the last 24 hours
SELECT time_bucket('1 hour', time) AS bucket,
       device_id,
       avg(temperature) AS avg_temp,
       max(temperature) AS max_temp
FROM metrics
WHERE time > now() - interval '24 hours'
AND device_id = 'sensor-42'
GROUP BY bucket, device_id
ORDER BY bucket;

Continuous aggregates are one of TimescaleDB's best features — they're materialized views that automatically refresh as new data arrives:

CREATE MATERIALIZED VIEW hourly_metrics
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS hour,
       device_id,
       avg(temperature) AS avg_temp,
       count(*) AS readings
FROM metrics
GROUP BY hour, device_id;

InfluxDB: Purpose-Built for Metrics

InfluxDB is designed exclusively for time series data. It uses its own query language (Flux, or the newer SQL-like InfluxQL) and a custom storage engine optimized for high-cardinality time series.

InfluxDB 3.0 (the latest major rewrite) switched to Apache Arrow and Parquet under the hood, which dramatically improved query performance and storage efficiency. It also added native SQL support.

# Write data using the line protocol
curl -X POST 'http://localhost:8086/api/v2/write?bucket=iot'   --header 'Authorization: Token my-token'   --data-raw 'temperature,device=sensor-42,location=warehouse value=22.5 1695216000000000000'

# Query with SQL (InfluxDB 3.0+)
SELECT time, value
FROM temperature
WHERE device = 'sensor-42'
AND time >= now() - interval '1 hour'

Strengths: Extremely fast ingestion (millions of points per second), efficient compression (typically 2-5 bytes per data point), built-in retention policies and downsampling, great for operational monitoring.

Weaknesses: The frequent major version changes (1.x → 2.x → 3.x) have broken backward compatibility each time. Community trust took a hit when they changed the open-source license. The custom query languages have a learning curve.

The High-Cardinality Problem

Here's the issue that trips up most time series deployments: high cardinality. Cardinality is the number of unique time series — unique combinations of metric name and tag values.

If you're monitoring CPU usage across 1,000 servers, that's 1,000 time series. Manageable. But if you add per-process metrics across those servers, and each server runs 200 processes, you're at 200,000 series. Add per-container metrics in Kubernetes, and you might hit millions of series.

High cardinality kills performance because:

  • Index memory grows linearly with series count
  • Query planning gets expensive when matching tag values
  • Write amplification increases as data gets distributed across more series

Mitigation strategies:

  • Don't put high-cardinality values in tags/labels. A user ID or request ID as a tag creates a new series per user/request — that's unbounded.
  • Use pre-aggregation. Instead of storing every HTTP request, store per-minute aggregates by endpoint and status code.
  • Set cardinality limits. Prometheus has a sample_limit per scrape target. InfluxDB has series cardinality limits.

Choosing Between Them

Use TimescaleDB when: you're already on PostgreSQL, you need to join time series data with relational data, your team knows SQL, or you need features like continuous aggregates and compression with full SQL.

Use InfluxDB when: you're building a dedicated monitoring/metrics platform, ingestion rate is your primary concern, or you want built-in dashboarding with Telegraf/Grafana integration out of the box.

Use Prometheus (which I haven't covered in depth but is worth mentioning) when: you're monitoring Kubernetes/infrastructure, you want a pull-based model, and your retention needs are under 30 days. Prometheus is fantastic for operational monitoring but not great for long-term analytics.

For most application developers, TimescaleDB is the safest choice. It's SQL, it works with your existing tools, and you can always query time series data alongside your application tables.