Time-series databases: metrics, and why they are different

Some data is a stream of measurements stamped with a time: a server's CPU every second, a sensor's temperature every minute, a stock price every tick, a smart meter's reading every fifteen minutes. This is time-series data, and it is different enough that a family of databases exists just for it — InfluxDB, TimescaleDB (a PostgreSQL extension), Prometheus, and others.

What makes time-series data special

It is not just "data with a timestamp column". It has a distinctive shape that breaks the assumptions a general database is tuned for:

• Append-only, effectively. New measurements arrive constantly; old ones are essentially never updated. A temperature reading from last Tuesday does not change.
• Enormous volume. A thousand sensors reporting every second is 86 million rows a day, billions a month. Time-series is often the highest-volume data an organisation has.
• Time-ordered writes and time-ranged reads. You almost always write "now" and query "a time range" — the last hour, yesterday, this month.
• Recent data matters most, and value fades with age. You query the last hour constantly and last year rarely; you often only need last year summarised, not every raw point.
• Queries are overwhelmingly aggregations over time. "Average CPU per minute over the last day", "max temperature per hour" — downsampling a fine stream into coarse buckets.

A general-purpose relational table can hold this, and for modest volumes it is fine. But at scale the mismatch bites: the table grows without bound, time-bucketed aggregates scan enormous ranges, indexes balloon, and you write your own deletion job for old data.

What a time-series database does about it

Purpose-built engines exploit the shape:

Time-based partitioning automatically. Data is split into chunks by time (a chunk per day, per week), so a query for "yesterday" touches one chunk and ignores the rest, and dropping old data is dropping whole chunks — instant, versus a slow DELETE of billions of rows. TimescaleDB calls these hypertables; it is the partitioning idea from module 8, automated for time.

Heavy compression. Time-series compresses extremely well — consecutive readings are similar, so storing the deltas is tiny. 90%+ compression is normal, which is the difference between affordable and not at billions of rows.

Fast time-bucketed aggregation. A first-class "bucket by 5 minutes and average" operation, far faster than a general GROUP BY on a truncated timestamp, often with continuous aggregates — rollups kept up to date automatically (the materialised-view idea from module 8, specialised for time).

Downsampling and retention built in. Keep raw data for a week, five-minute averages for a month, hourly for a year, then delete — expressed as a policy, not a cron job. This matches how the value of the data actually decays.

The specialised options, briefly

• TimescaleDB — a PostgreSQL extension. This is the one to reach for first, because it is still PostgreSQL: your SQL, your joins to relational tables, your tooling, your one system — plus hypertables, compression and continuous aggregates. For most teams that already run PostgreSQL, it is the whole answer, and it embodies this course's recurring "stay in one system" advice.
• InfluxDB — a dedicated time-series database with its own query languages, popular for metrics and IoT; strong when time-series is your whole workload.
• Prometheus — purpose-built for monitoring: it scrapes metrics from services and stores them, and is the default for infrastructure and application metrics (usually paired with Grafana for dashboards). A slightly different niche — operational monitoring — but the same underlying time-series shape.

Recognising the need

You have a time-series workload when: data is append-only measurements over time; volume is large and growing continuously; queries are time-ranged aggregations; and recent data matters more than old. Concretely — IoT and sensor telemetry, application and infrastructure metrics, financial ticks, analytics events, smart-meter and industrial data.

The progression, in this course's spirit:

1. A plain PostgreSQL table, well-indexed on the timestamp, with native partitioning by time (module 8). This carries you a long way and is the right start.
2. TimescaleDB when the volume, the retention management, or the aggregation speed outgrows a plain table — and you get it without leaving PostgreSQL.
3. A dedicated engine (InfluxDB, Prometheus) when time-series is the product or the workload, at a scale and specialisation that justify a separate system.

Do not reach for a specialised time-series database on day one. A partitioned PostgreSQL table handles more than most projects will ever generate, and TimescaleDB extends that without a second system. The mistake, as always, is adopting heavy specialised infrastructure for scale you have not reached.

Check your work

What time-series data is. A stream of time-stamped measurements — append-only, high-volume, queried by time range.

Five properties that make it special. Append-only, enormous volume, time-ordered writes and time-ranged reads, recent data matters most, and queries are aggregations over time.

Why a plain table struggles at scale. Unbounded growth, huge range scans for aggregates, ballooning indexes, and manual deletion of old data.

Time-based partitioning, and what it buys. Data split into time chunks, so a time-ranged query touches few chunks and dropping old data is dropping whole chunks (instant).

Why time-series compresses so well. Consecutive readings are similar, so storing deltas is tiny — 90%+ is normal.

Continuous aggregates. Time-bucketed rollups kept automatically up to date — materialised views specialised for time.

What downsampling and retention policies express. Keep raw briefly, coarser summaries longer, then delete — matching how the data's value decays.

Why TimescaleDB is the first specialised step. It is a PostgreSQL extension — same SQL, joins, tooling and single system, plus hypertables, compression and continuous aggregates.

What Prometheus is for specifically. Monitoring — scraping and storing service/infrastructure metrics, usually with Grafana.

The recommended progression. Partitioned PostgreSQL table → TimescaleDB → dedicated engine, each on evidence.

Practice

1. Estimate the daily and monthly row count for 1,000 sensors reporting every second. Note when a single table becomes a problem.
2. Create a plain PostgreSQL table for readings and write the "average per 5-minute bucket over the last day" query with date_trunc and GROUP BY.
3. Add native range partitioning by day and confirm a one-day query prunes to one partition with EXPLAIN.
4. Reason about deleting a month of old data as a DELETE versus dropping partitions.
5. Describe a retention policy for metrics: what resolution would you keep at one week, one month, one year?
6. Explain why delta compression works so well on consecutive sensor readings.
7. Decide, for an application you know, whether any data is genuinely time-series, and which tier (plain table, TimescaleDB, dedicated engine) fits.
8. Look at how your own team's metrics are stored (likely Prometheus) and identify the time-series properties in play.

Official documentation

• TimescaleDB — Documentation — Hypertables, compression and continuous aggregates on PostgreSQL.
• PostgreSQL — Table partitioning — Native time partitioning, the first step.
• InfluxDB — Documentation — The dedicated time-series database.
• Prometheus — Documentation — The monitoring-focused time-series system.
• Grafana — Documentation — Dashboards over time-series sources.

Next: vector databases and embeddings, the newest family.