TimescaleDB is a PostgreSQL extension that turns PostgreSQL into a capable time-series database. You keep full SQL, joins with your regular tables, PostgreSQL tooling and backups — and gain automatic time partitioning, fast time-bucketed queries, compression and retention policies.

It suits metrics, IoT sensor data, financial ticks, application events and anything else that’s mostly “append rows with a timestamp, query by time range”.

Licensing: core features are Apache 2.0; advanced features (compression, continuous aggregates, policies) are under the Timescale License, free to self-host. Check the current terms for your use.

1. Install

TimescaleDB provides packages for PostgreSQL versions it supports. On Ubuntu/Debian:

sudo apt install -y gnupg postgresql-common apt-transport-https lsb-release wget
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh    # PostgreSQL's own repository
echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/timescaledb.gpg
sudo apt update
sudo apt install -y timescaledb-2-postgresql-17

(Match the package to your PostgreSQL major version; see Timescale’s docs for current instructions.)

Tune and enable the extension library:

sudo timescaledb-tune --quiet --yes
sudo systemctl restart postgresql

timescaledb-tune adds timescaledb to shared_preload_libraries and suggests memory settings based on your RAM.

Docker alternative: the timescale/timescaledb:latest-pg17 image.

2. Enable it in a database

sudo -u postgres psql -d metrics
CREATE EXTENSION IF NOT EXISTS timescaledb;
\dx

3. Create a hypertable

Create a normal table, then convert it:

CREATE TABLE sensor_data (
    time        TIMESTAMPTZ NOT NULL,
    sensor_id   INTEGER     NOT NULL,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION
);

SELECT create_hypertable('sensor_data', by_range('time', INTERVAL '1 day'));
CREATE INDEX ON sensor_data (sensor_id, time DESC);

A hypertable looks like one table but is split into chunks by time behind the scenes. Queries on recent time ranges touch only the relevant chunks, and old chunks can be compressed or dropped cheaply.

4. Insert and query

INSERT INTO sensor_data VALUES (now(), 1, 21.7, 48.2), (now(), 2, 19.9, 52.0);

-- 15-minute averages per sensor for the last day
SELECT time_bucket('15 minutes', time) AS bucket, sensor_id,
       avg(temperature) AS avg_temp, max(temperature) AS max_temp
FROM sensor_data
WHERE time > now() - INTERVAL '1 day'
GROUP BY bucket, sensor_id
ORDER BY bucket DESC;

time_bucket() is the workhorse for dashboards. Batch inserts (multi-row INSERT or COPY) for high ingest rates.

5. Continuous aggregates

Pre-compute rollups that refresh automatically — dashboards over months of data stay fast:

CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket, sensor_id,
       avg(temperature) AS avg_temp, count(*) AS samples
FROM sensor_data
GROUP BY bucket, sensor_id;

SELECT add_continuous_aggregate_policy('sensor_hourly',
  start_offset => INTERVAL '3 days', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '30 minutes');

6. Compression

Older chunks can be compressed (often 90%+ smaller):

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

7. Retention

Drop raw data automatically after a period, keeping the aggregates:

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

Backups and upgrades

Standard PostgreSQL tools work: pg_dump (with some care around TimescaleDB’s catalog — follow Timescale’s backup docs) or physical backups with pgBackRest. Upgrade the extension with ALTER EXTENSION timescaledb UPDATE; after installing a new package version.

Visualise

Grafana has a native PostgreSQL data source with TimescaleDB support (time-bucket macros). See Grafana and Prometheus.

TimescaleDB vs InfluxDB

TimescaleDB: full SQL, joins with relational data, PostgreSQL ecosystem. InfluxDB: purpose-built, simple ingestion, its own query languages. See time-series databases and PostgreSQL performance tuning.

  • TimescaleDB
  • PostgreSQL
  • Time Series