Timescaledb Hyperfunctions
Use this skill when writing analytical SQL over time-series data with the TimescaleDB...
Install
npx skills add timescale/pg-aiguide --skill timescaledb-hyperfunctions -g -a claude-code -y- Repository
- timescale/pg-aiguide1.9k
- Category
- Data and ML
- Updated
- 8d ago
- License
- Apache-2.0
- Context cost
- Mediumabout 3,000 tokens
- Runs scripts
- No, instructions only
What it does
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
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:
- An aggregate builds a compact summary (
percentile_agg(val)→uddsketch,time_weight(...)→timeweightsummary, ...). - 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:
