Skip to content
Telemetry
API reliability SQL recipe

Calculate p50, p95, and p99 API latency

Compare median and tail latency by endpoint with DataFusion-compatible percentile SQL.

Intermediateapi_requestsReviewed 2026-07-27Tested with Apache DataFusion 45.2.0

Reviewed by the Telemetry product team on . We checked the SQL syntax, required event fields, sample results, and limits on using the query. Who reviews this page

Question answered

Which endpoints have the worst tail latency?

Averages hide the slow requests users remember. Comparing p50, p95, and p99 reveals whether an endpoint is consistently slow or has a long tail caused by a smaller set of requests.

Event schema

Fields the query expects

FieldTypeWhy it exists
timestamp_utcTimestampRequest completion time in UTC.
route_templateUtf8Stable route template.
latency_msFloat64Request duration in milliseconds.
status_codeInt64HTTP response status code.
DataFusion SQL

Copy the query

sql
SELECT
  route_template,
  COUNT(*) AS requests,
  approx_percentile_cont(latency_ms, 0.50) AS p50_ms,
  approx_percentile_cont(latency_ms, 0.95) AS p95_ms,
  approx_percentile_cont(latency_ms, 0.99) AS p99_ms
FROM api_requests
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
  AND status_code < 500
GROUP BY route_template
HAVING COUNT(*) >= 50
ORDER BY p95_ms DESC
LIMIT 10;

This read-only query is planned and executed against an empty typed table with Apache DataFusion 45.2.0. We review the synthetic sample output separately. Check field types, thresholds, and counting rules against your own data. Read the testing methodology.

Query result

p95 latency by route

Export requests have the slowest tail and the largest gap between typical and worst-case performance.

route_templaterequestsp50_msp95_msp99_ms
/api/reports/export50800800800
/api/projects/:id/sync50320320320
/api/search50120120120

Synthetic example output. Run the query against your own event schema and thresholds before using it for operational decisions.

p95 latency by route: static chart of synthetic p95_ms values from the Calculate p50, p95, and p99 API latency example result
Download this SVG chart of the sample results for an article, runbook, or design review. Please credit Telemetry.

Reproduce the example

Download the sample data

The JSON bundle includes the event schema with field types, reproducible input rows, exact SQL, expected output, review notes, and engine version. The CSV contains the displayed result.

Run in browserSample JSONExpected output CSVStatic SVG chart

How the SQL works

  1. 1approx_percentile_cont uses DataFusion's t-digest implementation, which is efficient for large event tables.
  2. 2Filtering out 5xx responses makes this a successful-request latency view. Create a separate failed-request view when failure duration matters.
  3. 3Compare p50 with p95. A wide gap means some requests take much longer than the median request.

Edge cases to check

  • Do not compare endpoints with fundamentally different work without preserving route context.
  • Streaming routes and long-running exports may need separate service-level objectives.
  • Keep latency in a consistent unit and use a numeric field.

Recommended dashboard

  • Grouped bars: p50_ms and p95_ms by route_template
  • Line chart: p95_ms over time for the slowest routes
  • Table: individual requests above the route's p99

Alert guidance

Alert when a high-volume route's p95 exceeds its service objective for three complete time buckets.

Read alert setup

Set up the events this query needs

Related instrumentation and guides

API reliability use caseAPI error-rate recipeProfiling guide

Define the source data

Event schemas for this analysis

api_request_completed

An event sent when a request finishes. Use route templates instead of raw URLs, choose errors from a fixed list, and measure latency at the service.

Review schema

Continue the analysis

Calculate API error rate by route

Rank API routes by 5xx error rate. Exclude routes with fewer than 20 requests so one failure does not dominate the results.

Open recipe

Calculate API error-budget burn rate

Turn hourly request failures into an SLO burn-rate series that shows how quickly the allowed error budget is being consumed.

Open recipe

Compare API reliability by release

Compare traffic, 5xx rate, and p95 latency across application releases without attributing every post-deploy change to the deploy.

Open recipe

Run it on your events

Create a table, adapt the fields, and save the result

Start free, send structured events, and use the query result as a chart, shared dashboard widget, or alert input.

Get an API key