Capstok — learn by doing

Why this matters

The first instinct when a database feels slow is to look at average query time, and it is almost always the wrong instinct. Latency distributions in databases are not symmetric — they are a dense cluster of cache hits with a long tail of cache misses, lock waits and plan regressions dragging to the right. The mean sits inside the cluster and barely moves when the tail doubles, which means you can watch your average all day while a growing fraction of users time out. Worse, the average is the statistic most easily improved by doing nothing: add more cheap fast queries and it falls. Percentiles do not have this problem, because p95 is defined by position rather than magnitude — it only moves when the shape of the distribution moves. There is a second trap on top of it: you cannot average percentiles across servers or time buckets. A p95 of 200ms on each of ten servers does not give you a fleet p95 of 200ms, and every dashboard that shows you one is lying by construction.

Demo

The same thousand query timings, summarised four ways. Every summary is arithmetically correct and three of them would let you close the incident.

Try it yourself

Run the synthetic distribution query and change 0.95 to 0.80 — watch the mean move a little and p95 move enormously. That asymmetry is the whole argument.
Run the pg_stat_statements coefficient-of-variation query on your seeded database. Any query with cv above 1.0 has a tail your mean is hiding.
Time the same query 100 times in a loop and record every duration, then compute the p95 yourself. Compare it to mean_exec_time from pg_stat_statements for the same statement.
Take two sets of 100 timings, compute p95 on each, average those two p95s, then compute p95 on the merged 200. Note how far apart the two answers are.
Find your slowest single execution with max_exec_time and check whether that query appears anywhere in your top-10-by-mean list.

References

-- Percentiles straight out of the engine. Run this against your own
-- timing table, or against the synthetic distribution below.

-- A realistic shape: 950 cache hits around 2ms, 50 misses around 900ms.
WITH timings AS (
  SELECT CASE WHEN random() < 0.95
              THEN 1.0 + random() * 3       -- fast path: buffer hit
              ELSE 400  + random() * 1200   -- slow path: disk + lock wait
         END AS ms
  FROM generate_series(1, 1000)
)
SELECT
  count(*)                                                  AS n,
  round(avg(ms)::numeric, 1)                                AS mean_ms,
  round(percentile_cont(0.50) WITHIN GROUP (ORDER BY ms)::numeric, 1) AS p50,
  round(percentile_cont(0.95) WITHIN GROUP (ORDER BY ms)::numeric, 1) AS p95,
  round(percentile_cont(0.99) WITHIN GROUP (ORDER BY ms)::numeric, 1) AS p99,
  round(max(ms)::numeric, 1)                                AS worst
FROM timings;

--  n    | mean_ms |  p50  |  p95  |   p99   |  worst
-- ------+---------+-------+-------+---------+---------
--  1000 |    52.4 |   2.5 | 843.1 | 1502.7  | 1598.2
--
-- The mean says 52ms: fine. p50 says 2.5ms: excellent!
-- p95 says 843ms: one request in twenty is nearly a second.
-- Which number would you put on a status page? Which one do users feel?

-- pg_stat_statements gives you mean and stddev but NOT percentiles.
-- A large stddev relative to the mean is your tell that a tail exists:
SELECT
  left(query, 60)                        AS query,
  calls,
  round(mean_exec_time::numeric, 2)      AS mean_ms,
  round(stddev_exec_time::numeric, 2)    AS stddev_ms,
  round(max_exec_time::numeric, 2)       AS max_ms,
  -- Coefficient of variation. Over ~1.0 means the mean is meaningless.
  round((stddev_exec_time / nullif(mean_exec_time, 0))::numeric, 2) AS cv
FROM pg_stat_statements
WHERE calls > 100
ORDER BY cv DESC
LIMIT 10;

Averages Lie: p95 Is the Number That Matters

Why this matters

Demo

Try it yourself

References