/diagnosing-endpoint-performance

name: diagnosing-endpoint-performance
description: >
  Diagnose why a PostHog endpoint is slow or expensive and propose a concrete fix — bump the cache
  TTL, enable materialisation, restructure variables, or rewrite the query. Use when the user says
  "this endpoint is slow", "my endpoint times out", "we're hitting the cost cap on this one", or
  asks "should I materialise this?". Focuses on a single named endpoint, not a project-wide audit.

Diagnosing endpoint performance

This skill walks through a specific endpoint that is slow, expensive, or unreliable, and produces a concrete recommendation. It is the deep-dive counterpart to `auditing-endpoints` (which finds candidates).

When to use this skill

• "This endpoint is slow / timing out"
• "Why is my endpoint hitting the cost cap?"
• "Should I materialise X?"
• An endpoint surfaced from `auditing-endpoints` as a failing materialisation or expensive caller
• The user has a specific endpoint in mind and wants advice

If the question is project-wide ("what should I clean up?"), use `auditing-endpoints` first.

Available tools

| Tool | Purpose | | ------------------------------------- | ---------------------------------------------------------------------------------------------- | | `endpoint-get` | Full endpoint config: query, current version, `data_freshness_seconds`, materialisation status | | `endpoint-versions` | History of every version (query + materialisation state); which version is current | | `endpoint-materialization-status` | Whether materialisation is eligible, current state, last run, last error | | `endpoints-materialization-preview` | What the materialised query would look like, plus the rejection reason if ineligible | | `endpoint-materialization-suggestion` | Server-side AI rewrite of an ineligible SQL query, validated against the live checks | | `endpoint-materialization-conditions` | Source code of the live eligibility checks + the rewrite contract, for DIY rewriting | | `endpoints-last-execution-times` | When was it last called (endpoint-level sanity-check that it is in active use) | | `execute-sql` | Query `query_log` for endpoint-level call frequency and per-call duration/bytes |

The decision tree

When deciding what to recommend, walk these in order — the first one that applies is the cheapest fix.

Step 1 — Is it cached at all?

Fetch the endpoint and look at `data_freshness_seconds` (it sets both the cache TTL and, when materialised, the refresh cadence). If the user's traffic calls the same parameters repeatedly within that window, every call after the first is a cache hit and effectively free.

• TTL is at the default (24h / 86400s) and the data really doesn't need fresher than that →

done, no change needed.

• TTL is at the 900s floor (15 min) and the user is hitting the endpoint many times per minute →

bump the TTL. This is almost always the cheapest first move. (`data_freshness_seconds` is an enum: 900, 1800, 3600, 21600, 43200, 86400, 604800 — there is no sub-15-minute value.)

• TTL is at the floor _because the data must be fresh_ (e.g. real-time dashboard) → cache won't

help, skip to step 2.

The shape of the variables matters here: if every call passes different `user_id` or `date_from` values, the cache has many distinct keys and a higher TTL helps less. If almost every call uses the same handful of parameter combinations, the cache helps a lot.

Step 2 — Should it be materialised?

Materialisation pre-computes the query into a saved view that's refreshed on a schedule. Reads become near-instant — at the cost of staleness equal to the refresh interval, plus storage and compute for the materialisation itself.

Call `endpoints-materialization-preview`. The response tells you:

• **Eligible + clean transform** → strong candidate. Recommend enabling, especially for

endpoints with predictable filter shapes (variables, breakdowns).

• **Not eligible**, with a rejection reason → cannot materialise. The reason often hints at the

next step (see step 3 — rewrite).

• **Eligible but the transform is gnarly** (lots of range pairs, complex aggregation

re-derivation) → materialisation will work but may not save much. Worth flagging before flipping the switch.

When materialisation is enabled, callers **must pass all materialised variables** — calls without them are rejected (security: prevents returning unfiltered data). Pair the recommendation with a note about which variables become required.

Step 3 — Does the query need rewriting?

For a SQL endpoint that isn't eligible, try the fast path first: call `endpoint-materialization-suggestion`. PostHog rewrites the query into a semantically equivalent form and validates it against the live eligibility checks before returning it — `ok` means the rewrite passes the checks plus variable- and output-column parity, but semantic equivalence is the model's claim, not proven. Before applying, run the original and the rewrite with the same representative variable values (via `execute-sql` or the endpoint playground) and compare the results; only then apply it with `endpoint-update` (creates a new version), then confirm with `endpoint-materialization-status`. `cannot_fix` means no equivalent rewrite exists (e.g. an `OR {variables.x} = 'all'` optional-variable idiom) — say so rather than forcing a change in behaviour. Requires the org's AI data processing approval; without it, or to reason about the rewrite yourself, call `endpoint-materialization-conditions` — it returns the actual source code of the checks this instance enforces plus the rewrite contract. Treat that as authoritative; the bullet list below is a summary and may lag it.

Otherwise,