tdigest() functions

Introduction

Estimate the value at a given percentile, or the percentile rank of a given value, using the t-digest algorithm. This estimation is more memory- and CPU-efficient than an exact calculation using PostgreSQL’s percentile_cont and percentile_disc functions.

tdigest is one of two advanced percentile approximation aggregates provided in TimescaleDB Toolkit. It is a space-efficient aggregation, and it provides more accurate estimates at extreme quantiles than traditional methods.

tdigest is somewhat dependent on input order. If tdigest is run on the same data arranged in different order, the results should be nearly equal, but they are unlikely to be exact.

The other advanced percentile approximation aggregate is uddsketch, which produces stable estimates within a guaranteed relative error. If you aren’t sure which to use, try the default percentile estimation method, percentile_agg. It uses the uddsketch algorithm with some sensible defaults.

For more information about percentile approximation algorithms, see the algorithms overview.

Related hyperfunction groups

• uddsketch()

Two-step aggregation

Hide content

This group of functions uses the two-step aggregation pattern.

Rather than calculating the final result in one step, you first create an intermediate aggregate by using the aggregate function.

Then, use any of the accessors on the intermediate aggregate to calculate a final result. You can also roll up multiple intermediate aggregates with the rollup functions.

The two-step aggregation pattern has several advantages:

1. More efficient because multiple accessors can reuse the same aggregate
2. Easier to reason about performance, because aggregation is separate from final computation
3. Easier to understand when calculations can be rolled up into larger intervals, especially in window functions and continuous aggregates
4. Can perform retrospective analysis even when underlying data is dropped, because the intermediate aggregate stores extra information not available in the final result

To learn more, see the blog post on two-step aggregates.

Functions in this group

Aggregate

tdigest

Aggregate data in a tdigest for further calculation of percentile estimates

Accessor

approx_percentile

Estimate the value at a given percentile from a tdigest

approx_percentile_rank

Estimate the percentile of a given value from a tdigest

mean

Calculate the exact mean from values in a tdigest

num_vals

Get the number of values contained in a tdigest

Rollup

rollup

Roll up multiple tdigests

Function details

tdigest()

Stabilized in Toolkit v1.0.0

Hide content

`

tdigest(

`

  buckets INTEGER,
  value DOUBLE PRECISION

`

) RETURNS TDigest

`

This is the first step for calculating approximate percentiles with the tdigest algorithm. Use tdigest to create an intermediate aggregate from your raw data. This intermediate form can then be used by one or more accessors in this group to compute final results.

Optionally, multiple such intermediate aggregate objects can be combined using rollup() before an accessor is applied.

Required arguments

Returns

Examples

Given a table called samples, with a column called data, build a tdigest using the data column. Use 100 buckets for the approximation:

SELECT tdigest(100, data) FROM samples;

approx_percentile()

Stabilized in Toolkit v1.0.0

Hide content

`

approx_percentile(

`

  percentile DOUBLE PRECISION,
  tdigest  TDigest

`

) RETURNS DOUBLE PRECISION

`

Estimate the approximate value at a percentile from a tdigest aggregate.

Required arguments

Returns

Examples

Estimate the value at the first percentile, given a sample containing the numbers from 0 to 100:

SELECT
  approx_percentile(0.01, tdigest(data))
FROM generate_series(0, 100) data;
approx_percentile
-------------------
            0.999

approx_percentile_rank()

Stabilized in Toolkit v1.0.0

Hide content

`

approx_percentile_rank(

`

  value DOUBLE PRECISION,
  digest TDigest

`

) RETURNS DOUBLE PRECISION

`

Estimate the the percentile at which a given value would be located.

Required arguments

Returns

Examples

Estimate the percentile rank of the value 99, given a sample containing the numbers from 0 to 100:

SELECT
  approx_percentile_rank(99, tdigest(data))
FROM generate_series(0, 100) data;
approx_percentile_rank
----------------------------
        0.9851485148514851

mean()

Stabilized in Toolkit v1.0.0

Hide content

`

mean(

`

  digest TDigest

`

) RETURNS DOUBLE PRECISION

`

Calculate the exact mean of the values in a tdigest aggregate. Unlike percentile calculations, the mean calculation is exact. This accessor allows you to calculate the mean alongside percentiles, without needing to create two separate aggregates from the same raw data.

Required arguments

Returns

Examples

Calculate the mean of the integers from 0 to 100:

SELECT mean(tdigest(data))
FROM generate_series(0, 100) data;
mean
------
50

num_vals()

Stabilized in Toolkit v1.0.0

Hide content

`

num_vals(

`

  digest TDigest

`

) RETURNS DOUBLE PRECISION

`

Get the number of values contained in a tdigest aggregate. This accessor allows you to calculate a count alongside percentiles, without needing to create two separate aggregates from the same raw data.

Required arguments

Returns

Examples

Count the number of integers from 0 to 100:

SELECT num_vals(tdigest(data))
FROM generate_series(0, 100) data;
num_vals
-----------
    101

rollup()

Stabilized in Toolkit v1.0.0

Hide content

`

rollup(

`

  digest TDigest

`

) RETURNS TDigest

`

Combine multiple intermediate tdigest aggregates, produced by tdigest, into a single intermediate tdigest aggregate. For example, you can use rollup to combine tdigests from 15-minute buckets into daily buckets.

Required arguments

Returns

Extended examples

Aggregate and roll up percentile data to calculate daily percentiles

Create an hourly continuous aggregate that contains a percentile aggregate:

CREATE MATERIALIZED VIEW foo_hourly
WITH (timescaledb.continuous)
AS SELECT
    time_bucket('1 h'::interval, ts) as bucket,
    tdigest(value) as tdigest
FROM foo
GROUP BY 1;

You can use accessors to query directly from the continuous aggregate for hourly data. You can also roll the hourly data up into daily buckets, then calculate approximate percentiles:

SELECT
    time_bucket('1 day'::interval, bucket) as bucket,
    approx_percentile(0.95, rollup(tdigest)) as p95,
    approx_percentile(0.99, rollup(tdigest)) as p99
FROM foo_hourly
GROUP BY 1;