timescaledb-hyperfunctions

TimescaleDB Toolkit Hyperfunctions

Hyperfunctions are SQL aggregates and accessors from the timescaledb_toolkit extension for analysis that plain PostgreSQL aggregates handle badly: percentiles that can be re-aggregated, rates over resetting counters, averages over irregular samples, uptime, state durations, and more.

Setup

CREATE EXTENSION IF NOT EXISTS timescaledb_toolkit;   -- preinstalled on Tiger Cloud
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb_toolkit';
ALTER EXTENSION timescaledb_toolkit UPDATE;           -- after upgrading the package

Only use functions from the default schema in persistent objects. Anything in the toolkit_experimental schema can change between releases, and ALTER EXTENSION ... UPDATE drops views, continuous aggregates, and functions that depend on it. All functions in this skill are stable.

The Two-Step Pattern (read this first)

Nearly every hyperfunction works in two steps:

1. An aggregate builds a compact summary (percentile_agg(val) → uddsketch, time_weight(...) → timeweightsummary, ...).
2. An accessor extracts a result from the summary (approx_percentile(0.95, summary), average(summary), ...).

-- Function-call style
SELECT approx_percentile(0.95, percentile_agg(latency_ms)) FROM requests;

-- Arrow style (same result, reads left to right, chains multiple accessors)
SELECT percentile_agg(latency_ms) -> approx_percentile(0.95) FROM requests;

The pattern exists so summaries can be stored and re-aggregated:

• rollup(summary) merges summaries correctly. Use it to go from hourly to daily buckets.
• Combining entities depends on the summary type. Distribution summaries (percentile_agg, uddsketch, tdigest, stats_agg, hyperloglog, mcv_agg, min_n/max_n) can be rolled up across entities, e.g. one p99 for all hosts. Time-series summaries (time_weight, counter_agg, gauge_agg, heartbeat_agg, state_agg) describe one series. They can only be rolled up over consecutive, non-overlapping time ranges of the same entity: rolling up overlapping summaries from different hosts raises an ordering error. Compute the per-entity result first, then combine the numbers (e.g. SUM of per-host rates for a fleet-wide rate).
• Never average averages, percentiles, or rates. AVG(p95_hourly) is not a daily p95. rollup(hourly_sketch) -> approx_percentile(0.95) is the correct value.
• Store the summary in a continuous aggregate, not the final number. Then any accessor works at query time.