How to Install and Use TimescaleDB for Time-Series Data

How to Install and Use TimescaleDB for Time-Series Data

TimescaleDB is a PostgreSQL extension optimized for time-series workloads such as metrics, IoT data, and event logging on your Breeze.

Installation

Add the TimescaleDB repository and install:

sudo apt install gnupg postgresql-common -y
sudo sh -c "echo 'deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -cs) main' > /etc/apt/sources.list.d/timescaledb.list"
curl -fsSL https://packagecloud.io/timescale/timescaledb/gpgkey | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/timescaledb.gpg
sudo apt update && sudo apt install timescaledb-2-postgresql-16 -y

Enable the Extension

Run the tuning tool and enable it in your database:

sudo timescaledb-tune --quiet --yes
sudo systemctl restart postgresql

psql -U postgres -d mydb -c "CREATE EXTENSION IF NOT EXISTS timescaledb;"

Create a Hypertable

Convert a regular table to a hypertable for automatic time-based partitioning:

CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION
);
SELECT create_hypertable('metrics', 'time');

Querying Time-Series Data

SELECT time_bucket('1 hour', time) AS hour,
       AVG(temperature) AS avg_temp
FROM metrics
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour ORDER BY hour;

TimescaleDB seamlessly extends PostgreSQL with powerful time-series capabilities on your Breeze.