analyzing-experiment-query-performance

Analyzing experiment query performance

The /experiments/staff scene (staff-only UI, "Experiments staff tools") is backed by three GET endpoints that are also callable directly with a personal API key. They return the exact data the UI renders, sourced from ClickHouse query_log_archive (experiment queries only, lc_product = 'experiments'), system.parts, and the Postgres PreaggregationJob table.

Backend: posthog/api/debug_ch_queries.py (DebugCHQueries viewset). Frontend types (authoritative response shapes): frontend/src/scenes/instance/QueryPerformance/queryPerformanceLogic.ts.

Environment

Region	Base URL
US	https://us.posthog.com
EU	https://eu.posthog.com

The regions are separate instances with separate data and separate keys. When the user doesn't specify a region, check both — a regression is often region-specific.

Authentication

Requests need a personal API key (PAT) from a staff account, carrying the query_performance:read scope. Two deliberate properties of this scope:

• A full-access (*) PAT is rejected — the viewset is an INTERNAL scope object, so the key must carry query_performance:read explicitly. Prefer a dedicated key with only this scope; it can read query-performance data and nothing else.
• Every request is additionally gated on is_staff, so a leaked key from a non-staff account is useless.

The scope is deliberately absent from the key-creation UI (frontend/src/lib/scopes.tsx omits it as PAT-grantable only), so the key must be created via the API. Setup (once per region): the user, logged in to <base-url> as staff, runs this in the browser devtools console:

await fetch('/api/personal_api_keys/', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json',
    'X-CSRFToken': document.cookie.match(/posthog_csrftoken=([^;]+)/)?.[1] ?? '',
  },
  body: JSON.stringify({
    label: 'query-perf-agent',
    scopes: ['query_performance:read'],
    // required fields; empty = unrestricted (the endpoints are instance-level anyway)
    scoped_teams: [],
    scoped_organizations: [],
  }),
}).then(async (r) => (await r.json()).value)