---

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) AS bucket,
       service,
       percentile_agg(latency_ms) -> approx_percentile(0.5)  AS p50,
       percentile_agg(latency_ms) -> approx_percentile(0.99) AS p99
FROM requests
WHERE ts > now() - INTERVAL '1 day'
GROUP BY bucket, service;

-- Several percentiles from one sketch; fraction of requests under 200 ms
WITH s AS (SELECT percentile_agg(latency_ms) AS sketch FROM requests)
SELECT approx_percentile_array(ARRAY[0.5, 0.9, 0.99], sketch),
       approx_percentile_rank(200, sketch)
FROM s;

• percentile_agg(value): default choice. It is a uddsketch with sensible defaults and a bounded relative error.
• uddsketch(size, max_error, value), e.g. uddsketch(200, 0.001, v): guaranteed relative error. Use when you need to tune accuracy or memory.
• tdigest(buckets, value), e.g. tdigest(100, v): more accurate at extreme tails (p99.9). Error is not bounded, and results depend on input order.
• Results are approximate. Use PERCENTILE_CONT when exact values are required and the data is small.

Statistical Summaries

-- Re-aggregatable average/stddev
SELECT device_id,
       stats_agg(temperature) -> average() AS avg_temp,
       stats_agg(temperature) -> stddev()  AS sd_temp
FROM readings GROUP BY device_id;

-- 2D: linear regression of y on x
SELECT stats_agg(power_w, temperature) -> slope() AS watts_per_degree,
       stats_agg(power_w, temperature) -> corr()  AS correlation
FROM readings;

-- Rolling 7-day average from daily summaries (window function over summaries)
SELECT bucket,
       rolling(daily_stats) OVER (ORDER BY bucket ROWS 6 PRECEDING) -> average() AS avg_7d
FROM daily_rollup;

Accessors such as stddev and variance take an optional 'population' or 'sample' argument (default 'sample'). In 2D, stats_agg(y, x) puts the dependent variable first. Use average_x(), average_y(), and similar accessors for per-axis stats.

Time-Weighted Averages

AVG() overweights periods with dense sampling. time_weight weights each value by how long it was in effect.

SELECT time_bucket('1 hour', ts) AS bucket, sensor_id,
       time_weight('Linear', ts, value) -> average() AS tw_avg,
       time_weight('LOCF', ts, value) -> integral('hour') AS value_hours
FROM readings
GROUP BY bucket, sensor_id;

• 'Linear' interpolates between points. Use it for continuously varying signals such as temperature.
• 'LOCF' (last observation carried forward) holds each value until the next one. Use it for set-points, prices, and state-like values.
• average() returns NULL for a bucket with a single point, because there is no time span to weight. Use interpolated_average (below), or a larger bucket, for sparse series.
• integral(unit) takes 'microsecond', 'millisecond', 'second' (default), 'minute', or 'hour'.

Bucket edges. A plain average() only sees points inside the bucket. To account for values that span bucket boundaries, pass the neighbouring buckets' summaries:

WITH t AS (
  SELECT time_bucket('1 hour', ts) AS bucket, sensor_id,
         time_weight('LOCF', ts, value) AS tw
  FROM readings GROUP BY 1, 2
)
SELECT bucket, sensor_id,
       tw -> interpolated_average(
               bucket, INTERVAL '1 hour',
               LAG(tw)  OVER w,
               LEAD(tw) OVER w) AS tw_avg
FROM t
WINDOW w AS (PARTITION BY sensor_id ORDER BY bucket);

Counters and Gauges

Counters only increase and reset to zero on restart (Prometheus counters, byte totals, request totals). counter_agg detects resets and corrects for them.

SELECT time_bucket('5 minutes', ts) AS bucket, host,
       counter_agg(ts, bytes_total) -> delta()        AS bytes,
       counter_agg(ts, bytes_total) -> rate()         AS bytes_per_sec,
       counter_agg(ts, bytes_total) -> num_resets()   AS restarts,
       counter_agg(ts, bytes_total) -> irate_right()  AS instant_rate
FROM metrics
GROUP BY bucket, host;

• rate() is per second, measured between the first and last sample in the bucket.
• For Prometheus-compatible extrapolation to the bucket edges, give the aggregate bounds: counter_agg(ts, v, tstzrange(bucket, bucket + '5 min')) -> extrapolated_rate('prometheus'). You can also set bounds afterwards with with_bounds(summary, range).
• For edge-accurate deltas across buckets, use interpolated_delta(bucket, interval, LAG(summary) OVER w, LEAD(summary) OVER w) or interpolated_rate(...), following the interpolated_average pattern above.

Gauges

Gauges go up and down: memory in use, queue depth, temperature, connection count. gauge_agg(ts, value) gives counter-style change metrics but treats every drop as a real decrease, not a reset. Requires toolkit 1.25+.

SELECT time_bucket('15 minutes', ts) AS bucket, queue,
       gauge_agg(ts, depth) -> delta()        AS net_change,     -- last - first
       gauge_agg(ts, depth) -> rate()         AS change_per_sec, -- delta / time_delta
       gauge_agg(ts, depth) -> slope()        AS trend_per_sec,  -- least-squares fit
       gauge_agg(ts, depth) -> num_changes()  AS changes,
       gauge_agg(ts, depth) -> irate_right()  AS latest_rate     -- last two points
FROM queue_depth
GROUP BY bucket, queue;

• Accessors (stable in 1.25): delta, rate, time_delta, idelta_left/idelta_right, irate_left/irate_right, slope, intercept, corr, num_changes, num_elements, zero_time (as -> zero_time(); the function form is gauge_zero_time(summary)), and extrapolated_delta/extrapolated_rate. The extrapolated accessors take no method argument and need bounds, from gauge_agg(ts, v, tstzrange(...)) or with_bounds(summary, range).

• Edge-accurate deltas across buckets: call interpolated_delta or interpolated_rate in function form, with the summary first. The arrow form is not stable for gauges.

  SELECT bucket, queue,
         interpolated_delta(ga, bucket, INTERVAL '15 minutes',