丁久

Posted on May 17 • Originally published at dingjiu1989-hue.github.io

Time-Series with PostgreSQL: TimescaleDB, Hypertables, and Aggregates

#database #backend #data

This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Time-Series with PostgreSQL: TimescaleDB, Hypertables, and Aggregates

Time-series data powers monitoring systems, IoT applications, financial tick data, and analytics pipelines. PostgreSQL with TimescaleDB offers a robust solution that combines SQL power with time-series optimizations.

The Time-Series Challenge

Time-series workloads differ from traditional OLTP:

Append-heavy : Most data is inserted, rarely updated.
Time-ordered : Queries filter on time ranges.
Downsampling : Old data is aggregated and retained at lower granularity.
Retention : Data older than a threshold is dropped automatically.

Hypertables

TimescaleDB's central abstraction is the hypertable, which automatically partitions data by time:

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Enable the extension

CREATE EXTENSION IF NOT EXISTS timescaledb;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Create a regular table

CREATE TABLE sensor_readings (

time TIMESTAMPTZ NOT NULL,

sensor_id INTEGER NOT NULL,

temperature DOUBLE PRECISION,

humidity DOUBLE PRECISION,

pressure DOUBLE PRECISION

);

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Convert to hypertable, partitioned by time

SELECT create_hypertable('sensor_readings', 'time',

chunk_time_interval => INTERVAL '1 day');

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Optional: space partition by sensor_id for parallel I/O

SELECT add_dimension('sensor_readings',

create_hypertable_index('sensor_readings', 'sensor_id', 4));

TimescaleDB automatically creates chunks (internal partitions), each covering one day of data. Queries that filter on time prune irrelevant chunks, similar to declarative partitioning.

Inserting Data

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Inserts work exactly as with regular tables

INSERT INTO sensor_readings (time, sensor_id, temperature, humidity, pressure)

SELECT

generate_series('2026-01-01', '2026-05-12', INTERVAL '1 minute'),

(random() * 100)::INTEGER + 1,

random() * 35 + 5,

random() * 60 + 20,

random() * 50 + 950;

Querying Data

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Time-range queries prune chunks automatically

SELECT time, temperature

FROM sensor_readings

WHERE sensor_id = 42

AND time BETWEEN '2026-05-10' AND '2026-05-11'

ORDER BY time;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Chunk pruning visible in explain plan

EXPLAIN (ANALYZE, BUFFERS)

SELECT avg(temperature) FROM sensor_readings

WHERE time > now() - INTERVAL '1 hour';

Continuous Aggregates

Continuous aggr

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.

DEV Community

Time-Series with PostgreSQL: TimescaleDB, Hypertables, and Aggregates

Time-Series with PostgreSQL: TimescaleDB, Hypertables, and Aggregates