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

sqlCopy code
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), ...).

sqlCopy code
-- 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

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

Percentiles

sqlCopy code
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

sqlCopy code
-- 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.

sqlCopy code
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:

sqlCopy code
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.

sqlCopy code
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+.

sqlCopy code
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.

  sqlCopy code
  SELECT bucket, queue,
         interpolated_delta(ga, bucket, INTERVAL '15 minutes',
                            LAG(ga) OVER w, LEAD(ga) OVER w) AS net_change
  FROM (SELECT time_bucket('15 minutes', ts) AS bucket, queue,
               gauge_agg(ts, depth) AS ga
        FROM queue_depth GROUP BY 1, 2) t
  WINDOW w AS (PARTITION BY queue ORDER BY bucket);
  

• Not available on gauges: num_resets, and first_val/last_val. Use time_weight if you need first/last values or a time-weighted average of the gauge.

• Choosing gauge or counter: if a value only ever falls on a restart, use counter_agg. If falls are real, such as memory being freed, use gauge_agg. stats_agg or time_weight are often better for "average level". gauge_agg answers "how much and how fast did it change".

Heartbeats and Uptime

heartbeat_agg(ts, start, range, heartbeat_interval): each heartbeat at ts marks the system live until ts + heartbeat_interval. start and range define the window that the aggregate covers.

sqlCopy code
-- Store per day, e.g. as continuous aggregate daily_heartbeats
SELECT time_bucket('1 day', ts) AS day, service,
       heartbeat_agg(ts, time_bucket('1 day', ts), '1 day', '2 minutes') AS hb
FROM pings
GROUP BY day, service;

SELECT day, service,
       hb -> uptime()      AS uptime,
       hb -> downtime()    AS downtime,
       hb -> num_gaps()    AS outages,
       hb -> dead_ranges() AS outage_ranges   -- setof (start, end)
FROM daily_heartbeats;

• Make start and range match the bucket. Otherwise time outside the data is counted as downtime.
• Use interpolated_uptime(hb, LAG(hb) OVER w) so a heartbeat just before the bucket boundary counts toward the next bucket.
• live_at(hb, timestamp) checks liveness at one point in time. rollup(hb) merges adjacent windows.

State Tracking

state_agg(ts, state) takes a text or bigint state and records how long each state lasted.

sqlCopy code
-- One summary per machine per day; store it, e.g. as continuous aggregate machine_states_daily
SELECT time_bucket('1 day', ts) AS day, machine_id, state_agg(ts, status) AS sa
FROM machine_status
GROUP BY day, machine_id;

SELECT day, machine_id,
       sa -> duration_in('running') AS running_time,
       sa -> duration_in('error')   AS error_time
FROM machine_states_daily;

-- Full timeline: one row per contiguous period
SELECT machine_id, (sa -> state_timeline()).*   -- state, start_time, end_time
FROM machine_states_daily
WHERE machine_id = 1
ORDER BY start_time;

-- All periods in one state; time in a state within a sub-range
SELECT machine_id, (sa -> state_periods('error')).* FROM machine_states_daily;
SELECT day, machine_id, duration_in(sa, 'running', day + INTERVAL '8 hours', '8 hours') AS running_8_to_16
FROM machine_states_daily;

• state_at(sa, ts) returns the state at a given instant. into_values(sa) returns the total duration per state.
• Across bucket boundaries, use interpolated_duration_in, interpolated_state_timeline, and interpolated_state_periods with LAG(sa).
• The final state's duration ends at the last observed timestamp. Add a closing row, or use the interpolated variants, if the final state should extend to the end of the bucket.

Financial: Candlesticks

sqlCopy code
-- From raw ticks; store, e.g. as continuous aggregate one_min_candles
SELECT time_bucket('1 minute', ts) AS bucket, symbol,
       candlestick_agg(ts, price, volume) AS candle
FROM ticks GROUP BY bucket, symbol;

-- Accessors (also work after rollup to 1 hour / 1 day)
SELECT bucket, symbol,
       candle -> open() AS open, candle -> high() AS high,
       candle -> low()  AS low,  candle -> close() AS close,
       volume(candle) AS volume, vwap(candle) AS vwap   -- function form only
FROM one_min_candles;

volume and vwap have no arrow form. Call them as functions. Use candlestick(ts, open, high, low, close, volume) to wrap bars that are already aggregated, for example from an upstream feed. open_time, high_time, low_time, and close_time return when each value occurred.

Approximate Distinct, Top-N, Frequency

sqlCopy code
-- Approx COUNT DISTINCT; hyperloglog buckets are rounded up to a power of 2
SELECT approx_count_distinct(user_id) -> distinct_count() FROM events;
SELECT hyperloglog(32768, user_id) AS hll FROM events GROUP BY day;   -- store, then rollup(hll) -> distinct_count()

