tdigest

tdigest : Provides tdigest aggregate function.

Overview

Packages

Source

Install

Make sure PGDG repo available:

pig repo add pgdg -u    # add pgdg repo and update cache

Install this extension with pig:

pig install tdigest;		# install via package name, for the active PG version

pig install tdigest -v 18;   # install for PG 18
pig install tdigest -v 17;   # install for PG 17
pig install tdigest -v 16;   # install for PG 16
pig install tdigest -v 15;   # install for PG 15
pig install tdigest -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION tdigest;

Usage

Sources:

• tdigest v1.4.4 README
• v1.4.4 release
• Extension control file

tdigest implements an approximate, mergeable t-digest for online rank statistics such as quantiles, percentile ranks, and trimmed means. It supports parallel aggregation and storing pre-aggregated digests for later rollups.

CREATE EXTENSION tdigest;

Direct Aggregation Functions

Pre-aggregation Functions

Incremental Update Functions

Utility Functions

Parameters

• compression – controls accuracy (higher = more accurate, larger digest). Error is roughly 1/compression.

Examples

-- Instead of: SELECT percentile_cont(0.95) WITHIN GROUP (ORDER BY a) FROM t;
SELECT tdigest_percentile(a, 100, 0.95) FROM t;

-- Multiple percentiles
SELECT tdigest_percentile(a, 100, ARRAY[0.5, 0.95, 0.99]) FROM t;

-- Pre-aggregate for fast repeated queries
CREATE TABLE p AS SELECT a, b, tdigest(c, 100) AS d FROM t GROUP BY a, b;

-- Query pre-aggregated data (~1.5ms vs ~7s for exact)
SELECT a, tdigest_percentile(d, 0.95) FROM p GROUP BY a ORDER BY a;

Caveats

• Results are estimates. Validate the chosen compression against exact percentile_cont results on representative data before setting accuracy targets.
• Higher compression usually improves tail accuracy but increases state size and CPU cost.
• Stored digests can be merged across groups and time windows. Version 1.4.4 fixes combining digests created with different parameters, so use that patch level when heterogeneous states may meet.
• Version 1.4.4 also strengthens text-input parsing and validation and adds PostgreSQL 19 build/test coverage; malformed serialized digests that older builds accepted may now be rejected.