Percentile

Overview

Percentiles describe the distribution of a dataset by identifying the value at a given rank. The 50th percentile (p50) is the median; the 95th percentile (p95) and 99th percentile (p99) are especially common in performance and reliability monitoring because they capture the experience of the slowest requests, which averages hide. A system with a fast average latency can still have a terrible p99 if a small fraction of requests are very slow — percentiles surface that in a way a mean cannot.

Computing percentiles in DuckDB

DuckDB supports exact percentile calculation through quantile_cont (continuous, interpolated between values) and quantile_disc (discrete, returns an actual value from the dataset), along with a dedicated median function:

Copy code
SELECT
    quantile_cont(latency_ms, 0.5)  AS p50,
    quantile_cont(latency_ms, 0.95) AS p95,
    quantile_cont(latency_ms, 0.99) AS p99
FROM requests;

SELECT median(latency_ms) FROM requests;  -- shorthand for the 0.5 quantile

quantile_cont and quantile_disc also accept a list of fractions to compute multiple percentiles from a single pass over the data:

Copy code
SELECT quantile_cont(latency_ms, [0.5, 0.95, 0.99]) AS percentiles
FROM requests;

Exact versus approximate

Exact percentile calculation requires sorting (or an equivalent ordering operation) over the full column, which can be expensive on very large datasets. When an estimate is acceptable — dashboards, monitoring, exploratory analysis — DuckDB's approx_quantile function (based on a t-digest sketch) computes percentiles much faster and with bounded memory, at the cost of a small, generally negligible amount of accuracy.