Diagnosing Endpoint Performance

1---
2name: diagnosing-endpoint-performance
3description: >
4  Diagnose why a PostHog endpoint is slow or expensive and propose a concrete fix — bump the cache
5  TTL, enable materialisation, restructure variables, or rewrite the query. Use when the user says
6  "this endpoint is slow", "my endpoint times out", "we're hitting the cost cap on this one", or
7  asks "should I materialise this?". Focuses on a single named endpoint, not a project-wide audit.
8---
9 
10# Diagnosing endpoint performance
11 
12This skill walks through a specific endpoint that is slow, expensive, or unreliable, and produces
13a concrete recommendation. It is the deep-dive counterpart to `auditing-endpoints` (which finds
14candidates).
15 
16## When to use this skill
17 
18- "This endpoint is slow / timing out"
19- "Why is my endpoint hitting the cost cap?"
20- "Should I materialise X?"
21- An endpoint surfaced from `auditing-endpoints` as a failing materialisation or expensive caller
22- The user has a specific endpoint in mind and wants advice
23 
24If the question is project-wide ("what should I clean up?"), use `auditing-endpoints` first.
25 
26## Available tools
27 
28| Tool                                | Purpose                                                                                        |
29| ----------------------------------- | ---------------------------------------------------------------------------------------------- |
30| `endpoint-get`                      | Full endpoint config: query, current version, `data_freshness_seconds`, materialisation status |
31| `endpoint-versions`                 | History of every version (query + materialisation state); which version is current             |
32| `endpoint-materialization-status`   | Whether materialisation is eligible, current state, last run, last error                       |
33| `endpoints-materialization-preview` | What the materialised query would look like, plus the rejection reason if ineligible           |
34| `endpoints-last-execution-times`    | When was it last called (endpoint-level sanity-check that it is in active use)                 |
35| `execute-sql`                       | Query `query_log` for endpoint-level call frequency and per-call duration/bytes                |
36 
37## The decision tree
38 
39When deciding what to recommend, walk these in order — the first one that applies is the cheapest
40fix.
41 
42### Step 1 — Is it cached at all?
43 
44Fetch the endpoint and look at `data_freshness_seconds` (it sets both the cache TTL and, when
45materialised, the refresh cadence). If the user's traffic
46calls the same parameters repeatedly within that window, every call after the first is a cache
47hit and effectively free.
48 
49- TTL is at the default (24h / 86400s) and the data really doesn't need fresher than that →
50  done, no change needed.
51- TTL is at the 900s floor (15 min) and the user is hitting the endpoint many times per minute →
52  bump the TTL. This is almost always the cheapest first move. (`data_freshness_seconds` is an
53  enum: 900, 1800, 3600, 21600, 43200, 86400, 604800 — there is no sub-15-minute value.)
54- TTL is at the floor _because the data must be fresh_ (e.g. real-time dashboard) → cache won't
55  help, skip to step 2.
56 
57The shape of the variables matters here: if every call passes different `user_id` or `date_from`
58values, the cache has many distinct keys and a higher TTL helps less. If almost every call uses
59the same handful of parameter combinations, the cache helps a lot.
60 
61### Step 2 — Should it be materialised?
62 
63Materialisation pre-computes the query into a saved view that's refreshed on a schedule. Reads
64become near-instant — at the cost of staleness equal to the refresh interval, plus storage and
65compute for the materialisation itself.
66 
67Call `endpoints-materialization-preview`. The response tells you:
68 
69- **Eligible + clean transform** → strong candidate. Recommend enabling, especially for
70  endpoints with predictable filter shapes (variables, breakdowns).
71- **Not eligible**, with a rejection reason → cannot materialise. The reason often hints at the
72  next step (see step 3 — rewrite).
73- **Eligible but the transform is gnarly** (lots of range pairs, complex aggregation
74  re-derivation) → materialisation will work but may not save much. Worth flagging before
75  flipping the switch.
76 
77When materialisation is enabled, callers **must pass all materialised variables** — calls without
78them are rejected (security: prevents returning unfiltered data). Pair the recommendation with
79a note about which variables become required.
80 
81### Step 3 — Does the query need rewriting?
82 
83If the endpoint isn't eligible for materialisation, the rejection reason from
84`endpoints-materialization-preview` is usually the lead:
85 
86- **Cohort breakdown / compare mode rejection** → regular property breakdowns materialise fine;
87  only cohort breakdowns and compare mode are blocked. Swap a cohort breakdown for a property
88  breakdown, or drop compare mode (expose the comparison window as a variable instead).
89- **JOINs combined with variables** → a top-level `JOIN` plus a variable filter is rejected for
90  materialisation, because applying the variable changes the joined row cardinality and silently
91  produces wrong results (e.g. `LEFT JOIN` non-matches lose the variable column). Restructure so the
92  variable filters a single table — push the filter into a subquery/CTE that's then joined, rather
93  than filtering across the join. This is the most common "looks fine but won't materialise" trap.
94- **"Missing variables" / unbounded scan** → the query reads too much data without a filter.
95  Encourage adding a required time-window variable (e.g. `date_from`, `lookback_days`).
96- **HogQL with `*` / non-deterministic functions** → narrow the columns selected, replace
97  `now()` / `today()` with a variable when possible.
98 
99Check `endpoint-versions` to see whether the query was recently changed. Often the regression
100came from a specific commit and reverting that version is faster than rewriting.
101 
102### Step 4 — Is the slow version even the one being called?
103 
104Only the latest version runs by default; older versions run only when a caller pins `?version=N`.
105So the version to tune is almost always the current one — unless a pinned older version is the
106culprit. Call `endpoint-versions` and read each version's `last_executed_at` to see which versions
107have been hit recently; a materialised version with a null or long-stale `last_executed_at` is a
108candidate to unmaterialise or delete rather than tune (confirm first — that signal only counts
109API-key runs and can be sparse).
110 
111For endpoint-level call frequency and per-call cost, query `query_log` with `execute-sql` — it
112carries `query_duration_ms`, `read_rows`, and `read_bytes`, handy for confirming how heavy the
113endpoint's calls actually are:
114 
115```sql
116SELECT count() AS calls, max(query_start_time) AS last_called, avg(query_duration_ms) AS avg_ms
117FROM query_log
118WHERE name = '<endpoint_name>' AND endpoint LIKE '%/endpoints/%' AND is_personal_api_key_request
119```
120 
121## Workflow
122 
1231. Identify the endpoint by name. If the user gave a URL, parse the name from
124   `/api/projects/{team_id}/endpoints/{name}/run`.
1252. `endpoint-get` to fetch full config. Note: `data_freshness_seconds`, `is_materialized`,
126   current version, query kind.
1273. `endpoint-materialization-status` to see materialisation health (failed? eligible?).
1284. `endpoint-versions` to see the query history and which version is current (the one callers
129   hit unless they pin `?version=N`).
1305. If the user gave specific symptoms ("times out", "memory limit", "very slow"), walk the
131   decision tree above. Otherwise, present the four checkpoints and ask which matches.
1326. End with a concrete recommendation — bump TTL, materialise, rewrite, or rollback — and
133   confirm before applying it.
134 
135## Example interaction
136 
137```text
138User: "monthly_active_users is timing out for some callers"
139 
140Agent steps:
141- endpoint-get monthly_active_users → HogQL endpoint, data_freshness_seconds=900,
142  is_materialized=false, current version v4
143- endpoint-materialization-status monthly_active_users → not materialised; eligible: true
144- endpoint-versions monthly_active_users
145  → v4 is current; v1–v3 are prior query revisions (callers hit v4 unless pinned)
146- endpoints-materialization-preview → eligible, clean transform with one range pair
147 
148- "Three things stand out:
149   1. Cache TTL is 900 seconds (the 15-min floor), but the data is
150      aggregating a month — that aggressive a refresh probably isn't
151      necessary. Bumping to 1 hour (3600s) would dramatically reduce
152      ClickHouse load.
153   2. It's eligible for materialisation with a clean transform. If
154      callers can tolerate 5-15 minute staleness, materialisation
155      would make reads near-instant.
156   3. v4 is the current version, so callers hit it by default — the
157      tuning target is clear.
158 
159   Quickest win: bump TTL to 3600s. Bigger win: materialise.
160 
161   Which trade-off matches your needs? If you need real-time freshness,
162   neither helps and we'd need to rewrite the query — likely narrowing
163   the aggregation window."
164```
165 
166## Important notes
167 
168- **Cache is almost always the first fix.** It's free, instantly reversible, and doesn't change
169  data semantics. Resist jumping to materialisation if a higher TTL would do.
170- **Materialisation has hidden costs.** Storage of the materialised view, refresh compute, and
171  the requirement that callers pass all variables.
172- **Don't rewrite the query without the user.** A query change creates a new version and may
173  break callers!!! Surface the suggested change, get sign-off, then apply.
174- **Three usage signals.** `endpoint-get`'s `last_executed_at` is endpoint-level recency;
175  `endpoint-versions` gives each version's own `last_executed_at`; `query_log` (via `execute-sql`)
176  gives endpoint-level call frequency and per-call cost. All count only personal-API-key calls, and
177  per-version recency can be sparse — confirm with the user before calling a version dead.
178- **The "right" fix depends on the SLA, not the query.** Always ask the user about acceptable
179  staleness before recommending materialisation. A 15-minute-stale materialised view is wrong
180  for a real-time dashboard, regardless of how cheap it'd be.
181- **Tell PostHog what's missing.** If the diagnosis runs into a product limitation (an eligibility
182  rule, the TTL enum, required variables), nudge the team via `agent-feedback`.
183