TimescaleDB is a PostgreSQL extension for time-series data such as metrics, IoT readings or events. It stores rows in hypertables that are split automatically into time-based chunks, and adds functions for time bucketing, incrementally refreshed aggregates, columnar compression and automatic data retention, while you keep using normal SQL. In this tutorial you will install TimescaleDB on PostgreSQL 17 on Ubuntu 24.04, load sample sensor data into a hypertable and set up the policies that keep a time-series database fast and small over time.

Prerequisites

To follow this tutorial, you will need:

  • A server running Ubuntu 24.04 LTS, for example a CubePath VPS, with at least 2 GB of RAM.
  • A non-root user with sudo privileges.
  • No existing PostgreSQL installation you depend on, or one you are ready to upgrade. This guide installs PostgreSQL 17 from the official PostgreSQL repository.

Step 1 - Adding the PostgreSQL and TimescaleDB repositories

Ubuntu 24.04 ships PostgreSQL 16, but TimescaleDB packages are built against the packages from the official PostgreSQL (PGDG) repository, so you will use both repositories.

Install the helper package that sets up the PGDG repository, and run its script. It adds the repository with its signing key and asks you to press Enter to continue:

sudo apt update
sudo apt install postgresql-common curl gnupg
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Next, add the TimescaleDB repository. Download its signing key into /etc/apt/keyrings:

sudo install -m 0755 -d /etc/apt/keyrings
curl -fsSL https://packagecloud.io/timescale/timescaledb/gpgkey | sudo gpg --dearmor -o /etc/apt/keyrings/timescaledb.gpg

Create the repository file. The Ubuntu codename (noble) is read from /etc/os-release:

echo "deb [signed-by=/etc/apt/keyrings/timescaledb.gpg] https://packagecloud.io/timescale/timescaledb/ubuntu/ $(. /etc/os-release && echo "$VERSION_CODENAME") main" | sudo tee /etc/apt/sources.list.d/timescaledb.list

Update the package index and check that the TimescaleDB package is available:

sudo apt update
apt-cache policy timescaledb-2-postgresql-17

timescaledb-2-postgresql-17:
  Installed: (none)
  Candidate: 2.x.x~ubuntu24.04
  Version table:
     2.x.x~ubuntu24.04 500
        500 https://packagecloud.io/timescale/timescaledb/ubuntu noble/main amd64 Packages

Step 2 - Installing TimescaleDB

Install the extension for PostgreSQL 17. The package pulls in PostgreSQL 17 and the timescaledb-tools package:

sudo apt install timescaledb-2-postgresql-17 postgresql-client-17

TimescaleDB must be loaded when PostgreSQL starts, through shared_preload_libraries. The timescaledb-tune tool adds it and also adjusts memory, worker and WAL settings to the server's CPU and RAM:

sudo timescaledb-tune --quiet --yes

The tool edits /etc/postgresql/17/main/postgresql.conf. Restart PostgreSQL to apply the changes:

sudo systemctl restart postgresql

Confirm that the library is preloaded:

sudo -u postgres psql -c "SHOW shared_preload_libraries;"

 shared_preload_libraries
--------------------------
 timescaledb
(1 row)

If you run timescaledb-tune without --yes, it shows each proposed change and asks for confirmation, which is useful to review what it modifies.

Step 3 - Creating a database with the extension

The extension is enabled per database. Create a database called metrics and a user that owns it. Replace your_strong_password with a real password:

sudo -u postgres psql

CREATE USER metrics_user WITH PASSWORD 'your_strong_password';
CREATE DATABASE metrics OWNER metrics_user;
\c metrics
CREATE EXTENSION IF NOT EXISTS timescaledb;

The extension prints a banner the first time it is created. Check that it is installed with \dx:

\dx timescaledb

                                                List of installed extensions
    Name     | Version | Schema |                                      Description
-------------+---------+--------+---------------------------------------------------------------------------------------
 timescaledb | 2.x.x   | public | Enables scalable inserts and complex queries for time-series data (Community Edition)
