How to store and query time-series data with TimescaleDB

What and why

TimescaleDB extends PostgreSQL for time-series data. It partitions tables automatically into chunks by time (hypertables), accelerates rollups with continuous aggregates, and reduces storage with native compression. You keep full SQL and the PostgreSQL ecosystem while handling high-ingest metric workloads. This tutorial sets up a metrics table end to end.

Prerequisites

• PostgreSQL with the TimescaleDB extension available.
• Time-stamped data such as sensor readings or application metrics.
• A SQL client.

Steps

1. Enable TimescaleDB

CREATE EXTENSION IF NOT EXISTS timescaledb;

2. Create a hypertable

Create a normal table, then convert it:

CREATE TABLE metrics (
  time TIMESTAMPTZ NOT NULL,
  device_id TEXT,
  value DOUBLE PRECISION
);
SELECT create_hypertable('metrics', 'time');

TimescaleDB now partitions metrics into time chunks transparently.

3. Insert metric data

Insert as you would any table; writes route to the right chunk:

INSERT INTO metrics VALUES (now(), 'sensor-1', 23.5);

4. Add a continuous aggregate

Precompute hourly rollups that refresh incrementally:

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       device_id, avg(value) AS avg_value
FROM metrics GROUP BY bucket, device_id;

Add a refresh policy so it stays current automatically.

5. Enable compression

Compress older chunks to save space:

ALTER TABLE metrics SET (timescaledb.compress, timescaledb.compress_segmentby = 'device_id');
SELECT add_compression_policy('metrics', INTERVAL '7 days');

6. Set a retention policy

Drop data past a retention window automatically:

SELECT add_retention_policy('metrics', INTERVAL '90 days');

Verification

Insert data and query metrics_hourly; aggregates should return quickly even over large ranges. SELECT * FROM timescaledb_information.chunks shows chunking, and compression and retention jobs appear in timescaledb_information.jobs.

Next Steps

Use time_bucket_gapfill for evenly spaced series, add space partitioning by device for very high cardinality, and connect Grafana for dashboards over the hypertable.