PIGSTY

timescaledb

Enables scalable inserts and complex queries for time-series data

Overview

1000timescaledbtimescaledb2.20.0
TIME
Timescale
C

Attributes

YesNoYesYesYesNoNotimescaledb_information, timescaledb_experimental

Packages

EL
PIGSTY
timescaledb-tsl_$v*2.20.0-
17
16
15
14
13
Debian
PIGSTY
postgresql-$v-timescaledb-tsl2.20.0-
17
16
15
14
13

Availability

PG17PG16PG15PG14PG13
el8.x86_64
2.20.3
2.20.3
2.20.3
2.19.3
2.15.3
el8.aarch64
2.20.0
2.20.0
2.20.0
2.19.3
✗
el9.x86_64
2.20.3
2.20.3
2.20.3
2.19.3
2.15.3
el9.aarch64
2.20.0
2.20.0
2.20.0
2.19.3
✗
d12.x86_64
2.20.0
2.20.0
2.20.0
2.19.3
✗
d12.aarch64
2.20.0
2.20.0
2.20.0
2.19.3
✗
u22.x86_64
2.20.0
2.20.0
2.20.0
2.19.3
✗
u22.aarch64
2.20.0
2.20.0
2.20.0
2.19.3
✗
u24.x86_64
2.20.0
2.20.0
2.20.0
2.19.3
✗
u24.aarch64
2.20.0
2.20.0
2.20.0
2.19.3
✗
CONTRIB
PGDG
PIGSTY

Download

To add the required PGDG / PIGSTY upstream repository, use:

pig repo add pgdg -u    # add PGDG repo and update cache (leave existing repos)
pig repo add pigsty -u  # add PIGSTY repo and update cache (leave existing repos)
pig repo add pgsql -u   # add PGDG + Pigsty repo and update cache (leave existing repos)
pig repo set all -u     # set repo to all = NODE + PGSQL + INFRA  (remove existing repos)
./node.yml -t node_repo -e node_repo_modules=node,pgsql # -l <cluster>

Or download the latest packages directly:

Install

Install this extension with:

pig ext install timescaledb; # install by extension name, for the current active PG version

pig ext install timescaledb -v 17;   # install for PG 17
pig ext install timescaledb -v 16;   # install for PG 16
pig ext install timescaledb -v 15;   # install for PG 15
dnf install timescaledb-tsl_17*;
dnf install timescaledb-tsl_16*;
dnf install timescaledb-tsl_15*;
apt install postgresql-17-timescaledb-tsl;
apt install postgresql-16-timescaledb-tsl;
apt install postgresql-15-timescaledb-tsl;
./pgsql.yml -t pg_ext -e '{"pg_extensions": ["timescaledb"]}' # -l <cls>

Preload this extension with:

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

Create this extension with:

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);