timescaledb

timescaledb : Enables scalable inserts and complex queries for time-series data

Overview

Packages

Source

pig build pkg timescaledb;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install timescaledb;		# install via package name, for the active PG version

pig install timescaledb -v 18;   # install for PG 18
pig install timescaledb -v 17;   # install for PG 17
pig install timescaledb -v 16;   # install for PG 16
pig install timescaledb -v 15;   # install for PG 15

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'timescaledb';

Create this extension with:

CREATE EXTENSION timescaledb;

Usage

Sources: README, TimescaleDB 2.27.2 release, 2.27.2 changelog, CREATE TABLE API, create_hypertable() API, continuous aggregate API, add_columnstore_policy() API, GUCs

timescaledb is a PostgreSQL extension for time-series and event analytics. The current docs emphasize CREATE TABLE ... WITH (tsdb.hypertable), continuous aggregates, automation jobs, and moving chunks into the columnstore.

Hypertables

CREATE EXTENSION timescaledb;

CREATE TABLE ts_test (
  ts timestamptz NOT NULL,
  id bigint,
  v integer
) WITH (
  tsdb.hypertable,
  tsdb.orderby = 'ts DESC'
);

To convert an existing PostgreSQL table, use the generalized hypertable API:

CREATE TABLE ts_existing (
  ts timestamptz NOT NULL,
  id bigint,
  v integer
);
SELECT create_hypertable('ts_existing', by_range('ts'));

• CREATE TABLE ... WITH (tsdb.hypertable) has been documented since TimescaleDB 2.20.0 and is the best-practice path for new hypertables.
• For TimescaleDB 2.23.0 and later, the first TIMESTAMP or TIMESTAMPTZ column is selected automatically as the partition column unless more than one candidate makes the choice ambiguous.
• create_hypertable() still works for converting existing tables.

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.
• The continuous aggregate WITH clause supports timescaledb.materialized_only; the current API default is TRUE, so real-time aggregation is not enabled unless configured otherwise.

Columnstore

CREATE TABLE crypto_ticks (
  "time" timestamptz,
  symbol text,
  price double precision,
  day_volume numeric
) WITH (
  tsdb.hypertable,
  tsdb.segmentby = 'symbol',
  tsdb.orderby = 'time DESC'
);

CALL add_columnstore_policy('crypto_ticks', after => INTERVAL '60 days');

• CREATE TABLE ... WITH (tsdb.hypertable) enables columnstore by default unless tsdb.columnstore = false.
• add_columnstore_policy() replaces the older add_compression_policy() API and requires either after or created_before, not both.
• Bloom filters are enabled by default for new columnstore chunks. Existing chunks need recompression before they have bloom indexes.

Relevant GUCs

SET timescaledb.enable_direct_compress_insert = on;
SET timescaledb.enable_cagg_rewrites = on;
SET timescaledb.enable_columnar_scan_filter_pushdown = on;

timescaledb.enable_direct_compress_insert and timescaledb.enable_direct_compress_copy enable tech-preview direct compression during ingestion. TimescaleDB 2.27.0 adds timescaledb.enable_cagg_rewrites and timescaledb.cagg_rewrites_debug_info, and documents timescaledb.enable_columnar_scan_filter_pushdown as enabled by default.

Caveats

• This project’s CSV tracks TimescaleDB 2.27.2 for PostgreSQL 15-18.
• TimescaleDB 2.27.0 adds Hypercore columnstore performance work: vectorized filters, bloom-filter pruning for UPDATE/DELETE equality predicates, and bloom-filter pruning for UPSERT.
• The 2.27.1 and 2.27.2 bugfix releases fix columnstore and catalog edge cases, including wrong results or crashes for grouped columnar scans, skipping ColumnarIndexScan when quals contain subplans or unsupported grouping forms, composite bloom-filter migration scripts, orphaned compression settings, and job/policy information leaks.
• The 2.27.0 release notes list backward-incompatible upgrade caveats for affected compressed int2 bloom sparse indexes and for composite bloom metadata generated by v2.26; 2.27.1 adds the composite bloom-filter migration scripts.
• The 2.27.0 release notes announce that the June 2026 TimescaleDB release is planned to be the last version supporting PostgreSQL 15.