Choosing Percentile Bands for Latency SLOs

Choosing percentile bands for latency SLOs means picking which quantiles to gate on (p50, p95, p99), the per-tier multiplier each is allowed to move above baseline, and the minimum sample count and confidence-interval width below which the band is not yet trustworthy. This runbook explains why a single hard latency ceiling collapses under variable concurrency, how to set defensible bands per workload tier, and how to compute nonparametric confidence intervals with numpy so a band never fires on statistical noise. It is the measurement layer beneath defining regression thresholds for query plans and complements the adaptive baseline math in setting dynamic thresholds for query regression alerts.

Symptom Identification and Production Thresholds #

A hard latency ceiling like $p99 \le 25 ms$ looks precise but silently misfires: it false-alarms whenever concurrency spikes and goes blind whenever the baseline itself was measured on a quiet window. Encode the following as the exact bands and gates; each is keyed on the plan fingerprint and a workload tier, never on a global constant.

1. Under-sampled quantile. A p99 band is evaluated on < 2,000 samples, a p95 band on < 400 samples, or a p50 band on < 100 samples. Below these counts the quantile’s sampling error swamps any real regression, so the window must hold rather than fire.
2. Confidence-interval breach. The 95% bootstrap confidence-interval half-width for the estimated quantile exceeds 10% of the point estimate. A band whose interval is wider than a tenth of its own value cannot distinguish a real shift from noise and must not gate a deploy.
3. Per-tier band multiplier breach. A quantile exceeds its tier’s multiplier over baseline: Tier-0 interactive $p95 > 2.0\times$ or $p99 > 2.5\times$; Tier-1 dashboard $p95 > 2.5\times$ or $p99 > 4.0\times$; Tier-2 analytical $p95 > 3.0\times$ or $p99 > 5.0\times$. Multipliers, not fixed millisecond ceilings, absorb the tier’s natural spread.
4. Multimodal distribution. The bimodality coefficient of the latency window exceeds 0.55, meaning a single percentile summarizes two populations (cache-hit vs cache-miss, or two bind-parameter regimes). A single band over a bimodal window is meaningless and must be split by mode.
5. Concurrency skew. Per-sample latency correlates with the concurrent-session count at Spearman ρ > 0.4. When latency is driven by load rather than plan shape, the band belongs to capacity alerting, not the plan-regression queue.

Fire a BLOCK only when breach 3 holds and breaches 1, 2, 4, and 5 are all clear — that is, the band is well-sampled, tightly estimated, unimodal, and not merely a concurrency artifact.

Root Cause Analysis #

When a percentile band flaps or fails to catch a known regression, the cause is almost always statistical rather than a genuine plan change. Three domains dominate.

Low sample counts inflating the tail. A p99 built from a few hundred executions is dominated by a handful of order statistics; one slow outlier moves it by tens of percent. Confirm the count actually backing each quantile before trusting it:

-- PostgreSQL: executions available per fingerprint in the scoring window
SELECT queryid, calls, mean_exec_time, stddev_exec_time,
       round(stddev_exec_time / NULLIF(mean_exec_time, 0), 3) AS cv
FROM pg_stat_statements
WHERE calls < 2000
ORDER BY calls ASC;

A high coefficient of variation (cv > 1.0) alongside a low calls count means the tail band is not yet estimable — widen the window before you compute p99.

Multimodal latency. Two distinct execution regimes (a warm buffer pool versus a cold read, or a selective versus a broad bind parameter) create two humps, and every percentile lands in the valley between them where no real query sits. Inspect the shape directly by bucketing:

-- PostgreSQL 14+: histogram of per-call latency for one fingerprint
SELECT width_bucket(total_exec_time / calls, 0, 500, 20) AS bucket,
       count(*) AS n
FROM pg_stat_statements
WHERE queryid = $1
GROUP BY bucket
ORDER BY bucket;

