Approximate percentiles
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.
Run an approximate percentage query
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.
Running an approximate percentage query
At the
psql
prompt, create a continuous aggregate that computes the daily aggregates:CREATE MATERIALIZED VIEW response_times_daily WITH (timescaledb.continuous) AS SELECT time_bucket('1 day'::interval, ts) as bucket, percentile_agg(response_time_ms) FROM response_times GROUP BY 1;
Re-aggregate the aggregate to get the last 30 days, and look for the 95th percentile:
SELECT approx_percentile(0.95, percentile_agg(percentile_agg)) as threshold FROM response_times_daily WHERE 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 threshold FROM response_times_daily WHERE bucket >= time_bucket('1 day'::interval, now() - '30 days'::interval)) SELECT count(*) FROM response_times WHERE ts > now()- '1 minute'::interval AND 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!Keywords