Managing Time Series Data Using TimeScaleDB on Postgres

TimescaleDB is an open-source time-series database optimized for fast ingest and complex queries, built on PostgreSQL. This guide will help you install TimescaleDB on PostgreSQL, configure it, and convert a regular table into a hypertable for efficient time-series data management.

Installation

First, download the TimescaleDB package:

wget https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-9-x86_64/timescaledb_14-2.11.1-1PGDG.rhel9.x86_64.rpm

Then, install the package using rpm:

rpm -iv timescaledb_14-2.11.1-1PGDG.rhel9.x86_64.rpm

Verify that the TimescaleDB control file exists:

rpm -ql timescaledb_14 | grep /usr/pgsql-14/share/extension/timescaledb.control

If the file exists, you can proceed with the configuration.

Configuration

Modify the postgresql.conf file to include TimescaleDB in the shared preload libraries.

For PostgreSQL:

vim /pg_data/data/postgresql.conf

Add the following line:

shared_preload_libraries = 'timescaledb'

For Patroni, use patronictl to edit the configuration:

patronictl -c /etc/patroni/patroni.yml edit-config

After modifying the configuration, restart PostgreSQL:

sudo systemctl restart postgresql-14.service && sudo systemctl restart patroni.service

Creating the Extension

Once PostgreSQL is restarted, create the TimescaleDB extension:

CREATE EXTENSION timescaledb;

Creating a Hypertable

TimescaleDB uses hypertables to manage time-series data. Hypertables partition your data by time and provide efficient querying and data management.

Here’s how to create a sensor_data table and convert it into a hypertable:

Create the base table:

CREATE TABLE sensor_data (
    time        TIMESTAMPTZ       NOT NULL,
    sensor_id   TEXT              NOT NULL,
    value       DOUBLE PRECISION  NOT NULL,
    PRIMARY KEY (time, sensor_id)
);

Convert the base table into a hypertable:

Hypertables are PostgreSQL tables that automatically partition your data by time. You interact with hypertables in the same way as regular PostgreSQL tables, but with extra features that make managing your time-series data much easier.

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

Additional Hypertable Options

If you need to specify additional options like chunk time intervals, you can include them in the create_hypertable function:

rpm -iv timescaledb_14-2.11.1-1PGDG.rhel9.x86_64.rpm

0

To automatically create default indexes:

rpm -iv timescaledb_14-2.11.1-1PGDG.rhel9.x86_64.rpm

1

Advanced Features

Continuous Aggregates

Continuous aggregates allow you to maintain real-time aggregates of your data, which are automatically refreshed as new data is ingested. This is highly efficient for queries that involve large data scans.

Compression

Compression in TimescaleDB helps reduce storage costs and improve query performance for historical data. You can enable compression on hypertables and define specific policies.

Data Retention Policies

Data retention policies allow you to automatically remove old data, ensuring that your database size remains manageable.