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

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 aggregates are TimescaleDB's answer to materialized views for time-series. They automatically and incrementally maintain pre-computed aggregations:

-- Create a continuous aggregate

CREATE MATERIALIZED VIEW hourly_stats

WITH (timescaledb.continuous) AS

SELECT

time_bucket('1 hour', time) AS bucket,

sensor_id,

COUNT(*) AS readings,

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;

-- Set refresh policy: refresh every hour, keep 7 days of data

SELECT add_continuous_aggregate_policy('hourly_stats',

start_offset => INTERVAL '3 days',

end_offset => INTERVAL '1 hour',

schedule_interval => INTERVAL '1 hour'

);

Continuous aggregates update incrementally: only the time buckets that have new data are recomputed. This makes them viable for real-time dashboards.

-- Query the continuous aggregate

SELECT bucket, avg_temp, max_temp

FROM hourly_stats

WHERE sensor_id = 42

AND bucket >= now() - INTERVAL '7 days'

ORDER BY bucket;

Data Retention

Time-series data grows indefinitely without a retention policy. TimescaleDB provides native retention policies:

-- Drop chunks older than 90 days

SELECT add_retention_policy('sensor_readings',

drop_after => INTERVAL '90 days');

The drop operation is nearly instant because it removes entire chunks rather than individual rows.

-- Manual chunk management

SELECT show_chunks('sensor_readings');

SELECT drop_chunks('sensor_readings', older_than => INTERVAL '90 days');

SELECT reorder_chunk('_hyper_1_1_chunk', 'sensor_readings_time_idx');

Compression

TimescaleDB's native compression is designed for time-series data:

-- Enable compression

ALTER TABLE sensor_readings SET (

timescaledb.compress,

timescaledb.compress_segmentby = 'sensor_id',

timescaledb.compress_orderby = 'time DESC'

);

-- Set compression policy: compress chunks older than 7 days

SELECT add_compression_policy('sensor_readings',

compress_after => INTERVAL '7 days');

TimescaleDB reports 90-95% compression ratios for typical sensor data because consecutive readings from the same sensor are highly correlated.

Performance Optimization

-- Index for typical queries

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

-- Use timescaledb toolkit extension for advanced analytics

CREATE EXTENSION timescaledb_toolkit;

-- Approximate percentile

SELECT

time_bucket('1 hour', time) AS bucket,

approx_percentile(0.95, percentile_agg(temperature)) AS p95_temp

FROM sensor_readings

WHERE sensor_id = 42

GROUP BY bucket;

PostgreSQL vs Dedicated Time-Series Databases

| Feature | TimescaleDB | InfluxDB | ClickHouse | |---------|------------|----------|------------| | Query language | Full SQL | Flux | SQL-like | | Joins | Full support | Limited | Moderate | | ACID transactions | Yes | No | Limited | | Compression ratio | 90-95% | 90-95% | 90-99% | | Ingestion rate | 1M+ rows/sec | 1M+ rows/sec | 10M+ rows/sec | | Ecosystem | PostgreSQL ecosystem | Grafana mainly | Analytics tools |

TimescaleDB is the right choice when you need:

* Full SQL and JOIN capabilities across time-series and relational data.

* ACID guarantees for insert patterns.

* Existing PostgreSQL tooling (PgBouncer, pgBadger, ORMs).

* A single database for both transactional and time-series workloads.

The combination of hypertables, continuous aggregates, and compression makes PostgreSQL with TimescaleDB a compelling default choice for time-series data that coexists with relational data.