timescaledb
Enables scalable inserts and complex queries for time-series data
Module:
Categories:
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.19.0
- Postgres Support:
17,16,15,14 - Need Load: Explicit Loading Required
- Need DDL: Need
CREATE EXTENSIONDDL - 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.18.2 - RPM Deps: N/A
- DEB Repo: PIGSTY
- DEB Name:
postgresql-$v-timescaledb-tsl - DEB Ver :
2.18.2 - 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*;
dnf install timescaledb-tsl_14*;
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;
apt install postgresql-14-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);
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.