This is the multi-page printable view of this section.
Click here to print.
Return to the regular view of this page.
Category: TIME
TIME: TimescaleDB, Versioning & Temporal Table, Crontab, Async & Background Job Scheduler, …
TIME category has 11 available extensions:
1 - timescaledb
Enables scalable inserts and complex queries for time-series data
Overview
PIGSTY 3rd Party Extension: timescaledb : Enables scalable inserts and complex queries for time-series data
- Latest Version: 2.18.1
- Postgres Support:
17,16,15,14
- 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.18.1
- RPM Deps: N/A
- DEB Repo: PIGSTY
- DEB Name:
postgresql-$v-timescaledb-tsl
- DEB Ver :
2.18.1
- DEB Deps: N/A
Packages
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 added to shared_preload_libraries
shared_preload_libraries = 'timescaledb'; # add to pg cluster config
Enable 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);
2 - timescaledb_toolkit
Library of analytical hyperfunctions, time-series pipelining, and other SQL utilities
Overview
PIGSTY 3rd Party Extension: timescaledb_toolkit : Library of analytical hyperfunctions, time-series pipelining, and other SQL utilities
- Latest Version: 1.19.0
- Postgres Support:
17,16,15,14
- Need Load: Shared library do not need explicit loading
- Need DDL: Need
CREATE EXTENSION DDL
- Relocatable: Can be installed into other schemas
- Trusted: Trusted, Can be created by user with
CREATE Privilege
- Schemas: N/A
- Requires: N/A
RPM / DEB
- RPM Repo: PIGSTY
- RPM Name:
timescaledb-toolkit_$v
- RPM Ver :
1.19.0
- RPM Deps: N/A
- DEB Repo: PIGSTY
- DEB Name:
postgresql-$v-timescaledb-toolkit
- DEB Ver :
1.19.0
- DEB Deps: N/A
Packages
Installation
Install timescaledb_toolkit via the pig CLI tool:
pig ext install timescaledb_toolkit
Install timescaledb_toolkit via Pigsty playbook:
./pgsql.yml -t pg_extension -e '{"pg_extensions": ["timescaledb_toolkit"]}' # -l <cls>
Install timescaledb_toolkit RPM from YUM repo directly:
dnf install timescaledb-toolkit_17;
dnf install timescaledb-toolkit_16;
dnf install timescaledb-toolkit_15;
dnf install timescaledb-toolkit_14;
Install timescaledb_toolkit DEB from APT repo directly:
apt install postgresql-17-timescaledb-toolkit;
apt install postgresql-16-timescaledb-toolkit;
apt install postgresql-15-timescaledb-toolkit;
apt install postgresql-14-timescaledb-toolkit;
Enable timescaledb_toolkit extension on PostgreSQL cluster：
CREATE EXTENSION timescaledb_toolkit;
Usage
This extension provide experimental features for timescaledb, check the docs for details.
Features
The following links lead to pages for the different features in the TimescaleDB Toolkit repository.
-
ASAP Smoothing experimental - A data smoothing algorithm designed to generate human readable graphs which maintain any erratic data behavior while smoothing away the cyclic noise.
-
Hyperloglog experimental – An approximate COUNT DISTINCT based on hashing that provides reasonable accuracy in constant space. (Methods)
-
LTTB experimental – A downsample method that preserves visual similarity. (Methods)
-
Percentile Approximation - A simple percentile approximation interface [(Methods)], wraps and simplifies the lower level algorithms:
- T-Digest – A quantile estimate sketch optimized to provide more accurate estimates near the tails (i.e. 0.001 or 0.995) than conventional approaches. (Methods)
- UddSketch – A quantile estimate sketch which provides a guaranteed maximum relative error. (Methods)
3 - timeseries
Convenience API for Tembo time series stack
Overview
PIGSTY 3rd Party Extension: pg_timeseries : Convenience API for Tembo time series stack
- Latest Version: 0.1.6
- Postgres Support:
17,16,15,14,13
- Need Load: Shared library do not need explicit loading
- Need DDL: Need
CREATE EXTENSION DDL
- Relocatable: Can be installed into other schemas
- Trusted: Untrusted, Require Superuser to Create
- Schemas: N/A
- Requires:
columnar, pg_cron, pg_ivm, pg_partman
RPM / DEB
- RPM Repo: PIGSTY
- RPM Name:
pg_timeseries_$v
- RPM Ver :
0.1.6
- RPM Deps:
hydra_$v, pg_cron_$v, pg_ivm_$v, pg_partman_$v
- DEB Repo: PIGSTY
- DEB Name:
postgresql-$v-pg-timeseries
- DEB Ver :
0.1.6
- DEB Deps: N/A
Packages
Installation
Install timeseries via the pig CLI tool:
pig ext install pg_timeseries; # Extension Namepig ext install timeseries; # normalized package name
Install pg_timeseries via Pigsty playbook:
./pgsql.yml -t pg_extension -e '{"pg_extensions": ["pg_timeseries"]}' # -l <cls>
Install pg_timeseries RPM from YUM repo directly:
dnf install pg_timeseries_17;
dnf install pg_timeseries_16;
dnf install pg_timeseries_15;
dnf install pg_timeseries_14;
dnf install pg_timeseries_13;
Install pg_timeseries DEB from APT repo directly:
apt install postgresql-17-pg-timeseries;
apt install postgresql-16-pg-timeseries;
apt install postgresql-15-pg-timeseries;
apt install postgresql-14-pg-timeseries;
apt install postgresql-13-pg-timeseries;
Enable timeseries extension on PostgreSQL cluster：
CREATE EXTENSION timeseries CASCADE;
4 - periods
Provide Standard SQL functionality for PERIODs and SYSTEM VERSIONING
Overview
PGDG 1st Party Extension: periods : Provide Standard SQL functionality for PERIODs and SYSTEM VERSIONING
- Latest Version: 1.2
- Postgres Support:
17,16,15,14,13
- Need Load: Shared library do not need explicit loading
- Need DDL: Need
CREATE EXTENSION DDL
- Relocatable: Can be installed into other schemas
- Trusted: Untrusted, Require Superuser to Create
- Schemas: N/A
- Requires:
btree_gist
RPM / DEB
- RPM Repo: PGDG
- RPM Name:
periods_$v*
- RPM Ver :
1.2
- RPM Deps: N/A
- DEB Repo: PGDG
- DEB Name:
postgresql-$v-periods
- DEB Ver :
1.2
- DEB Deps: N/A
Packages
Installation
Install periods via the pig CLI tool:
Install periods via Pigsty playbook:
./pgsql.yml -t pg_extension -e '{"pg_extensions": ["periods"]}' # -l <cls>
Install periods RPM from YUM repo directly:
dnf install periods_17*;
dnf install periods_16*;
dnf install periods_15*;
dnf install periods_14*;
dnf install periods_13*;
Install periods DEB from APT repo directly:
apt install postgresql-17-periods;
apt install postgresql-16-periods;
apt install postgresql-15-periods;
apt install postgresql-14-periods;
apt install postgresql-13-periods;
Enable periods extension on PostgreSQL cluster：
CREATE EXTENSION periods CASCADE;
5 - temporal_tables
temporal tables
Overview
PIGSTY 3rd Party Extension: temporal_tables : temporal tables
- Latest Version: 1.2.2
- Postgres Support:
17,16,15,14,13
- Need Load: Shared library do not need explicit loading
- Need DDL: Need
CREATE EXTENSION DDL
- Relocatable: Can not install to arbitrary schema
- Trusted: Untrusted, Require Superuser to Create
- Schemas: N/A
- Requires: N/A
RPM / DEB
- RPM Repo: PIGSTY
- RPM Name:
temporal_tables_$v*
- RPM Ver :
1.2.2
- RPM Deps: N/A
- DEB Repo: PIGSTY
- DEB Name:
postgresql-$v-temporal-tables
- DEB Ver :
1.2.2
- DEB Deps: N/A
Packages
Installation
Install temporal_tables via the pig CLI tool:
pig ext install temporal_tables
Install temporal_tables via Pigsty playbook:
./pgsql.yml -t pg_extension -e '{"pg_extensions": ["temporal_tables"]}' # -l <cls>
Install temporal_tables RPM from YUM repo directly:
dnf install temporal_tables_17*;
dnf install temporal_tables_16*;
dnf install temporal_tables_15*;
dnf install temporal_tables_14*;
dnf install temporal_tables_13*;
Install temporal_tables DEB from APT repo directly:
apt install postgresql-17-temporal-tables;
apt install postgresql-16-temporal-tables;
apt install postgresql-15-temporal-tables;
apt install postgresql-14-temporal-tables;
apt install postgresql-13-temporal-tables;
Enable temporal_tables extension on PostgreSQL cluster：
CREATE EXTENSION temporal_tables;
6 - emaj
Enables fine-grained write logging and time travel on subsets of the database.
Overview
MIXED 3rd Party Extension: emaj : Enables fine-grained write logging and time travel on subsets of the database.
- Latest Version: 4.5.0
- Postgres Support:
17,16,15,14,13
- Need Load: Shared library do not need explicit loading
- Need DDL: Need
CREATE EXTENSION DDL
- Relocatable: Can be installed into other schemas
- Trusted: Untrusted, Require Superuser to Create
- Schemas:
emaj
- Requires:
dblink, btree_gist
RPM / DEB
- RPM Repo: PGDG
- RPM Name:
e-maj_$v
- RPM Ver :
4.5.0
- RPM Deps: N/A
- DEB Repo: PIGSTY
- DEB Name:
postgresql-$v-emaj
- DEB Ver :
4.4.0
- DEB Deps: N/A
Packages
Installation
Install emaj via the pig CLI tool:
Install emaj via Pigsty playbook:
./pgsql.yml -t pg_extension -e '{"pg_extensions": ["emaj"]}' # -l <cls>
Install emaj RPM from YUM repo directly:
dnf install e-maj_17;
dnf install e-maj_16;
dnf install e-maj_15;
dnf install e-maj_14;
dnf install e-maj_13;
Install emaj DEB from APT repo directly:
apt install postgresql-17-emaj;
apt install postgresql-16-emaj;
apt install postgresql-15-emaj;
apt install postgresql-14-emaj;
apt install postgresql-13-emaj;
Enable emaj extension on PostgreSQL cluster：
CREATE EXTENSION emaj CASCADE;
7 - table_version
PostgreSQL table versioning extension
Overview
MIXED 3rd Party Extension: table_version : PostgreSQL table versioning extension
- Latest Version: 1.11.1
- Postgres Support:
17,16,15,14,13
- Need Load: Shared library do not need explicit loading
- Need DDL: Need
CREATE EXTENSION DDL
- Relocatable: Can be installed into other schemas
- Trusted: Untrusted, Require Superuser to Create
- Schemas:
table_version
- Requires:
plpgsql
RPM / DEB
- RPM Repo: PIGSTY
- RPM Name:
table_version_$v
- RPM Ver :
1.11.1
- RPM Deps: N/A
- DEB Repo: PIGSTY
- DEB Name:
postgresql-$v-tableversion
- DEB Ver :
1.10.3
- DEB Deps: N/A
Packages
Installation
Install table_version via the pig CLI tool:
pig ext install table_version
Install table_version via Pigsty playbook:
./pgsql.yml -t pg_extension -e '{"pg_extensions": ["table_version"]}' # -l <cls>
Install table_version RPM from YUM repo directly:
dnf install table_version_17;
dnf install table_version_16;
dnf install table_version_15;
dnf install table_version_14;
dnf install table_version_13;
Install table_version DEB from APT repo directly:
apt install postgresql-17-tableversion;
apt install postgresql-16-tableversion;
apt install postgresql-15-tableversion;
apt install postgresql-14-tableversion;
apt install postgresql-13-tableversion;
Enable table_version extension on PostgreSQL cluster：
CREATE EXTENSION table_version CASCADE;
8 - pg_cron
Job scheduler for PostgreSQL
Overview
PGDG 1st Party Extension: pg_cron : Job scheduler for PostgreSQL
- Latest Version: 1.6
- Postgres Support:
17,16,15,14,13
- 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:
pg_catalog
- Requires: N/A
RPM / DEB
- RPM Repo: PGDG
- RPM Name:
pg_cron_$v*
- RPM Ver :
1.6
- RPM Deps: N/A
- DEB Repo: PGDG
- DEB Name:
postgresql-$v-cron
- DEB Ver :
1.6
- DEB Deps: N/A
Packages
Installation
Install pg_cron via the pig CLI tool:
Install pg_cron via Pigsty playbook:
./pgsql.yml -t pg_extension -e '{"pg_extensions": ["pg_cron"]}' # -l <cls>
Install pg_cron RPM from YUM repo directly:
dnf install pg_cron_17*;
dnf install pg_cron_16*;
dnf install pg_cron_15*;
dnf install pg_cron_14*;
dnf install pg_cron_13*;
Install pg_cron DEB from APT repo directly:
apt install postgresql-17-cron;
apt install postgresql-16-cron;
apt install postgresql-15-cron;
apt install postgresql-14-cron;
apt install postgresql-13-cron;
Extension pg_cron has to be added to shared_preload_libraries
shared_preload_libraries = 'pg_cron'; # add to pg cluster config
Enable pg_cron extension on PostgreSQL cluster：
CREATE EXTENSION pg_cron;
Usage
beware that cron.database has to be set before adding to shared_preload_libraries
-- Delete old data on Saturday at 3:30am (GMT)
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
schedule
----------
42
-- Vacuum every day at 10:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');
schedule
----------
43
-- Change to vacuum at 3:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');
schedule
----------
43
-- Stop scheduling jobs
SELECT cron.unschedule('nightly-vacuum' );
unschedule
------------
t
SELECT cron.unschedule(42);
unschedule
------------
t
-- Vacuum every Sunday at 4:00am (GMT) in a database other than the one pg_cron is installed in
SELECT cron.schedule_in_database('weekly-vacuum', '0 4 * * 0', 'VACUUM', 'some_other_database');
schedule
----------
44
-- Call a stored procedure every 5 seconds
SELECT cron.schedule('process-updates', '5 seconds', 'CALL process_updates()');
-- Process payroll at 12:00 of the last day of each month
SELECT cron.schedule('process-payroll', '0 12 $ * *', 'CALL process_payroll()');
Crontab format:
┌───────────── min (0 - 59)
│ ┌────────────── hour (0 - 23)
│ │ ┌─────────────── day of month (1 - 31) or last day of the month ($)
│ │ │ ┌──────────────── month (1 - 12)
│ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
│ │ │ │ │ Saturday, or use names; 7 is also Sunday)
│ │ │ │ │
│ │ │ │ │
* * * * *
9 - pg_task
execute any sql command at any specific time at background
Overview
PGDG 3rd Party Extension: pg_task : execute any sql command at any specific time at background
- Latest Version: 1.0.0
- Postgres Support:
17,16,15,14,13
- 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: N/A
- Requires: N/A
RPM / DEB
- RPM Repo: PGDG
- RPM Name:
pg_task_$v*
- RPM Ver :
1.0.0
- RPM Deps: N/A
- DEB Repo: PIGSTY
- DEB Name:
postgresql-$v-pg-task
- DEB Ver :
1.0.0
- DEB Deps: N/A
Packages
Installation
Install pg_task via the pig CLI tool:
Install pg_task via Pigsty playbook:
./pgsql.yml -t pg_extension -e '{"pg_extensions": ["pg_task"]}' # -l <cls>
Install pg_task RPM from YUM repo directly:
dnf install pg_task_17*;
dnf install pg_task_16*;
dnf install pg_task_15*;
dnf install pg_task_14*;
dnf install pg_task_13*;
Install pg_task DEB from APT repo directly:
apt install postgresql-17-pg-task;
apt install postgresql-16-pg-task;
apt install postgresql-15-pg-task;
apt install postgresql-14-pg-task;
apt install postgresql-13-pg-task;
Extension pg_task has to be added to shared_preload_libraries
shared_preload_libraries = 'pg_task'; # add to pg cluster config
Enable pg_task extension on PostgreSQL cluster：
CREATE EXTENSION pg_task;
10 - pg_later
pg_later: Run queries now and get results later
Overview
PIGSTY 3rd Party Extension: pg_later : pg_later: Run queries now and get results later
- Latest Version: 0.3.0
- Postgres Support:
17,16,15,14,13
- Need Load: Shared library do not need explicit loading
- Need DDL: Need
CREATE EXTENSION DDL
- Relocatable: Can be installed into other schemas
- Trusted: Untrusted, Require Superuser to Create
- Schemas:
pglater
- Requires:
pgmq
RPM / DEB
- RPM Repo: PIGSTY
- RPM Name:
pg_later_$v
- RPM Ver :
0.3.0
- RPM Deps:
pgmq_$v
- DEB Repo: PIGSTY
- DEB Name:
postgresql-$v-pg-later
- DEB Ver :
0.3.0
- DEB Deps:
postgresql-$v-pgmq
Packages
Installation
Install pg_later via the pig CLI tool:
Install pg_later via Pigsty playbook:
./pgsql.yml -t pg_extension -e '{"pg_extensions": ["pg_later"]}' # -l <cls>
Install pg_later RPM from YUM repo directly:
dnf install pg_later_17;
dnf install pg_later_16;
dnf install pg_later_15;
dnf install pg_later_14;
dnf install pg_later_13;
Install pg_later DEB from APT repo directly:
apt install postgresql-17-pg-later;
apt install postgresql-16-pg-later;
apt install postgresql-15-pg-later;
apt install postgresql-14-pg-later;
apt install postgresql-13-pg-later;
Enable pg_later extension on PostgreSQL cluster：
CREATE EXTENSION pg_later CASCADE;
11 - pg_background
Run SQL queries in the background
Overview
MIXED 3rd Party Extension: pg_background : Run SQL queries in the background
- Latest Version: 1.3
- Postgres Support:
17,16,15,14,13
- Need Load: Shared library do not need explicit loading
- Need DDL: Need
CREATE EXTENSION DDL
- Relocatable: Can not install to arbitrary schema
- Trusted: Untrusted, Require Superuser to Create
- Schemas: N/A
- Requires: N/A
RPM / DEB
- RPM Repo: PGDG
- RPM Name:
pg_background_$v*
- RPM Ver :
1.3
- RPM Deps: N/A
- DEB Repo: PIGSTY
- DEB Name:
postgresql-$v-pg-background
- DEB Ver :
1.3
- DEB Deps: N/A
Packages
Installation
Install pg_background via the pig CLI tool:
pig ext install pg_background
Install pg_background via Pigsty playbook:
./pgsql.yml -t pg_extension -e '{"pg_extensions": ["pg_background"]}' # -l <cls>
Install pg_background RPM from YUM repo directly:
dnf install pg_background_17*;
dnf install pg_background_16*;
dnf install pg_background_15*;
dnf install pg_background_14*;
dnf install pg_background_13*;
Install pg_background DEB from APT repo directly:
apt install postgresql-17-pg-background;
apt install postgresql-16-pg-background;
apt install postgresql-15-pg-background;
apt install postgresql-14-pg-background;
apt install postgresql-13-pg-background;
Enable pg_background extension on PostgreSQL cluster：
CREATE EXTENSION pg_background;