Why your p99 is not really a p99

Most latency reporting in Postgres pipelines fails in one of two places. Either the pipeline stores a mean, which hides the tail entirely, or it stores fixed histogram buckets and then interpolates — so “p99” comes back as “somewhere between 400 and 500 ms.” The third failure is worse: rolling percentiles up across app hosts by averaging them, which produces numbers that never existed.

hdr_histogram (project name pg_hdr_histogram, 1.0.0, released 2026-10-26) attacks the storage side of that problem. It puts a full HDR histogram into Postgres as a first-class type: ~0.1% relative precision over six orders of magnitude, lossless merges across time windows and hosts, and plain SQL access. This tutorial installs it, computes percentiles over raw rows, stores per-minute frames, and merges them for dashboards and alerts.

Step 1 — Install and enable the extension

It supports PostgreSQL 14–18 and needs the server dev package, zlib and a C compiler. On Debian/Ubuntu, substitute your actual major version from pg_config --version:

sudo apt install postgresql-server-dev-18 build-essential zlib1g-dev
git clone https://github.com/vovcke/pg_hdr_histogram.git
cd pg_hdr_histogram
make && sudo make install

On Red Hat-family systems the equivalents are postgresql-server-devel, gcc, make and zlib-devel. Then run CREATE EXTENSION hdr_histogram; in each database that needs it, and sanity-check that the library loaded with SELECT hdr_percentile(hdr_add(1.0), 50); — it should return 1.

The extension is marked trusted = true, so on managed providers a non-superuser with CREATE privilege can install it — no shared preload required for core use.

Step 2 — Compute p50, p99 and p99.9 over real rows

hdr_add(double precision) is an aggregate that records each value into a histogram, and hdr_percentile(h, p) reads a percentile out of it. Values are converted to int64, so pick one unit and stay in it: milliseconds for request latency, nanoseconds for tight API budgets. Start with a throwaway dataset so you can see the tail move:

CREATE TEMP TABLE request_log AS
SELECT g AS id,
       (random() ^ 3) * 900 + 5 AS latency_ms   -- long tail by construction
FROM generate_series(1, 200000) g;

SELECT hdr_percentile(hdr_add(latency_ms), 50)     AS p50,
       hdr_percentile(hdr_add(latency_ms), 99)     AS p99,
       hdr_percentile(hdr_add(latency_ms), 99.9)   AS p999,
       hdr_max(hdr_add(latency_ms))                AS max_ms,
       hdr_total_count(hdr_add(latency_ms))        AS n
FROM request_log;

Two things to notice. First, each hdr_add(...) call in the select list builds its own histogram, so you scan the data more than once — fine for an ad-hoc check, wasteful for a repeated report. Second, values outside the configured range, or non-finite values such as NaN, raise an error instead of being silently bucketed into the extremes. That strictness is a feature: silent clamping is how dashboards end up lying.

If you are starting from pg_stat_statements instead of raw request rows, remember that it gives you one row per normalized query with a mean execution time — good for per-query snapshots, not a weighted sample of every call. We covered those gaps in why slow queries vanish from pg_stat_statements.

Step 3 — Store per-minute histograms instead of raw samples

The hdr_histogram type holds a serialized, compressed histogram, so it is cheap to keep one per service per minute and keep it forever. Blob size follows the spread of the values, not how many there are: a tight cluster of 10 million values compresses to roughly 50 bytes, while pathological long-tailed latency lands around 12 KB.

CREATE TABLE latency_hist (
  service     text,
  recorded_at timestamptz,
  hist        hdr_histogram
);
CREATE INDEX ON latency_hist (service, recorded_at);

-- tune per session before building: 3 sig figs = ~0.1%, ceiling 1e9
SET hdr_histogram.sig_figs = 3;
SET hdr_histogram.highest  = 1e9;

INSERT INTO latency_hist
SELECT 'checkout-api',
       date_trunc('minute', observed_at),
       hdr_add(latency_ns)
FROM request_log_ns
GROUP BY 1, 2;

Building from raw values runs at roughly 9 million values per second on the project’s dev benchmarks, so a nightly backfill of a year of frames is a minutes-scale job, not an overnight one. Raise sig_figs to 4 for ~0.01% precision, or raise highest if you track anything above 1e9 units (in nanoseconds, that ceiling is about one second).

Step 4 — Merge across minutes and hosts, then watch the GUC trap

Any reporting window is just a merge over a time range, and hdr_merge combines histograms without approximating the distribution. Merges with differing parameters are governed by hdr_histogram.merge_mode: lossless (default) errors rather than losing data, strict requires identical parameters, left keeps the accumulator’s parameters and silently drops out-of-range values, and union rebuilds into the widest range and finest precision.

SET hdr_histogram.merge_mode = 'union';

SELECT hdr_percentile(hdr_merge(hist), 99)     AS p99,
       hdr_percentile(hdr_merge(hist), 99.9)   AS p999,
       hdr_stddev(hdr_merge(hist))             AS stddev,
       hdr_total_count(hdr_merge(hist))        AS samples
FROM latency_hist
WHERE service = 'checkout-api'
  AND recorded_at >= now() - interval '1 hour';

Here is the trap that costs people an afternoon: the GUC is registered when the extension’s library loads, which happens on the first hdr_* call or an explicit LOAD 'hdr_histogram'. ALTER DATABASE ... SET and ALTER ROLE ... SET are applied at session start — before the library loads — so they are silently ignored. A mid-session SET or SET LOCAL works fine. If you need database or role defaults, add session_preload_libraries = 'hdr_histogram' to postgresql.conf first. On managed providers where you cannot set that, pass it in the connection string instead: options='-c hdr_histogram.merge_mode=union'.

Step 5 — Prove the numbers before you alert on them

Two checks before anyone pages on this. First, compare against the exact computation on a slice you can afford to sort: run percentile_cont(0.99) WITHIN GROUP (ORDER BY latency_ms) over the same window and confirm the merged HDR value lands within a fraction of a percent. Second, pull hdr_total_count, hdr_max and hdr_mean from the same merged window and sanity-check them against your row counts and known slowest request — a mean that disagrees with reality by orders of magnitude is nearly always a unit bug, not a histogram bug.

Keep in mind what HDR actually promises: bounded relative error, not exact order statistics. At three significant figures you are within about 0.1% of the true value, which is far tighter than any fixed-bucket dashboard and stable enough to threshold on. Also check units explicitly — because samples are integers, mixing milliseconds and nanoseconds across services is the most common way a merged percentile turns into nonsense.

Where this earns its keep

For Houston teams running dispatch, telematics or EDI ingest on Postgres, the failure mode is rarely a bad average. It is a p99.9 spike at shift change, a load-tender endpoint that stalls for 40 seconds twice a day, or one app host quietly slower than the other three — all invisible when percentiles are averaged across hosts instead of merged. Per-minute histograms in a table give you the tail across any window, per host or per service, with no extra monitoring tier to operate.

If you would rather not assemble the retention, merge and alerting pieces yourself, we do Postgres latency and observability work for Houston teams — from sizing per-minute histogram storage to setting SLO thresholds you can actually page on. Related reading: PgBouncer 1.26 and killing p99 timeouts.