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

  1. 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;
  2. 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);
  3. 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

Related Content