How to Set Up TimescaleDB for Time-Series Data

How to Set Up TimescaleDB for Time-Series Data

TimescaleDB is a PostgreSQL extension purpose-built for time-series data. It provides automatic partitioning, advanced compression, and continuous aggregates while retaining full SQL compatibility. It is ideal for monitoring metrics, IoT data, and financial data on your Breeze instances.

Installing TimescaleDB

On your Breeze instance running Ubuntu, add the TimescaleDB repository and install:

sudo apt install -y gnupg postgresql-common apt-transport-https lsb-release wget
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -cs) main" | \
  sudo tee /etc/apt/sources.list.d/timescaledb.list
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -
sudo apt update
sudo apt install -y timescaledb-2-postgresql-16

Configuring PostgreSQL for TimescaleDB

Run the tuning utility to optimize PostgreSQL settings for your Breeze instance resources:

sudo timescaledb-tune --yes
sudo systemctl restart postgresql

This adjusts shared_preload_libraries, shared_buffers, work_mem, and other parameters based on your available RAM and CPU cores.

Creating a Hypertable

Connect to PostgreSQL and create a TimescaleDB-enabled database:

sudo -u postgres psql

CREATE DATABASE metrics;
\c metrics
CREATE EXTENSION IF NOT EXISTS timescaledb;

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

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

The create_hypertable function automatically partitions the table by time into chunks, enabling efficient querying and data management.

Inserting and Querying Data

Insert data just like a regular PostgreSQL table:

INSERT INTO sensor_data (time, sensor_id, temperature, humidity, location)
VALUES (NOW(), 1, 22.5, 45.0, 'datacenter-a'),
       (NOW(), 2, 23.1, 42.3, 'datacenter-b');

Query with time-bucket aggregations:

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

Enabling Compression

Compress older data to save disk space on your Breeze instance:

ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id'
);

SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

This automatically compresses chunks older than 7 days, typically achieving 90-95% compression ratios.

Continuous Aggregates

Create materialized views that automatically refresh as new data arrives:

CREATE MATERIALIZED VIEW hourly_temps
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       sensor_id,
       AVG(temperature) AS avg_temp,
       MIN(temperature) AS min_temp,
       MAX(temperature) AS max_temp
FROM sensor_data
GROUP BY bucket, sensor_id;

SELECT add_continuous_aggregate_policy('hourly_temps',
    start_offset => INTERVAL '3 days',
    end_offset   => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

TimescaleDB is fully compatible with all PostgreSQL tools, so you can use pg_dump, psql, and any PostgreSQL client library to interact with your time-series data on your Breeze instances.