Two separated peaks confirm the window must be segmented by regime before a band applies — the same segmentation principle used when mapping EXPLAIN costs to real-world latency metrics.

Concurrency skew. Latency that rises with load, not with plan changes, corrupts a baseline captured off-peak. Correlate latency against active sessions over the sampling window:

psql "$DSN" -qAtF',' -c \
  "SELECT date_trunc('minute', ts) AS m, avg(active_sessions) AS sess, \
          percentile_disc(0.95) WITHIN GROUP (ORDER BY exec_ms) AS p95 \
   FROM latency_samples GROUP BY 1 ORDER BY 1" \
  | python3 -c "import sys,csv,statistics as s; r=list(csv.reader(sys.stdin)); \
print('rows', len(r))"

If p95 tracks session count minute-for-minute, the band is measuring the platform, not the plan.

Step-by-Step Remediation #

1. Compute quantiles with confidence intervals and a sufficiency guard #

Replace the point estimate with a nonparametric estimator that refuses to emit a band until the window is large enough and the bootstrap interval is tight enough. The estimator runs the guard first, computes p50/p95/p99 with numpy, derives each interval by resampling, and emits an OpenTelemetry span plus a structured log so every band is auditable.

from dataclasses import dataclass, field

import numpy as np
import structlog
from opentelemetry import trace

log = structlog.get_logger("slo.percentile_bands")
tracer = trace.get_tracer("slo.percentile_bands")

# Minimum sample count required before a given quantile is trustworthy.
MIN_SAMPLES = {50: 100, 95: 400, 99: 2000}
CI_HALFWIDTH_LIMIT = 0.10        # 95% CI half-width must be <= 10% of the estimate
BIMODALITY_LIMIT = 0.55          # above this the window is multimodal, split it


@dataclass(frozen=True)
class BandEstimate:
    quantile: int
    value_ms: float
    ci_low_ms: float
    ci_high_ms: float
    n: int
    trustworthy: bool


def _bimodality_coefficient(x: np.ndarray) -> float:
    n = x.size
    if n < 4:
        return 0.0
    skew = float(((x - x.mean()) ** 3).mean() / (x.std() ** 3 + 1e-12))
    kurt = float(((x - x.mean()) ** 4).mean() / (x.std() ** 4 + 1e-12)) - 3.0
    return (skew ** 2 + 1.0) / (kurt + 3.0 * (n - 1) ** 2 / ((n - 2) * (n - 3)))


def estimate_band(
    samples: np.ndarray, quantile: int, n_boot: int = 2000, seed: int = 7,
) -> BandEstimate:
    with tracer.start_as_current_span("estimate_band") as span:
        n = samples.size
        point = float(np.percentile(samples, quantile))
        rng = np.random.default_rng(seed)
        boot = np.percentile(
            rng.choice(samples, size=(n_boot, n), replace=True), quantile, axis=1
        )
        lo, hi = np.percentile(boot, [2.5, 97.5])
        half_width = (hi - lo) / 2.0
        enough = n >= MIN_SAMPLES[quantile]
        tight = point > 0 and (half_width / point) <= CI_HALFWIDTH_LIMIT
        unimodal = _bimodality_coefficient(samples) <= BIMODALITY_LIMIT
        trustworthy = bool(enough and tight and unimodal)
        span.set_attribute("slo.quantile", quantile)
        span.set_attribute("slo.value_ms", round(point, 3))
        span.set_attribute("slo.trustworthy", trustworthy)
        log.info("band_estimate", quantile=quantile, value_ms=round(point, 3),
                 ci=[round(float(lo), 3), round(float(hi), 3)], n=n,
                 trustworthy=trustworthy)
        return BandEstimate(quantile, point, float(lo), float(hi), n, trustworthy)

Expected output for a healthy Tier-0 p99 window:

{"quantile": 99, "value_ms": 21.6, "ci": [20.4, 22.9], "n": 5400, "trustworthy": true}

2. Apply the per-tier multipliers #

