uddsketch() and percentile_agg() functions

Introduction

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

uddsketch is one of two advanced percentile approximation aggregates provided in TimescaleDB Toolkit. It produces stable estimates within a guaranteed relative error.

The other advanced percentile approximation aggregate is tdigest, which is more accurate at extreme quantiles, but is somewhat dependent on input order.

If you aren’t sure which aggregate 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

• tdigest()

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

warning

This function group includes some experimental functions. Experimental functions might change or be removed in future releases. We do not recommend using them in production. Experimental functions are marked with an Experimental tag.

Aggregate

uddsketch

Aggregate data in a uddsketch for further calculation of percentile estimates

Alternate aggregate

percentile_agg

Aggregate data in a uddsketch, using some reasonable default values, for further calculation of percentile estimates

Accessor

approx_percentile

Estimate the value at a given percentile from a uddsketch

approx_percentile_array

ExperimentalEstimate the values for an array of given percentiles from a uddsketch

approx_percentile_rank

Estimate the percentile of a given value from a uddsketch

error

Get the maximum relative error for a uddsketch

mean

Calculate the exact mean from values in a uddsketch

num_vals

Get the number of values contained in a uddsketch

Rollup

rollup

Roll up multiple uddsketches

Function details

uddsketch()

Stabilized in Toolkit v1.0.0

Hide content

`

1. uddsketch(

`

1. size INTEGER,
2. max_error DOUBLE PRECISION,
3. value DOUBLE PRECISION

`

1. ) RETURNS UddSketch

`

This is the first step for calculating approximate percentiles with the uddsketch algorithm. Use uddsketch 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.

If you aren’t sure what values to set for size and max_error, try using the alternate aggregate function, percentile_agg(). percentile_agg also creates a UddSketch, but it sets some sensible default values for size and max_error that should work for many use cases.

Required arguments

Returns

Examples

Given a table called samples, with a column called data, build a uddsketch using the data column. Use a maximum of 100 buckets and a relative error of 0.01:

2. SELECT uddsketch(100, 0.01, data) FROM samples;

percentile_agg()

Stabilized in Toolkit v1.0.0

Hide content

`

1. percentile_agg(

`

1. value DOUBLE PRECISION

`

1. ) RETURNS UddSketch

`

This is an alternate first step for calculating approximate percentiles. It provides some added convenience by using some sensible defaults to create a UddSketch. Internally, it calls uddsketch with 200 buckets and a maximum error rate of 0.001.

Use percentile_agg 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

Create a continuous aggregate that stores percentile aggregate objects. These objects can later be used with multiple accessors for retrospective analysis:

2. CREATE MATERIALIZED VIEW foo_hourly

4. WITH (timescaledb.continuous)

6. AS SELECT

8. time_bucket('1 h'::interval, ts) as bucket,

10. percentile_agg(value) as pct_agg

12. FROM foo

14. GROUP BY 1;

approx_percentile()

Stabilized in Toolkit v1.0.0

Hide content

`

1. approx_percentile(

`

1. percentile DOUBLE PRECISION,
2. uddsketch  UddSketch

`

1. ) RETURNS DOUBLE PRECISION

`

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

Required arguments

Returns

Examples

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

2. SELECT

4. approx_percentile(0.01, uddsketch(data))

6. FROM generate_series(0, 100) data;

8. approx_percentile

10. -------------------

12. 0.999

approx_percentile_array()

ExperimentalExperimental TimescaleDB Toolkit functions are not suitable for production environments. They may have bugs and may cause data loss. Click to learn more.

Introduced in Toolkit v1.13.0

Hide content

`

1. approx_percentile_array(

`

1. percentiles DOUBLE PRECISION[],
2. uddsketch  UddSketch

`

1. ) RETURNS DOUBLE PRECISION[]

`

Estimate the approximate values of an array of percentiles from a uddsketch aggregate.

Required arguments

Returns

Examples

Estimate the value at the 90th, 50th, and 20th percentiles, given a sample containing the numbers from 0 to 100:

2. SELECT

4. approx_percentile_array(array[0.9,0.5,0.2], uddsketch(100,0.005,data))

6. FROM generate_series(0, 100) data;

8. approx_percentile_array

10. -------------------

12. {90.0,50.0,20.0}

approx_percentile_rank()

Stabilized in Toolkit v1.0.0

Hide content

`

1. approx_percentile_rank(

`

1. value DOUBLE PRECISION,
2. sketch UddSketch

`

1. ) RETURNS DOUBLE PRECISION

`

Estimate 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:

2. SELECT

4. approx_percentile_rank(99, uddsketch(data))

6. FROM generate_series(0, 100) data;

8. approx_percentile_rank

10. ----------------------------

12. 0.9851485148514851

error()

Stabilized in Toolkit v1.0.0

Hide content

`

1. error(

`

1. sketch UddSketch

`

1. ) RETURNS DOUBLE PRECISION

`

Get the maximum relative error of a uddsketch. The correct (non-estimated) percentile falls within the range defined by approx_percentile(sketch) +/- (approx_percentile(sketch) * error(sketch)).

Required arguments

Returns

Examples

Calculate the maximum relative error when estimating percentiles using uddsketch:

2. SELECT error(uddsketch(data))

4. FROM generate_series(0, 100) data;

6. error

8. -------

10. 0.001

mean()

Stabilized in Toolkit v1.0.0

Hide content

`

1. mean(

`

1. sketch UddSketch

`

1. ) RETURNS DOUBLE PRECISION

`

Calculate the exact mean of the values in a uddsketch. 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:

2. SELECT mean(uddsketch(data))

4. FROM generate_series(0, 100) data;

6. mean

8. ------

10. 50

num_vals()

Stabilized in Toolkit v1.0.0

Hide content

`

1. num_vals(

`

1. sketch UddSketch

`

1. ) RETURNS DOUBLE PRECISION

`

Get the number of values contained in a uddsketch. 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:

2. SELECT num_vals(uddsketch(data))

4. FROM generate_series(0, 100) data;

6. num_vals

8. -----------

10. 101

rollup()

Stabilized in Toolkit v1.0.0

Hide content

`

1. rollup(

`

1. sketch UddSketch

`

1. ) RETURNS UddSketch

`

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

Required arguments

Returns

Extended examples

Aggregate and roll up percentile data to calculate daily percentiles using percentile_agg

Create an hourly continuous aggregate that contains a percentile aggregate:

2. CREATE MATERIALIZED VIEW foo_hourly

4. WITH (timescaledb.continuous)

6. AS SELECT

8. time_bucket('1 h'::interval, ts) as bucket,

10. percentile_agg(value) as pct_agg

12. FROM foo

14. 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:

2. SELECT

4. time_bucket('1 day'::interval, bucket) as bucket,

6. approx_percentile(0.95, rollup(pct_agg)) as p95,

8. approx_percentile(0.99, rollup(pct_agg)) as p99

10. FROM foo_hourly

12. GROUP BY 1;

Aggregate and roll up percentile data to calculate daily percentiles using uddsketch

Create an hourly continuous aggregate that contains a percentile aggregate:

2. CREATE MATERIALIZED VIEW foo_hourly

4. WITH (timescaledb.continuous)

6. AS SELECT

8. time_bucket('1 h'::interval, ts) as bucket,

10. uddsketch(value) as uddsketch

12. FROM foo

14. 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:

2. SELECT

4. time_bucket('1 day'::interval, bucket) as bucket,

6. approx_percentile(0.95, rollup(uddsketch)) as p95,

8. approx_percentile(0.99, rollup(uddsketch)) as p99

10. FROM foo_hourly

12. GROUP BY 1;