SQL percentiles for latency and distributions

An average compresses a distribution into one number. That is useful for totals and broad trends, but it can hide the slow experiences that matter during an incident. Percentiles answer a different question: p95 is the value at or below which roughly 95 percent of observations fall.

Query several points in the distribution

Telemetry uses Apache DataFusion SQL. Use approx_percentile_cont for practical percentile estimates:

SELECT
  route_template,
  COUNT(*) AS requests,
  approx_percentile_cont(duration_ms, 0.50) AS p50_ms,
  approx_percentile_cont(duration_ms, 0.95) AS p95_ms,
  approx_percentile_cont(duration_ms, 0.99) AS p99_ms,
  MAX(duration_ms) AS maximum_ms
FROM api_request_events
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
GROUP BY route_template
HAVING COUNT(*) >= 100
ORDER BY p95_ms DESC;

Keep request count in the result. A percentile from a tiny group is unstable, and p99 is not meaningful when a group contains only a handful of observations. Approximate results also should not be presented with artificial decimal precision.

Interpret the distribution

If p50, p95, and p99 all rise together, the whole workflow may have shifted. If p50 remains steady while p99 increases, a subset of requests is experiencing tail latency. The maximum is useful for investigation, but one outlier should not define normal performance.

Group only by dimensions that can change an action: route template, dependency, release, region, or a bounded operation name. Raw URLs and unrestricted query text create high-cardinality groups that are difficult to compare and may expose sensitive values.

Percentiles are not percentages. A p95 latency of 800 milliseconds does not mean 95 percent of requests are slow; it means about 95 percent completed at or below that value. Pair the number with a reviewed objective and an explicit unit.

Use the API latency recipe, dependency latency recipe, or queue wait recipe for tested examples. The distribution visualization guide explains when a histogram is more revealing than three percentile points.