Skip to main content
skill CX Codex

diagnosing-endpoint-performance — Claude Code skill

diagnosing-endpoint-performance — a skill that identifies why a PostHog endpoint is slow or expensive, then recommends a concrete fix: increase cache TTL, enable materialisation, restructure variables, or rewrite the query.

MIT 316 installs ★ 80 updated 2026-10-24

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add PostHog/skills --skill diagnosing-endpoint-performance -a claude-code
Open on GitHub

What diagnosing-endpoint-performance does

When a user reports that an endpoint is timing out, hitting cost limits, or responding slowly, this skill walks through a single endpoint and surfaces the root cause. It starts by checking cache configuration — most often, raising the data_freshness_seconds TTL is the cheapest win. Next, it checks whether the endpoint qualifies for materialisation; if the query can be precomputed into a saved view, reads become near-instant.

If materialisation is blocked, the skill decodes the rejection reason and proposes a rewrite. Common cases: moving a variable filter into a subquery to avoid cardinality issues with JOIN, replacing a cohort breakdown with a property breakdown, or adding a required time-window variable to prevent unbounded scans. The tool also checks version history; often a performance regression came from a single commit and reverting is faster than rewriting.

This skill narrows in on one named endpoint — not a project-wide audit. To surface candidates across many endpoints, use auditing-endpoints first.

What it helps with

  • Find out why an endpoint is slow or expensive
  • Decide whether materialisation would help a specific query
  • Restructure a query so it becomes eligible for materialisation
  • Tune cache TTL to match the calling pattern
  • Pinpoint which version change caused a performance regression

Example prompts for the agent

  • My endpoint /insights/api/event_trends is slow — analyse it
  • Should I materialise this query? Here's the config…
  • We're hitting cost caps on custom_cohort_query — how do I rewrite it?

How to install diagnosing-endpoint-performance

1Skills CLI (Vercel)

npx skills add PostHog/skills --skill diagnosing-endpoint-performance -a claude-code

2Manually: put the files in place

~/.claude/skills/diagnosing-endpoint-performance/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/diagnosing-endpoint-performance/

Step by step, with commands for each platform: How to install skills in Claude Code: five ways

Questions about diagnosing-endpoint-performance

How do I install diagnosing-endpoint-performance in Claude Code?

Run npx skills add PostHog/skills --skill diagnosing-endpoint-performance -a claude-code, then open ~/.claude/skills/diagnosing-endpoint-performance/SKILL.md. The skill will appear in the Claude Code sidebar.

What does diagnosing-endpoint-performance do?

It takes a slow or expensive PostHog endpoint and recommends a concrete fix: bumping cache TTL, enabling materialisation, restructuring filters, or rewriting the query.

How does this differ from auditing-endpoints?

diagnosing-endpoint-performance drills into one endpoint with a full diagnosis. auditing-endpoints scans your project to find candidates for optimisation; you use it for discovery, then switch to diagnosing-endpoint-performance for details.

Source text

SKILL.md · MIT
---
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           |
| `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, the rejection reason from
`endpoints-materialization-preview` is the lead. That tool always resolves. Read the reason and
match it to the bullets below — they summarise the common cases, not every check the server runs,
so the raw reason wins when the two disagree. Propose the rewrite by hand:

- **Cohort breakdown / compare mode rejection** → regular property breakdowns materialise fine;
  only cohort breakdowns are blocked. Swap a cohort breakdown for a property breakdown. Compare
  mode materialises for Trends endpoints; on any other query kind, drop compare mode (expose the
  comparison window as a variable instead).
- **JOINs combined with variables** → a top-level `JOIN` plus a variable filter is rejected for
  materialisation, because applying the variable changes the joined row cardinality and silently
  produces wrong results (e.g. `LEFT JOIN` non-matches lose the variable column). Restructure so the
  variable filters a single table — push the filter into a subquery/CTE that's then joined, rather
  than filtering across the join. This is the most common "looks fine but won't materialise" trap.
- **"Missing variables" / unbounded scan** → the query reads too much data without a filter.
  Encourage adding a required time-window variable (e.g. `date_from`, `lookback_days`).
- **HogQL with `*` / non-deterministic functions** → narrow the columns selected, replace
  `now()` / `today()` with a variable when possible.
- **No bullet matches** → the list above is not exhaustive; the live checks reject more shapes than
  it names (a variable inside an `OR`, a variable compared against another variable, a variable in a
  `HAVING` clause, an unsupported operator, a query kind that can't be materialised). Quote the
  rejection reason to the user verbatim and reason from it directly rather than forcing the nearest
  bullet. Some reasons have no equivalent rewrite at all — the `OR {variables.x} = 'all'`
  optional-variable idiom is one — so say that instead of changing the query's behaviour.

Check `endpoint-versions` to see whether the query was recently changed. Often the regression
came from a specific commit and reverting that version is faster than rewriting.

### Step 4 — Is the slow version even the one being called?

Only the latest version runs by default; older versions run only when a caller pins `?version=N`.
So the version to tune is almost always the current one — unless a pinned older version is the
culprit. Call `endpoint-versions` and read each version's `last_executed_at` to see which versions
have been hit recently; a materialised version with a null or long-stale `last_executed_at` is a
candidate to unmaterialise or delete rather than tune (confirm first — that signal only counts
API-key runs and can be sparse).

For endpoint-level call frequency and per-call cost, query `query_log` with `execute-sql` — it
carries `query_duration_ms`, `read_rows`, and `read_bytes`, handy for confirming how heavy the
endpoint's calls actually are:

```sql
SELECT count() AS calls, max(query_start_time) AS last_called, avg(query_duration_ms) AS avg_ms
FROM query_log
WHERE name = '<endpoint_name>' AND endpoint LIKE '%/endpoints/%' AND is_personal_api_key_request
```

## Workflow

1. Identify the endpoint by name. If the user gave a URL, parse the name from
   `/api/projects/{team_id}/endpoints/{name}/run`.
2. `endpoint-get` to fetch full config. Note: `data_freshness_seconds`, `is_materialized`,
   current version, query kind.
3. `endpoint-materialization-status` to see materialisation health (failed? eligible?).
4. `endpoint-versions` to see the query history and which version is current (the one callers
   hit unless they pin `?version=N`).
5. If the user gave specific symptoms ("times out", "memory limit", "very slow"), walk the
   decision tree above. Otherwise, present the four checkpoints and ask which matches.
6. End with a concrete recommendation — bump TTL, materialise, rewrite, or rollback — and
   confirm before applying it.

## Example interaction

```text
User: "monthly_active_users is timing out for some callers"

