/timescaledb-hyperfunctions

name: timescaledb-hyperfunctions
description: |
  Use this skill when writing analytical SQL over time-series data with the TimescaleDB Toolkit (timescaledb_toolkit extension) hyperfunctions: approximate percentiles, statistical summaries, time-weighted averages, counter/gauge rates, uptime/heartbeat tracking, state durations, OHLC candlesticks, approximate distinct counts, top-N, and downsampling.

  **Trigger when user asks to:**
  - Compute percentiles/medians/p95/p99 over large or rolled-up time-series data
  - Compute rates or deltas from monotonic counters (Prometheus-style) or gauges
  - Compute time-weighted averages or integrals over irregularly sampled data
  - Track uptime/downtime from heartbeats, or time spent in each state
  - Build OHLC/candlestick or VWAP data for financial ticks
  - Store re-aggregatable summaries in continuous aggregates (two-step aggregation, rollup)
  - Approximate COUNT DISTINCT, find top-N / most frequent values, or downsample for charts

  **Keywords:** timescaledb_toolkit, hyperfunctions, percentile_agg, uddsketch, tdigest, approx_percentile, stats_agg, time_weight, counter_agg, gauge_agg, heartbeat_agg, state_agg, candlestick_agg, hyperloglog, approx_count_distinct, min_n, max_n, mcv_agg, lttb, asap_smooth, rollup, two-step aggregation
license: Apache-2.0
compatibility: Requires PostgreSQL 15+ with TimescaleDB and the timescaledb_toolkit extension (1.16+; gauge_agg needs 1.25+)
metadata:
  author: tigerdata

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.

Choosing a Function

| Question | Aggregate | Key accessors | |---|---|---| | p50/p95/p99, median | `percentile_agg`, `uddsketch`, `tdigest` | `approx_percentile`, `approx_percentile_array`, `approx_percentile_rank`, `mean`, `error` | | avg/stddev/variance that can be rolled up; regression | `stats_agg` (1D or 2D) | `average`, `stddev`, `variance`, `skewness`, `kurtosis`, `num_vals`, `sum`; 2D: `slope`, `intercept`, `corr`, `determination_coeff`, `covariance` | | Average over irregular sampling | `time_weight` | `average`, `integral`, `interpolated_average`, `first_val`, `last_val` | | Rate/delta of monotonic counters with resets | `counter_agg` | `delta`, `rate`, `extrapolated_rate`, `irate_right`, `num_resets`, `interpolated_rate` | | Rate/delta/trend of gauges (no reset handling) | `gauge_agg` (1.25+) | `delta`, `rate`, `extrapolated_rate`, `irate_right`, `slope`, `num_changes`, `interpolated_delta` | | Uptime/downtime from heartbeats | `heartbeat_agg` | `uptime`, `downtime`, `live_ranges`, `dead_ranges`, `live_at`, `num_gaps`, `interpolated_uptime` | | Time spent in each state | `state_agg` | `duration_in`, `state_timeline`, `state_periods`, `state_at`, `into_values` | | OHLC / VWAP | `candlestick_agg` (ticks), `candlestick` (pre-aggregated bars) | `open`, `high`, `low`, `close`, `volume`, `vwap`, `open_time`, ... | | Approx COUNT DISTINCT | `hyperloglog`, `approx_count_distinct` | `distinct_count`, `stderror` | | Top-N / bottom-N values (with rows) | `max_n`, `min_n`, `max_n_by`, `min_n_by` | `into_values`, `into_array` | | Most frequent values | `mcv_agg` | `topn`, `max_frequency`, `min_frequency`, `into_values` | | Downsample for charting | `lttb`, `asap_smooth` | `unnest` |

TimescaleDB core (not the toolkit) provides `time_bucket`, `time_bucket_gapfill`, `locf`, `interpolate`, `first`, and `last`. Combine them freely with hyperfunctions.

Percentiles

SELECT time_bucket('5 minutes', ts