How to create a TimescaleDB hypertable in PostgreSQL

Convert a standard PostgreSQL table into a TimescaleDB hypertable to enable efficient time-series storage and querying. These steps target PostgreSQL 16 and Ubuntu 24.04. You will install the extension, create the hypertable, and verify the data is stored correctly.

Prerequisites

• Ubuntu 24.04 or Debian 12 operating system.
• PostgreSQL 16 installed and running as a service.
• Superuser privileges or a role with CREATEROLE and CREATEDB privileges.
• Network access to the TimescaleDB repository (for apt installation).

Step 1: Install TimescaleDB

Add the TimescaleDB APT repository to your package manager and install the latest stable extension. This ensures you get the correct version compatible with your PostgreSQL installation.

echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu jammy main" | tee /etc/apt/sources.list.d/timescale.list
curl https://packagecloud.io/timescale/timescaledb/gpgkey | apt-key add -
apt-get update
apt-get install timescaledb-2-pg16

Verify the package installed correctly by checking the version.

apt list --installed | grep timescaledb

The output should show timescaledb-2-pg16 with a version number like 2.14.0.

Step 2: Create the TimescaleDB Extension

Connect to your PostgreSQL database using psql and create the extension in the target database. Run this command inside the database you intend to use for time-series data.

psql postgresql
CREATE EXTENSION timescaledb;

Exit the psql shell after the extension is created.

\q

The system will respond with CREATE EXTENSION if successful.

Step 3: Create a Standard Table

Create a standard PostgreSQL table that will hold your time-series data. Define the columns for time, device ID, and measurement values. Use a timestamp or timestamp with time zone for the time column.

CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER NOT NULL,
    temperature NUMERIC(5,2),
    humidity NUMERIC(5,2)
);

Insert some dummy data to populate the table before converting it.

INSERT INTO sensor_data (time, device_id, temperature, humidity)
SELECT
    generate_series('2024-01-01'::timestamp, '2024-01-01 00:00:01'::timestamp, '1 second'::interval) as time,
    1 as device_id,
    random() * 100 as temperature,
    random() * 100 as humidity
LIMIT 1000;

Check the row count to ensure data exists.

SELECT COUNT(*) FROM sensor_data;

The output should be 1000.

Step 4: Create the Hypertable

Convert the standard table into a hypertable using the create_hypertable function. Specify the time column as the first argument and the table name as the second. This splits the data into chunks automatically.

SELECT create_hypertable('sensor_data', 'time');

The function returns TRUE if the conversion succeeds.

TRUE

Verify the table is now a hypertable by checking the timescaledb_hypertable information schema.

SELECT * FROM timescaledb_information.hypertables WHERE hypertable_name = 'sensor_data';

The output should list sensor_data with the time column marked as the time column.

Step 5: Configure Chunking Options (Optional)

Adjust the chunk time interval if the default is not suitable for your data volume. The default is usually one day, but you can change it to one hour or one month.

SELECT create_hypertable('sensor_data', 'time', chunk_time_interval => INTERVAL '1 hour');

Alternatively, create a new hypertable with specific chunking options from the start.

CREATE TABLE sensor_data_new (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER NOT NULL,
    temperature NUMERIC(5,2),
    humidity NUMERIC(5,2)
);
SELECT create_hypertable('sensor_data_new', 'time', chunk_time_interval => INTERVAL '1 hour');

Verify the installation

Run a query that uses the hypertable structure to confirm the data is stored efficiently. Use EXPLAIN to see how the database accesses the data.

EXPLAIN ANALYZE SELECT AVG(temperature) FROM sensor_data WHERE time > NOW() - INTERVAL '1 day';

The output should show a Seq Scan or Index Scan on the time column, indicating the hypertable is active.

QUERY PLAN
------------------------------------------------------------------
 Aggregate  (cost=13.80..13.81 rows=1 width=32) (actual time=0.018..0.019 rows=1 loops=1)
   ->  Seq Scan on sensor_data  (cost=0.00..13.60 rows=86400 width=32) (actual time=0.008..0.015 rows=86400 loops=1)
         Filter: (time > ('2024-05-23 08:00:00+00'::timestamp without time zone)::timestamp)
 Planning Time: 0.123 ms
 Execution Time: 0.045 ms
(5 rows)

Troubleshooting

If the create_hypertable function fails, check the error message for column type mismatches. The time column must be timestamp, timestamp with time zone, or date. Ensure the column is NOT NULL if required by the function.

ERROR: column "time" must be of type timestamp, timestamp with time zone, or date

Resolve this by altering the column type.

ALTER TABLE sensor_data ALTER COLUMN time TYPE TIMESTAMPTZ;

If you encounter permission errors, ensure the current user has the necessary privileges.

FATAL:  permission denied to create extension

Switch to the postgres superuser or grant the required privileges.

sudo -u postgres psql
GRANT CREATE ON SCHEMA public TO your_user;

If the hypertable creation fails because the table already exists as a hypertable, drop and recreate it.

DROP TABLE sensor_data;
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER NOT NULL,
    temperature NUMERIC(5,2),
    humidity NUMERIC(5,2)
);
SELECT create_hypertable('sensor_data', 'time');

Check the chunk size if the table is growing too large. Increase the chunk time interval to reduce the number of chunks.

SELECT set_chunk_time_interval('sensor_data', INTERVAL '1 week');

Ensure the database has sufficient disk space. TimescaleDB stores data in chunks that require space proportional to the data volume.