-- 5 slowest requests with the full row
SELECT into_values(max_n_by(latency_ms, r, 5), NULL::requests)
FROM requests r;

-- 5 largest values as an array
SELECT max_n(latency_ms, 5) -> into_array() FROM requests;

-- 10 most common error codes
SELECT topn(mcv_agg(10, error_code)) FROM errors;

• approx_count_distinct(v) uses default precision. Use hyperloglog(buckets, v) to trade memory for accuracy. stderror() reports the expected relative error.
• max_n_by(sort_value, row, n) needs the row type in into_values(agg, NULL::rowtype) to decode the result.
• mcv_agg(n, value) is approximate and assumes a skewed (Zipf-like) distribution. Pass mcv_agg(n, skew, value) to tune the assumed skew (default 1.1).

Downsampling for Charts

sqlCopy code
-- Reduce 1M points to ~500 that preserve the visual shape
SELECT time, value
FROM unnest((SELECT lttb(ts, value, 500) FROM readings WHERE sensor_id = 42));

-- Smooth out periodic noise for display
SELECT time, value
FROM unnest((SELECT asap_smooth(ts, value, 800) FROM readings WHERE sensor_id = 42));

lttb keeps actual points. asap_smooth produces smoothed values. Use both for visualization only, not for further statistics.

Continuous Aggregates with Hyperfunctions

Store summaries in the continuous aggregate, then roll them up and apply accessors at query time.

sqlCopy code
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts) AS bucket,
       host,
       percentile_agg(latency_ms)          AS latency_pct,
       stats_agg(cpu)                      AS cpu_stats,
       time_weight('Linear', ts, mem_used) AS mem_tw,
       counter_agg(ts, bytes_total)        AS bytes_ctr
FROM metrics
GROUP BY bucket, host;

-- Hierarchical: daily built from hourly via rollup
CREATE MATERIALIZED VIEW metrics_daily
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', bucket) AS bucket,
       host,
       rollup(latency_pct) AS latency_pct,
       rollup(cpu_stats)   AS cpu_stats,
       rollup(mem_tw)      AS mem_tw,
       rollup(bytes_ctr)   AS bytes_ctr
FROM metrics_hourly
GROUP BY 1, host;

-- Distribution summaries: any percentile/statistic across all hosts
SELECT bucket,
       rollup(latency_pct) -> approx_percentile(0.99) AS p99_all_hosts,
       rollup(cpu_stats)   -> average()               AS avg_cpu
FROM metrics_daily
WHERE bucket > now() - INTERVAL '30 days'
GROUP BY bucket;

-- Time-series summaries: evaluate per host, then combine the results
SELECT bucket,
       SUM(bytes_ctr -> rate()) AS fleet_bytes_per_sec,
       AVG(mem_tw -> average()) AS avg_host_mem
FROM metrics_daily
WHERE bucket > now() - INTERVAL '30 days'
GROUP BY bucket;

Summaries are a few hundred bytes to a few KB. That is larger than a single float, but it replaces many precomputed columns (p50, p90, p95, p99, ...) and stays correct under rollup.

Common Mistakes

• Averaging precomputed statistics over time (AVG(p95_hourly), AVG(avg_hourly) for a daily value): store summaries and use rollup.
• Rolling up time-series summaries across entities (rollup(counter_agg), rollup(time_weight), etc. without grouping by entity): this fails with an ordering error. Evaluate per entity, then aggregate the results.
• Feeding time-series aggregates out-of-order rows from a partially compressed chunk (ERROR: out of order points): rows inserted into a compressed chunk stay in the rowstore until it is recompressed, so a scan can return them interleaved with columnstore rows. counter_agg, gauge_agg, time_weight, and similar aggregates then see time ranges that overlap. Recompress the chunk (compress_chunk), or sort the input first: SELECT counter_agg(ts, v) FROM (SELECT * FROM metrics WHERE ... ORDER BY ts) t.
• Using AVG() on irregularly sampled data: use time_weight.
• Using max(v) - min(v) or last - first on counters: this breaks on resets. Use counter_agg -> delta().
• Using counter_agg on gauges: a drop is treated as a reset and inflates the delta. Use gauge_agg.
• Building continuous aggregates on toolkit_experimental.*: they are dropped on extension update.
• Running interpolated accessors without ordering: the LAG/LEAD window must be PARTITION BY the entity and ORDER BY the bucket.
• Mismatched heartbeat_agg window: start/range must match the bucket, or the extra time counts as downtime.
• Expecting exact results from sketches: percentile_agg, tdigest, hyperloglog, and mcv_agg are approximate. Report this when precision matters.

Reference

Use the search_docs tool with source tiger (for example "hyperfunctions counter_agg") for full signatures and newer functions. Source: https://github.com/timescale/timescaledb-toolkit