--- title: "How to Install TimescaleDB on Ubuntu 24.04 with PostgreSQL" description: "Install TimescaleDB on PostgreSQL 17 in Ubuntu 24.04, create a hypertable and set up continuous aggregates, compression and retention for time-series data." category: "Database Advanced" language: en source: https://cubepath.com/docs/database-advanced/timescaledb-installation-for-time-series-data updated: 2026-09-25 --- # How to Install TimescaleDB on Ubuntu 24.04 with PostgreSQL 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: ```bash 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`: ```bash 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`: ```bash 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: ```bash sudo apt update apt-cache policy timescaledb-2-postgresql-17 ``` ```output 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: ```bash 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: ```bash sudo timescaledb-tune --quiet --yes ``` The tool edits `/etc/postgresql/17/main/postgresql.conf`. Restart PostgreSQL to apply the changes: ```bash sudo systemctl restart postgresql ``` Confirm that the library is preloaded: ```bash sudo -u postgres psql -c "SHOW shared_preload_libraries;" ``` ```output 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: ```bash sudo -u postgres psql ``` ```sql 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`: ```sql \dx timescaledb ``` ```output 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: ```sql 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: ```sql SELECT create_hypertable('conditions', by_range('time', INTERVAL '1 day')); ``` ```output 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: ```sql 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: ```sql 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; ``` ```output INSERT 0 432010 ``` Check how many chunks were created: ```sql SELECT count(*) FROM show_chunks('conditions'); ``` ```output 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: ```sql 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; ``` ```output 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: ```sql 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: ```sql 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: ```sql CALL refresh_continuous_aggregate('conditions_hourly', NULL, NULL); ``` Query it like any other view: ```sql 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; ``` ```output 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: ```sql 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: ```sql 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: ```sql 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: ```sql SELECT pg_size_pretty(before_compression_total_bytes) AS before, pg_size_pretty(after_compression_total_bytes) AS after FROM hypertable_compression_stats('conditions'); ``` ```output before | after --------+------- 41 MB | 2976 kB (1 row) ``` > Note: Recent TimescaleDB releases also call this feature the "columnstore" and offer equivalent `add_columnstore_policy` and `convert_to_columnstore` functions. The compression API used here remains supported. ## 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: ```sql 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: ```sql 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; ``` ```output 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.