Install TimescaleDB on PostgreSQL

TimescaleDB turns PostgreSQL into a purpose-built time-series database without abandoning the SQL you already know. It ships as a PostgreSQL extension, so installation is additive: your existing databases stay untouched, and you opt in table-by-table. This guide walks through adding the extension from the official TimescaleDB repository, converting a plain table into a hypertable, setting up a continuous aggregate for fast roll-ups, and enforcing an automated data-retention policy. Commands are shown for Ubuntu/Debian, Fedora/RHEL, and Arch where they differ.

1. Install PostgreSQL and the TimescaleDB Extension

Ubuntu / Debian

TimescaleDB publishes its own apt repository. The installer script adds the GPG key and source list, then you install the extension package that matches your PostgreSQL major version (16 shown here).

sudo apt install -y gnupg curl
curl -fsSL https://packagecloud.io/timescale/timescaledb/gpgkey \
  | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/timescaledb.gpg

echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu $(lsb_release -cs) main" \
  | sudo tee /etc/apt/sources.list.d/timescaledb.list

sudo apt update
sudo apt install -y timescaledb-2-postgresql-16

Fedora / RHEL / Rocky

sudo tee /etc/yum.repos.d/timescaledb.repo <<'EOF'
[timescaledb]
name=timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/9/$basearch
repo_gpgcheck=1
gpgcheck=0
enabled=1
gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey
sslverify=1
EOF

sudo dnf install -y timescaledb-2-postgresql-16

Arch Linux

paru -S timescaledb   # or yay, or clone the AUR package manually

2. Tune PostgreSQL for TimescaleDB

TimescaleDB ships a helper that writes sensible values for shared_preload_libraries, shared_buffers, max_connections, and other parameters into postgresql.conf. Run it as root and answer the interactive prompts, or pass --quiet to accept defaults.

sudo timescaledb-tune --pg-config=/usr/lib/postgresql/16/bin/pg_config

The most critical change is loading the extension at startup. Verify it landed correctly:

grep shared_preload_libraries /etc/postgresql/16/main/postgresql.conf
# Expected output contains: timescaledb

Restart PostgreSQL to apply the changes:

sudo systemctl restart postgresql

3. Enable the Extension in Your Database

Connect as the superuser and run CREATE EXTENSION once per database where you want TimescaleDB features.

sudo -u postgres psql -d your_database

CREATE EXTENSION IF NOT EXISTS timescaledb;
\dx timescaledb

You should see the extension listed with its version, for example timescaledb 2.15.x. If PostgreSQL says the library is not found, the shared_preload_libraries change has not taken effect — confirm the restart completed with systemctl status postgresql.

4. Create a Hypertable

A hypertable is a regular PostgreSQL table that TimescaleDB automatically partitions into chunks along a time column. You create the table normally, then promote it. The time column must have a TIMESTAMPTZ or BIGINT type.

psql -U postgres -d your_database

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

SELECT create_hypertable('sensor_readings', by_range('time'));

By default TimescaleDB creates 7-day chunks. For higher-frequency data (millions of rows per day) a shorter chunk interval improves query pruning. Set it at creation time:

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

You can also add a secondary space partition on sensor_id if you have hundreds of distinct devices and want parallelism across them, but for most workloads a single time dimension is sufficient.

Verify the hypertable

SELECT hypertable_name, num_dimensions
FROM timescaledb_information.hypertables;

5. Continuous Aggregates

Continuous aggregates are materialised views backed by a background job that refreshes only the new data since the last run. They dramatically speed up queries that repeatedly aggregate large time ranges, such as hourly averages over months of data.

CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  sensor_id,
  AVG(temperature)            AS avg_temp,
  AVG(humidity)               AS avg_humidity,
  MAX(temperature)            AS max_temp
FROM sensor_readings
GROUP BY bucket, sensor_id
WITH NO DATA;

The WITH NO DATA clause skips the initial full backfill. Trigger it explicitly after creation so you control when the load hits:

CALL refresh_continuous_aggregate(
  'sensor_hourly',
  NOW() - INTERVAL '30 days',
  NOW()
);

Then add an automatic refresh policy. This example keeps the aggregate current by refreshing every hour, looking back over a 2-hour window to catch any late-arriving rows:

SELECT add_continuous_aggregate_policy(
  'sensor_hourly',
  start_offset => INTERVAL '2 hours',
  end_offset   => INTERVAL '10 minutes',
  schedule_interval => INTERVAL '1 hour'
);

The end_offset intentionally excludes the most recent 10 minutes. TimescaleDB's background scheduler will not materialise an incomplete bucket that is still receiving writes.

6. Data Retention Policies

Storing years of raw sensor data is expensive. A retention policy automatically drops old chunks, which is far faster than DELETE because it removes entire files at the filesystem level — no vacuuming required.

SELECT add_retention_policy(
  'sensor_readings',
  drop_after => INTERVAL '90 days'
);

Chunks are dropped only when the entire chunk falls outside the retention window, so actual data loss starts at the chunk boundary past 90 days — typically one to a few days beyond the cutoff depending on chunk size. Check scheduled jobs:

SELECT job_id, application_name, schedule_interval, next_start
FROM timescaledb_information.jobs
WHERE application_name LIKE 'Retention%';

To run the policy immediately for testing:

SELECT run_job(job_id)
FROM timescaledb_information.jobs
WHERE application_name LIKE 'Retention%';

7. Verification

Insert a batch of synthetic readings and confirm chunking and the continuous aggregate both work:

INSERT INTO sensor_readings (time, sensor_id, temperature, humidity)
SELECT
  NOW() - (s * INTERVAL '1 minute'),
  (s % 5) + 1,
  20 + random() * 10,
  40 + random() * 20
FROM generate_series(1, 1440) s;

SELECT count(*) FROM sensor_readings;

SELECT chunk_name, range_start, range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_readings';

CALL refresh_continuous_aggregate('sensor_hourly', NOW() - INTERVAL '1 day', NOW());

SELECT bucket, sensor_id, ROUND(avg_temp::numeric, 2)
FROM sensor_hourly
ORDER BY bucket DESC
LIMIT 10;

Troubleshooting

• Extension not found after CREATE EXTENSION: TimescaleDB must be in shared_preload_libraries before the server starts. Re-run timescaledb-tune, confirm the line in postgresql.conf, and restart PostgreSQL.
• timescaledb-tune not found: On Debian/Ubuntu, the binary lands in /usr/bin/ only when the timescaledb-tools package is installed. Run sudo apt install timescaledb-tools.
• Continuous aggregate not updating: The background scheduler (timescaledb.telemetry_level aside) requires timescaledb.enable_background_workers = on in postgresql.conf and enough max_worker_processes slots. timescaledb-tune sets these, but manual configs may miss them.
• Retention policy drops nothing: The policy operates on complete chunks. If your chunk interval is 7 days and your retention is 90 days, nothing is dropped until a full 7-day chunk is older than 90 days. Reduce the chunk interval or widen the retention window.
• Version mismatch after PostgreSQL upgrade: The extension version compiled against the old PostgreSQL major version will not load. Reinstall the matching timescaledb-2-postgresql-<NEW_VERSION> package and run ALTER EXTENSION timescaledb UPDATE; inside the database.