Percentiles: describing a distribution instead of its mean

One number cannot describe a shape

A pile of 100,000 page-load times has a mean of 410 ms. That is true and nearly useless. It could mean every page loads in about 400 ms, or that 95% load in 200 ms and 5% take eight seconds. Those are different products, and the people who hit the eight seconds are the ones who leave.

A percentile answers a question the mean cannot: “what is the value that a given share of rows fall below?” The median is the 50th percentile, the point half the rows sit under. The 95th percentile, written p95, is the value that 95% of rows are under and 5% are over.

Getting one out of SQL

Postgres uses a syntax that reads oddly the first time:

SELECT
  percentile_cont(0.5)  WITHIN GROUP (ORDER BY load_ms) AS p50,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY load_ms) AS p95,
  percentile_cont(0.99) WITHIN GROUP (ORDER BY load_ms) AS p99,
  avg(load_ms)                                          AS mean
FROM page_loads
WHERE loaded_at >= '2026-03-01' AND loaded_at < '2026-04-01';

percentile_cont interpolates between the two nearest rows, so it can return a value no row actually has. percentile_disc returns a real row’s value. For latency and money the difference is cosmetic; for something like “the median number of children per household” percentile_disc avoids reporting 1.5 children.

DuckDB has quantile_cont(load_ms, 0.95) and a plain median(load_ms). MySQL and SQLite have no percentile function; you sort, number the rows with a window function, and pick the row at position ceil(0.95 * n), which the next module covers. Spreadsheets have PERCENTILE and MEDIAN, and LibreOffice Calc’s are the same as Excel’s.

Why p99 is the number that matters most

If 1% of requests are slow, that sounds negligible. At a million requests a day it is ten thousand slow experiences, and because a page makes many requests, a user’s chance of hitting at least one p99 event is far higher than 1%. Twenty requests per page at a 1% tail gives roughly an 18% chance the page contains a slow one. This is why engineering teams track p99 rather than the mean: the mean is dominated by the fast majority and hides the tail entirely.

Seeing the whole shape

Percentiles are samples of the distribution. A histogram is the distribution. width_bucket sorts values into equal-width bins:

SELECT width_bucket(amount, 0, 5000, 10) AS bucket,
       min(amount), max(amount), count(*)
FROM orders
GROUP BY bucket
ORDER BY bucket;

Ten bins of ₹500 between 0 and 5,000, with bucket 11 catching everything above. Read the counts down the column and you can see whether there is one hump, two humps, or a wall at a price point. Two humps usually means two populations in one table, such as retail and wholesale customers, and that is a finding on its own.

The limitation that is a mechanism, not a policy

You can add two sums. You cannot combine two medians. If server A has a p95 of 300 ms and server B has a p95 of 900 ms, the p95 across both is not 600 ms and cannot be computed from the two figures at all. It depends on how many requests each server handled and where every request fell. An exact percentile needs the raw values, sorted, which is why the query above has to read and sort all 100,000 rows, and why a summary table holding p95 per hour cannot give you p95 per day.

Large engines make a trade here. BigQuery’s APPROX_QUANTILES, Spark’s approx_percentile and DuckDB’s approx_quantile use sketches that hold a compressed picture of the distribution in a few kilobytes, can be merged across servers and hours, and are wrong by a small stated amount, typically under 1% of the rank. For a dashboard that is the right trade. For a service-level agreement with money attached, run the exact version and accept the sort.

Reporting them

Put p50, p95 and p99 in the same row as the mean and the count. A mean far above the median says “long tail”. A p99 ten times p50 says “a small group is having a very different experience”. The count says how many rows the tail is, which is the number somebody will ask for next.