timescaledb

Overview

PIGSTY 3rd Party Extension: timescaledb : Enables scalable inserts and complex queries for time-series data

Information

• Extension ID: 1000
• Extension Name: timescaledb
• Package Name: timescaledb
• Category: TIME
• License: Timescale
• Website: https://github.com/timescale/timescaledb
• Language: C
• Extra Tags: N/A
• Comment: N/A

Metadata

• Latest Version: 2.20.0
• Postgres Support: 17,16,15
• Need Load: Explicit Loading Required
• Need DDL: Need CREATE EXTENSION DDL
• Relocatable: Can be installed into other schemas
• Trusted: Untrusted, Require Superuser to Create
• Schemas: timescaledb_information, timescaledb_experimental
• Requires: N/A

RPM / DEB

• RPM Repo: PIGSTY
• RPM Name: timescaledb-tsl_$v*
• RPM Ver : 2.20.0
• RPM Deps: N/A
• DEB Repo: PIGSTY
• DEB Name: postgresql-$v-timescaledb-tsl
• DEB Ver : 2.20.0
• DEB Deps: N/A

Availability

Installation

Install timescaledb via the pig CLI tool:

pig ext install timescaledb

Install timescaledb via Pigsty playbook:

./pgsql.yml -t pg_extension -e '{"pg_extensions": ["timescaledb"]}' # -l <cls>

Install timescaledb RPM from YUM repo directly:

dnf install timescaledb-tsl_17*;
dnf install timescaledb-tsl_16*;
dnf install timescaledb-tsl_15*;

Install timescaledb DEB from APT repo directly:

apt install postgresql-17-timescaledb-tsl;
apt install postgresql-16-timescaledb-tsl;
apt install postgresql-15-timescaledb-tsl;

Extension timescaledb has to be loaded via shared_preload_libraries

shared_preload_libraries = 'timescaledb'; # add to pg cluster config

Create timescaledb extension on PostgreSQL cluster:

CREATE EXTENSION timescaledb;

Usage

Create a table and turn it into hypertable

DROP TABLE IF EXISTS ts_test;
CREATE TABLE ts_test
(
    id BIGINT PRIMARY KEY,
    ts TIMESTAMPTZ NOT NULL,
    v  INTEGER -- payload
);
SELECT create_hypertable('ts_test', by_range('id'));

INSERT INTO ts_test 
    SELECT i, now() + (i || ' seconds')::INTERVAL, i % 100 
    FROM generate_series(1, 1000000) i;


ALTER TABLE ts_test SET (timescaledb.compress_chunk_time_interval = '24 hours');

Continuous Agg Example:

CREATE MATERIALIZED VIEW continuous_aggregate_daily( timec, minl, sumt, sumh )
WITH (timescaledb.continuous) AS
  SELECT count(*) FROM ts_test;


SELECT add_job('SELECT 1','1h', initial_start => '2024-07-09 18:52:00+00'::timestamptz);