Score each trustworthy estimate against its tier band. A band exceeded while its confidence interval still overlaps the limit is a WARN, not a BLOCK — the overlap means the breach is not yet statistically resolved.

TIER_BANDS = {
    "tier0_interactive": {50: 1.4, 95: 2.0, 99: 2.5},
    "tier1_dashboard":   {50: 1.5, 95: 2.5, 99: 4.0},
    "tier2_analytical":  {50: 2.0, 95: 3.0, 99: 5.0},
}


def score_band(est: BandEstimate, baseline_ms: float, tier: str) -> str:
    if not est.trustworthy:
        return "HOLD"
    limit = baseline_ms * TIER_BANDS[tier][est.quantile]
    if est.value_ms <= limit:
        return "PASS"
    return "BLOCK" if est.ci_low_ms > limit else "WARN"

3. Wire the bands into the deploy gate #

Export each trustworthy band as a labelled gauge and let the CI gate block only on a resolved BLOCK, so an under-sampled or noisy window never fails a merge on its own. Source the shared regression contract from defining regression thresholds for query plans so runtime alerting and the gate evaluate identical bands.

groups:
  - name: latency_slo_bands
    rules:
      - alert: LatencyBandBlock
        expr: |
          query_latency_p99_ms{tier="tier0_interactive"}
            > on(query_id) query_latency_p99_baseline_ms * 2.5
          and query_latency_band_trustworthy == 1
        for: 10m
        labels: { severity: critical, team: db-sre }
        annotations:
          summary: "p99 band BLOCK on {{ $labels.query_id }}"
          description: "Tier-0 p99 exceeded 2.5x baseline with a tight CI."

A resolved BLOCK fails the merge; a WARN routes to the low-priority telemetry bucket, exactly the split that keeps alert volume sane in tuning thresholds for false positive reduction.

Verification Checklist #

Work through each item before a band is allowed to gate a deploy:

• [ ] Each quantile is backed by at least its MIN_SAMPLES count (100 for p50, 400 for p95, 2,000 for p99).
• [ ] The 95% bootstrap CI half-width is $\le 10\%$ of the point estimate for every gating band.
• [ ] The window’s bimodality coefficient is $\le 0.55$, or the window has been split by regime before scoring.
• [ ] Latency-versus-concurrency Spearman ρ is $\le 0.4$, confirming the band reflects plan shape, not load.
• [ ] Per-tier multipliers are applied ($2.0\times$/$2.5\times$ Tier-0, $2.5\times$/$4.0\times$ Tier-1, $3.0\times$/$5.0\times$ Tier-2), not a global ceiling.
• [ ] A BLOCK requires ci_low_ms to exceed the band limit; an overlapping interval yields a WARN.
• [ ] The exported query_latency_band_trustworthy gauge is 1 for every gating fingerprint.
• [ ] The baseline percentile was measured over a window spanning peak and off-peak concurrency, not a single quiet interval.

Compatibility and Engine-Specific Notes #

The band math is engine-agnostic; the raw material it needs — true per-execution latencies rather than pre-aggregated means — is what varies.

Because pg_stat_statements reports only a mean and standard deviation, never invert it into a percentile — sample real per-execution latencies through a low-overhead path and feed those to the estimator. MySQL’s per-digest histograms give usable quantile buckets directly, but their fixed edges cap resolution, so interpolate within the bucket before applying a multiplier. On distributed engines, aggregate every participating node’s samples into one window before scoring, or a routine rebalance will surface as a spurious tail regression rather than a plan change.

Related #

• Setting Dynamic Thresholds for Query Regression Alerts — the adaptive EWMA/MAD baseline these bands are scored against.
• Tuning Thresholds for False-Positive Reduction — how WARN-versus-BLOCK routing keeps alert volume manageable.
• Mapping EXPLAIN Costs to Real-World Latency Metrics — anchors optimizer cost to the measured latencies these bands consume.

← Back to Defining Regression Thresholds for Query Plans