Analyse data distribution

Use percentile approximation to understand data distribution in large datasets

Percentiles are useful for understanding the distribution of data. The fiftieth percentile is the point at which half of your data is greater and half is lesser. The tenth percentile is the point at which 90% of the data is greater, and 10% is lesser. The ninety-ninth percentile is the point at which 1% is greater, and 99% is lesser.

The fiftieth percentile, or median, is often a more useful measure than the average, especially when your data contains outliers. Outliers can dramatically change the average, but do not affect the median as much. For example, if you have three rooms in your house and two of them are 40℉ (4℃) and one is 130℉ (54℃), the average room temperature is 70℉ (21℃), which doesn’t tell you much. However, the fiftieth percentile temperature is 40℉ (4℃), which tells you that at least half your rooms are at refrigerator temperatures (also, you should probably get your heating checked!)

Percentiles are sometimes avoided because calculating them requires more CPU and memory than an average or other aggregate measures. This is because an exact computation of the percentile needs the full dataset as an ordered list. uses approximation algorithms to calculate a percentile without requiring all of the data. This also makes them more compatible with continuous aggregates. By default, uses uddsketch, but you can also choose to use tdigest.

Technically, a percentile divides a group into 100 equally sized pieces, while a quantile divides a group into an arbitrary number of pieces. Because we don’t always use exactly 100 buckets, “quantile” is the more technically correct term in this case. However, we use the word “percentile” because it’s a more common word for this type of function.

Approximation algorithms

provides two algorithms for percentile approximation: uddsketch and tdigest. Each algorithm has different features that make one better than another depending on your use case.

• uddsketch: The default algorithm. It uses exponentially sized buckets to guarantee the approximation falls within a known error range, relative to the true discrete percentile. This algorithm offers the ability to tune the size and maximum error target of the sketch.

  Advantages:

  • Stable bucketing function that always returns the same percentile estimate for the same data, regardless of ordering or re-aggregation
  • Guaranteed relative error bounds that are easier to characterize
  • Smaller memory and disk footprint than tdigest
  • Simpler to increase accuracy by adding more buckets

  Considerations:

  • Uses exponential bucketing, which can cause varying absolute errors if the dataset covers a large range. For example, if data is evenly distributed over [1,100], estimates at the high end have about 100 times the absolute error of those at the low end
  • Provides discrete percentile estimates (using ‘s percentile_disc definition)

• tdigest: Buckets data more aggressively toward the center of the quantile range, giving it greater accuracy at the tails of the range, around 0.001 or 0.995.

  Advantages:

  • Optimized for accurate estimates at the extremes (for example, ninety-ninth percentiles)
  • More stable absolute error across the data range
  • Provides continuous percentile estimates (using ‘s percentile_cont definition)

  Considerations:

  • Builds incremental buckets based on averages of nearby points, which can result in subtle differences in estimates unless order and batching are strictly controlled
  • More difficult to calculate precise error bars, especially when merging multiple sub-digests
  • Less accurate for median estimates compared to uddsketch

Choose the right algorithm

Consider these factors when choosing between uddsketch and tdigest:

• Percentile targets: Use tdigest if you need accurate ninety-ninth percentiles. Use uddsketch for accurate median estimates.
• Stability requirements: Use uddsketch if you need consistent estimates across different aggregation orders.
• Error characterization: Use uddsketch if you need well-defined error bounds. Use tdigest if you need stable absolute errors across large data ranges.
• Memory constraints: Use uddsketch for smaller memory and disk footprints.

If your use case does not get a clear benefit from using tdigest, the default uddsketch is your best choice.

Prerequisites

To follow the steps on this page:

• Create a target with Real-time analytics enabled.

  You need your connection details. This procedure also works for .

Calculate percentiles

This example uses a response_times table that tracks how long a server takes to respond to API calls.

1. Create the response_times hypertable

   CREATE TABLE response_times (
     ts TIMESTAMPTZ NOT NULL,
     endpoint TEXT,
     response_time_ms DOUBLE PRECISION
   ) WITH (tsdb.hypertable);
   

2. Insert sample data

   Generate response times with occasional slow responses to demonstrate percentile analysis:

   -- Insert normal response times (50-200ms)
   INSERT INTO response_times (ts, endpoint, response_time_ms)
   SELECT
     time,
     CASE (random() * 3)::int
       WHEN 0 THEN '/api/users'
       WHEN 1 THEN '/api/orders'
       ELSE '/api/products'
     END as endpoint,
     50 + random() * 150 as response_time_ms
   FROM generate_series(
     now() - interval '60 days',
     now(),
     interval '1 minute'
   ) AS g1(time);
   
   -- Insert some slow responses (500-2000ms) to create outliers
   INSERT INTO response_times (ts, endpoint, response_time_ms)
   SELECT
     time,
     '/api/orders' as endpoint,
     500 + random() * 1500 as response_time_ms
   FROM generate_series(
     now() - interval '60 days',
     now(),
     interval '30 minutes'
   ) AS g1(time)
   WHERE random() < 0.1;
   

3. Create a continuous aggregate with daily percentile aggregates

   Use percentile_agg() to create the aggregate:

   CREATE MATERIALIZED VIEW response_times_daily
   WITH (timescaledb.continuous)
   AS SELECT
     time_bucket('1 day'::interval, ts) as bucket,
     endpoint,
     percentile_agg(response_time_ms)
   FROM response_times
   GROUP BY bucket, endpoint;
   

4. Query the ninety-fifth percentile over the last 30 days

   Use approx_percentile() and rollup() to query the aggregated data:

   SELECT
     endpoint,
     approx_percentile(0.95, rollup(percentile_agg)) as p95_response_time
   FROM response_times_daily
   WHERE bucket >= time_bucket('1 day'::interval, now() - '30 days'::interval)
   GROUP BY endpoint
   ORDER BY p95_response_time DESC;
   

5. Create an alert for slow responses

   Detect requests that exceed the ninety-fifth percentile threshold:

   WITH threshold as (
     SELECT approx_percentile(0.95, rollup(percentile_agg)) as p95
     FROM response_times_daily
     WHERE bucket >= time_bucket('1 day'::interval, now() - '30 days'::interval)
   )
   SELECT
     endpoint,
     count(*) as slow_requests
   FROM response_times
   WHERE ts > now() - '1 minute'::interval
     AND response_time_ms > (SELECT p95 FROM threshold)
   GROUP BY endpoint;
   

For more information about how percentile approximation works, read the percentile approximation blog.