> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Analyse data distribution

> Use percentile approximation to understand data distribution in large datasets

export const PG = 'Postgres';

export const TOOLKIT_LONG = 'TimescaleDB Toolkit';

export const TIMESCALE_DB = 'TimescaleDB';

export const SERVICE_LONG = 'Tiger Cloud service';

export const SELF_LONG = 'self-hosted TimescaleDB';

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. {TIMESCALE_DB} 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_DB} uses `uddsketch`, but you can also choose to use `tdigest`.

<Info>
  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.
</Info>

## Approximation algorithms

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

* **[`uddsketch`][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 {PG}'s [`percentile_disc`][percentile_cont] definition)

* **[`tdigest`][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 {PG}'s [`percentile_cont`][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 [{SERVICE_LONG}][create-service] with Real-time analytics enabled.<p />

  You need [your connection details][connection-info]. This procedure also
  works for [{SELF_LONG}][enable-timescaledb].

[create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services

[enable-timescaledb]: /deploy-and-operate/self-hosted/install-and-update/install-self-hosted

[connection-info]: /integrations/find-connection-details

## 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**

   ```sql theme={"dark"}
   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:

   ```sql theme={"dark"}
   -- 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()`][percentile_agg] to create the aggregate:

   ```sql theme={"dark"}
   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()`][approx_percentile] and [`rollup()`][rollup] to query the aggregated data:

   ```sql theme={"dark"}
   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:

   ```sql theme={"dark"}
   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][blog-percentile-approx].

[approx_percentile]: /api-reference/timescaledb-toolkit/percentile-approximation/approx_percentile

[blog-percentile-approx]: https://www.tigerdata.com/blog/how-percentile-approximation-works-and-why-its-more-useful-than-averages

[percentile_agg]: /api-reference/timescaledb-toolkit/percentile-approximation/percentile_agg

[percentile_cont]: https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE

[rollup]: /api-reference/timescaledb-toolkit/percentile-approximation/rollup

[tdigest]: /api-reference/timescaledb-toolkit/percentile-approximation/tdigest

[uddsketch]: /api-reference/timescaledb-toolkit/percentile-approximation/uddsketch