Agent steps:
- endpoint-get monthly_active_users → HogQL endpoint, data_freshness_seconds=900,
  is_materialized=false, current version v4
- endpoint-materialization-status monthly_active_users → not materialised; eligible: true
- endpoint-versions monthly_active_users
  → v4 is current; v1–v3 are prior query revisions (callers hit v4 unless pinned)
- endpoints-materialization-preview → eligible, clean transform with one range pair

- "Three things stand out:
   1. Cache TTL is 900 seconds (the 15-min floor), but the data is
      aggregating a month — that aggressive a refresh probably isn't
      necessary. Bumping to 1 hour (3600s) would dramatically reduce
      ClickHouse load.
   2. It's eligible for materialisation with a clean transform. If
      callers can tolerate 5-15 minute staleness, materialisation
      would make reads near-instant.
   3. v4 is the current version, so callers hit it by default — the
      tuning target is clear.

   Quickest win: bump TTL to 3600s. Bigger win: materialise.

   Which trade-off matches your needs? If you need real-time freshness,
   neither helps and we'd need to rewrite the query — likely narrowing
   the aggregation window."
```

## Important notes

- **Cache is almost always the first fix.** It's free, instantly reversible, and doesn't change
  data semantics. Resist jumping to materialisation if a higher TTL would do.
- **Materialisation has hidden costs.** Storage of the materialised view, refresh compute, and
  the requirement that callers pass all variables.
- **Don't rewrite the query without the user.** A query change creates a new version and may
  break callers!!! Surface the suggested change, get sign-off, then apply.
- **Three usage signals.** `endpoint-get`'s `last_executed_at` is endpoint-level recency;
  `endpoint-versions` gives each version's own `last_executed_at`; `query_log` (via `execute-sql`)
  gives endpoint-level call frequency and per-call cost. All count only personal-API-key calls, and
  per-version recency can be sparse — confirm with the user before calling a version dead.
- **The "right" fix depends on the SLA, not the query.** Always ask the user about acceptable
  staleness before recommending materialisation. A 15-minute-stale materialised view is wrong
  for a real-time dashboard, regardless of how cheap it'd be.
- **Tell PostHog what's missing.** If the diagnosis runs into a product limitation (an eligibility
  rule, the TTL enum, required variables), nudge the team via `agent-feedback`.

© PostHog · MIT · PostHog/skills

Similar skills

All skills for Backend and APIs →

From the same set112

The whole set →
signals-scout-session-replay The signals-scout-session-replay PostHog Signals skill checks for drops in session capture and clusters of friction in recordings. Use it to investigate missing replays or recurring trouble on a page. CX Codex +1 317installs signals-scout-health-checks signals-scout-health-checks — a skill that reads active project health issues, groups them by type and severity, and surfaces the ones that matter. Built for PostHog Signals agent—cuts through log noise to find real problems. CX Codex +1 317installs signals-scout-data-pipelines signals-scout-data-pipelines signals scout detects silent delivery failures in PostHog data pipelines. It catches stalled exports, degraded CDP functions, and failing hog flows against baselines. CX Codex +1 317installs signals-scout-observability-gaps The signals-scout-observability-gaps skill finds meaningful events without insight, dashboard, or alert coverage. Use it to review analytics coverage as a product changes. CX Codex +1 316installs signals-scout-replay-vision signals-scout-replay-vision monitors PostHog Replay Vision scanners. Detects throughput drops, quota cliffs, and cross-session score shifts. CX Codex +1 316installs signals-scout-logs signals-scout-logs analyzes PostHog log streams to detect rate shifts, volume spikes, and newly emerging error patterns across time windows. CX Codex +1 315installs managing-endpoint-versions The managing-endpoint-versions skill guides agents through PostHog endpoint versioning workflows. It helps preview SQL changes, roll back queries, and toggle individual version flags safely. CX Codex +1 314installs signals-scout-revenue-analytics The signals-scout-revenue-analytics skill checks PostHog Revenue Analytics sources and settings for stalled Stripe syncs, missing revenue events, and configuration problems. CX Codex +1 313installs

Project sponsors

Companies that keep this analytics open