timescaledb

Overview

Version

Build

You can build the RPM / DEB packages for timescaledb using pig build:

pig build pkg timescaledb         # build RPM / DEB packages

Install

You can install timescaledb directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install timescaledb;          # Install for current active PG version

pig ext install -y timescaledb -v 18  # PG 18
pig ext install -y timescaledb -v 17  # PG 17
pig ext install -y timescaledb -v 16  # PG 16
pig ext install -y timescaledb -v 15  # PG 15

dnf install -y timescaledb-tsl_18       # PG 18
dnf install -y timescaledb-tsl_17       # PG 17
dnf install -y timescaledb-tsl_16       # PG 16
dnf install -y timescaledb-tsl_15       # PG 15

apt install -y postgresql-18-timescaledb-tsl   # PG 18
apt install -y postgresql-17-timescaledb-tsl   # PG 17
apt install -y postgresql-16-timescaledb-tsl   # PG 16
apt install -y postgresql-15-timescaledb-tsl   # PG 15

Preload:

shared_preload_libraries = 'timescaledb';

Create Extension:

CREATE EXTENSION timescaledb;

Usage

Source: README, TimescaleDB 2.26.4 release, create_hypertable() API, CREATE TABLE hypertable API, continuous aggregates guide, add_job() API, add_columnstore_policy() API

timescaledb is a PostgreSQL extension for time-series and event analytics. The current docs emphasize hypertables, continuous aggregates, automation jobs, and moving older chunks into the columnstore.

Hypertables

CREATE EXTENSION timescaledb;

CREATE TABLE ts_test (
  ts timestamptz NOT NULL,
  id bigint,
  v integer
);

SELECT create_hypertable('ts_test', by_range('ts'));

• create_hypertable() still works, but the API docs mark it as old since TimescaleDB 2.20.0 and point new users toward CREATE TABLE ... WITH (...).
• The current README also shows the newer pattern: CREATE TABLE ... WITH (tsdb.hypertable).

Continuous aggregates and jobs

CREATE MATERIALIZED VIEW ts_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts) AS bucket,
       count(*) AS cnt,
       avg(v)   AS avg_v
FROM ts_test
GROUP BY bucket;

SELECT add_continuous_aggregate_policy(
  'ts_hourly',
  start_offset => INTERVAL '3 hours',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour'
);

SELECT add_job('user_defined_action', '1h');

• Continuous aggregates require time_bucket(...) on the hypertable’s time dimension.
• In TimescaleDB 2.13 and later, real-time aggregates are disabled by default unless configured otherwise.

Columnstore

ALTER TABLE ts_test SET (
  timescaledb.enable_columnstore,
  timescaledb.orderby = 'ts DESC'
);

CALL add_columnstore_policy('ts_test', after => INTERVAL '1 day');

• The docs treat add_columnstore_policy() and convert_to_columnstore() as the current APIs.
• Older compression functions such as add_compression_policy() are documented as old APIs replaced by the columnstore interface.

Caveats

• TimescaleDB 2.26.4 is a bug-fix release over 2.26.3; the release notes recommend upgrading but do not add a new SQL usage surface for hypertables, continuous aggregates, jobs, or columnstore.
• Relevant 2.26.4 fixes touch continuous aggregate planning and refresh, runtime chunk exclusion, background worker restart after restore, sparse indexes on orderby, and compressed chunk merge safety. Keep using the documented APIs above rather than changing SQL for the patch release itself.