How to set up a TimescaleDB hypertable in PostgreSQL

Convert a standard PostgreSQL table into a time-series optimized hypertable using the TimescaleDB extension. These steps target PostgreSQL 16 and Ubuntu 24.04 with the official Timescale repository configured.

Prerequisites

• PostgreSQL 16 installed and running as a service.
• Superuser privileges or a role with CREATE and EXTENSION permissions.
• A source table containing a timestamp column and at least one other column.
• Access to the Timescale repository GPG key and APT repository.

Step 1: Add the Timescale repository

Import the official Timescale GPG key to verify package signatures. Then add the APT repository configuration for your specific PostgreSQL version.

curl -fsSL https://packagecloud.io/install/repositories/timescale/setup.deb.sh | sudo bash
curl -fsSL https://packagecloud.io/install/repositories/timescale/timescale-16/setup.deb.sh | sudo bash

Update the local package index to include the new repository sources.

sudo apt-get update

Step 2: Install the Timescale extension

Install the timescaledb package which contains the extension binaries and scripts. This command installs the extension into the system package manager.

sudo apt-get install timescaledb

Connect to your PostgreSQL database as a superuser to enable the extension. Run this command to load the extension into the postgres database.

sudo -u postgres psql -c "CREATE EXTENSION timescaledb;"

Step 3: Create the hypertable

Convert an existing table into a hypertable by specifying the time column and any partitioning keys. This command transforms a standard table into a partitioned, time-series optimized structure.

CREATE TABLE sensor_data (
    id SERIAL PRIMARY KEY,
    sensor_id INTEGER NOT NULL,
    reading DOUBLE PRECISION,
    created_at TIMESTAMP NOT NULL
);

Apply the create_hypertable function to the sensor_data table. Specify the created_at column as the time column and set if_not_exists to prevent errors if the table is already a hypertable.

SELECT create_hypertable('sensor_data', 'created_at', if_not_exists => true);

Step 4: Configure continuous aggregates

Create a continuous aggregate to automatically maintain a summary table for faster query performance. This step creates a view that aggregates data by day and calculates the average reading.

SELECT create_continuous_aggregate(
    'sensor_avg_daily',
    start_offset => INTERVAL '1 day',
    end_offset => INTERVAL '1 day',
    schedule => INTERVAL '1 hour'
);

Alternatively, use the create_continuous_aggregate function with specific column aggregations for better performance. This command creates a view that aggregates data by day and calculates the average reading.

SELECT create_hypertable('sensor_data', 'created_at', if_not_exists => true);
SELECT create_continuous_aggregate('sensor_avg_daily', 'created_at', 'sensor_id', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'sensor_id', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(reading)', 'min(reading)', 'max(reading)', 'count(*)', 'sum(reading)', 'avg(