Approximate percentiles

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;

   Copy

2. 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 threshold
   FROM response_times_daily
   WHERE bucket >= time_bucket('1 day'::interval, now() - '30 days'::interval);

   Copy

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);

   Copy