(1 row)

Stay connected to the metrics database as postgres for the rest of the tutorial. In a real application, create the tables as metrics_user.

Step 4 - Creating a hypertable

A hypertable looks like a normal table, but TimescaleDB stores its rows in chunks, each covering a range of time. Queries that filter by time only read the chunks they need, and old chunks can be compressed or dropped as a whole.

Create a regular table for sensor readings:

CREATE TABLE conditions (
  time        TIMESTAMPTZ      NOT NULL,
  device_id   INTEGER          NOT NULL,
  temperature DOUBLE PRECISION,
  humidity    DOUBLE PRECISION
);

Convert it into a hypertable partitioned by the time column, with one chunk per day:

SELECT create_hypertable('conditions', by_range('time', INTERVAL '1 day'));

 create_hypertable
-------------------
 (1,t)
(1 row)

Choose the chunk interval so that the chunks of recent data fit comfortably in memory. One day works well for this example. If you omit the interval, TimescaleDB uses seven days.

TimescaleDB creates an index on time automatically. Add one for the queries you run most often, typically by device and time:

CREATE INDEX ON conditions (device_id, time DESC);

Step 5 - Loading and querying sample data

Insert 30 days of readings, one per minute, for 10 devices:

INSERT INTO conditions (time, device_id, temperature, humidity)
SELECT t, d, 20 + random() * 10, 40 + random() * 20
FROM generate_series(now() - INTERVAL '30 days', now(), INTERVAL '1 minute') AS t,
     generate_series(1, 10) AS d;

INSERT 0 432010

Check how many chunks were created:

SELECT count(*) FROM show_chunks('conditions');

 count
-------
    31
(1 row)

The most useful TimescaleDB function for queries is time_bucket, which groups timestamps into fixed intervals. This query returns the hourly average temperature of one device over the last day:

SELECT time_bucket('1 hour', time) AS hour,
       round(avg(temperature)::numeric, 2) AS avg_temp
FROM conditions
WHERE device_id = 1
  AND time > now() - INTERVAL '1 day'
GROUP BY hour
ORDER BY hour DESC
LIMIT 5;

          hour          | avg_temp
------------------------+----------
 2026-09-25 10:00:00+00 |    25.12
 2026-09-25 09:00:00+00 |    24.87
 2026-09-25 08:00:00+00 |    25.31
 2026-09-25 07:00:00+00 |    24.69
 2026-09-25 06:00:00+00 |    25.04
(5 rows)

Prefix a query with EXPLAIN and you will see that only the chunks for the last day are scanned.

Step 6 - Creating a continuous aggregate

Dashboards often run the same aggregation over long periods. A continuous aggregate is a materialized view that TimescaleDB refreshes incrementally, recomputing only the time buckets whose data changed.

Create an hourly aggregate per device:

CREATE MATERIALIZED VIEW conditions_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       device_id,
       avg(temperature) AS avg_temp,
       max(temperature) AS max_temp,
       min(temperature) AS min_temp
FROM conditions
GROUP BY bucket, device_id
WITH NO DATA;

Add a policy that refreshes it every hour. It recomputes buckets between 3 days and 1 hour ago; the most recent hour is left out because it is still receiving data:

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

The policy only covers the last 3 days, so fill the view once with all existing data:

CALL refresh_continuous_aggregate('conditions_hourly', NULL, NULL);

Query it like any other view:

SELECT bucket, round(avg_temp::numeric, 2) AS avg_temp, round(max_temp::numeric, 2) AS max_temp
FROM conditions_hourly
WHERE device_id = 1
ORDER BY bucket DESC
LIMIT 3;

         bucket         | avg_temp | max_temp
------------------------+----------+----------
 2026-09-25 09:00:00+00 |    24.87 |    29.93
 2026-09-25 08:00:00+00 |    25.31 |    29.98
 2026-09-25 07:00:00+00 |    24.69 |    29.85
