Timescale uses approximation algorithms to calculate a percentile without requiring all of the data. This also makes them more compatible with continuous aggregates.
By default, Timescale Toolkit uses
uddsketch, but you can also choose to use
tdigest. For more information about these algorithms, see the
advanced aggregation methods documentation.
In this procedure, we use an example table called
response_times that contains
information about how long a server takes to respond to API calls.
psqlprompt, create a continuous aggregate that computes the daily aggregates:CREATE MATERIALIZED VIEW response_times_dailyWITH (timescaledb.continuous)AS SELECTtime_bucket('1 day'::interval, ts) as bucket,percentile_agg(response_time_ms)FROM response_timesGROUP BY 1;
Re-aggregate the aggregate to get the last 30 days, and look for the ninety-fifth percentile:SELECT approx_percentile(0.95, percentile_agg(percentile_agg)) as thresholdFROM response_times_dailyWHERE bucket >= time_bucket('1 day'::interval, now() - '30 days'::interval);
You can also create an alert:WITH t as (SELECT approx_percentile(0.95, percentile_agg(percentile_agg)) as thresholdFROM response_times_dailyWHERE bucket >= time_bucket('1 day'::interval, now() - '30 days'::interval))SELECT count(*)FROM response_timesWHERE ts > now()- '1 minute'::intervalAND response_time_ms > (SELECT threshold FROM t);
For more information about percentile approximation API calls, see the hyperfunction API documentation.
Found an issue on this page?Report an issue!