Managing Time Series Data Using TimeScaleDB on Postgres

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 makes 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:

SELECT create_hypertable('sensor_data', 'time', chunk_time_interval => interval '1 day');

To automatically create default indexes:

SELECT create_hypertable('sensor_data2', 'time', chunk_time_interval => interval '1 day', create_default_indexes => TRUE);

Advanced Features

TimescaleDB offers several advanced features to optimize your time-series data management:

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.

CREATE MATERIALIZED VIEW daily_avg AS
SELECT time_bucket('1 day', time) AS day,
       AVG(value) AS avg_value
FROM sensor_data
GROUP BY day
WITH NO DATA;

SELECT add_continuous_aggregate_policy('daily_avg', start_offset => INTERVAL '1 month', end_offset => INTERVAL '1 day', schedule_interval => INTERVAL '1 hour');

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.

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


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

Data Retention Policies

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

SELECT add_retention_policy('sensor_data', INTERVAL '1 year');

Conclusion

TimescaleDB enhances PostgreSQL with time-series capabilities, making it easier to handle large volumes of time-series data. By following this guide, you can set up TimescaleDB, configure it, and start using hypertables for efficient time-series data management. Also, consider reading the TimescaleDB documentation and exploring additional features like continuous aggregates, compression, and data retention policies. For more detailed and technical articles like this, keep following our blog on Medium. If you have any questions or need further assistance, feel free to reach out in the comments below and directly.