(3 rows)

By default, the view only returns materialized data. If you also want the most recent, not yet materialized buckets computed on the fly, enable real-time aggregation with ALTER MATERIALIZED VIEW conditions_hourly SET (timescaledb.materialized_only = false);.

Step 7 - Enabling compression

TimescaleDB can convert older chunks into a compressed columnar format, which usually reduces their size by 90% or more and speeds up analytical queries over them. Compressed data can still be queried normally.

Enable compression on the hypertable. segmentby groups the compressed data by device, which suits queries that filter on device_id, and orderby sorts it by time inside each segment:

ALTER TABLE conditions SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id',
  timescaledb.compress_orderby   = 'time DESC'
);

Add a policy that compresses chunks once they are older than 7 days:

SELECT add_compression_policy('conditions', INTERVAL '7 days');

The policy runs in the background. To see the effect right away, compress the eligible chunks manually:

SELECT compress_chunk(c, if_not_compressed => true)
FROM show_chunks('conditions', older_than => INTERVAL '7 days') AS c;

Compare the size before and after:

SELECT pg_size_pretty(before_compression_total_bytes) AS before,
       pg_size_pretty(after_compression_total_bytes)  AS after
FROM hypertable_compression_stats('conditions');

 before | after
--------+-------
 41 MB  | 2976 kB
(1 row)

Step 8 - Adding a retention policy

Raw time-series data rarely needs to be kept forever. A retention policy drops whole chunks older than a given age, which is much cheaper than running DELETE. Keep 90 days of raw readings:

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

The continuous aggregate keeps its own data, so hourly summaries remain available after the raw rows are dropped. Make sure the aggregate's refresh window (start_offset, 3 days here) is shorter than the retention period, otherwise a refresh could recompute buckets from data that no longer exists.

Step 9 - Checking background jobs

All policies run as background jobs. List them and see when they last ran:

SELECT j.job_id, j.proc_name, j.hypertable_name, j.schedule_interval, s.last_run_status, s.next_start
FROM timescaledb_information.jobs j
JOIN timescaledb_information.job_stats s USING (job_id)
ORDER BY j.job_id;

 job_id |              proc_name              |      hypertable_name       | schedule_interval | last_run_status |          next_start
--------+-------------------------------------+----------------------------+-------------------+-----------------+-------------------------------
   1000 | policy_refresh_continuous_aggregate | _materialized_hypertable_2 | 01:00:00          | Success         | 2026-09-25 11:12:03+00
   1001 | policy_compression                  | conditions                 | 12:00:00          | Success         | 2026-09-25 22:14:10+00
   1002 | policy_retention                    | conditions                 | 1 day             | Success         | 2026-09-26 10:15:22+00
(3 rows)

A job with Failed as its last status is recorded in timescaledb_information.job_errors, which shows the error message. You can also run a job immediately with CALL run_job(job_id);.

Troubleshooting

  • extension "timescaledb" must be preloaded: shared_preload_libraries does not contain timescaledb, or PostgreSQL was not restarted. Run timescaledb-tune again or add it to /etc/postgresql/17/main/postgresql.conf, then sudo systemctl restart postgresql.
  • could not open extension control file when running CREATE EXTENSION: the database is running on a PostgreSQL version without the matching package, for example PostgreSQL 16 from Ubuntu. Check with pg_lsclusters and install timescaledb-2-postgresql-16 or move to the version-17 cluster.
  • Inserts into compressed chunks are slow: writes to old, compressed time ranges are supported but more expensive. Set the compression policy's age so that late-arriving data lands in chunks that are not compressed yet.

Conclusion

You installed TimescaleDB on PostgreSQL 17, turned a table into a hypertable and added a continuous aggregate, compression and retention, so recent data stays fast to query and old data takes little space. As next steps, connect a visualization tool such as Grafana through its PostgreSQL data source, create the tables and grants for your application's own role, and set up regular backups with pg_dump or pgBackRest.