writing-clickhouse-queries
π―SkillInstallation
npx skills add posthog/posthog --skill writing-clickhouse-queriesRun it in a terminal; it asks which AI coding tools to install it for (Claude Code, Codex, Cursor and more).
Uses skills.sh Β· Node.js 22.20+
After installing
Ask for related work and the agent uses it on its own, or call it yourself.
/writing-clickhouse-queriesInstall by hand
Copy the whole skill folder from GitHub to the place below. Copying only SKILL.md can leave out files the skill uses.
Open the skill folder β- This project
.claude/skills/writing-clickhouse-queries/ - All projects
~/.claude/skills/writing-clickhouse-queries/
These paths are for Claude Code. For other tools, the command above puts it in the right place.
SKILL.md
View on GitHub βGuide for writing performant ClickHouse queries in PostHog product code. Use when writing HogQL query runners, adding or reviewing a ClickHouse call site (`sync_execute`, `execute_hogql_query`, a `QueryRunner`) and choosing its `Workload` (ONLINE, OFFLINE, LOGS, ENDPOINTS), designing a ClickHouse table for a new product, adding materialized columns or skip indexes, or choosing a row ID format. For optimizing an existing query that is already too slow, use `/optimizing-clickhouse-and-hogql-queries` instead.
Writing ClickHouse queries for new products
If you're optimizing an existing query rather than writing a new one, this is the wrong skill. Use /optimizing-clickhouse-and-hogql-queries instead. That skill covers layer triage, smell scanning (FROM ... FINAL, JSONExtract over properties, missing skip indexes, self-joins, CTE blow-up), measurement on the Test Cluster, and applying the fix at the right layer.
Read docs/published/handbook/engineering/databases/clickhouse-queries-new-products.md for the authoritative guide on writing new queries.
Then pull in whichever related docs the task touches:
hogql-python.mdfor HogQL in Pythonmaterialized-columns.mdquery-performance-optimization.md
When to use
- Writing or reviewing a
QueryRunnersubclass inposthog/hogql_queries/orproducts/*/backend/ - Adding or reviewing any ClickHouse call site, in request code, a Celery task, a Temporal activity, or a Dagster asset: pick its
Workloadwithreferences/choosing-a-workload.md - Adding a new ClickHouse table or ALTER for a product (
posthog/clickhouse/migrations/) - Choosing a row ID format for a new table
- Adding or removing materialized columns, skip indexes, or projections
For investigating an existing slow query, debugging a system.query_log row, or reviewing a proposed HogQL printer change for performance, use /optimizing-clickhouse-and-hogql-queries.
Not the right skill for: customer-facing ad-hoc HogQL via Max / posthog:execute-sql, use query-examples for that. For migration mechanics (node roles, engines, replication), use clickhouse-migrations.
Choose a workload on purpose
Every call site passes an explicit workload=, and the PR description says which one and why.
ONLINE is only for results a person waits for on screen.
Work that no person waits for (scheduled Celery tasks, most Temporal activities, Dagster assets, exports, backfills) is OFFLINE.
The workload is a tag of intent; the pod's CLICKHOUSE_HOST decides which replicas run the query.
Read references/choosing-a-workload.md for the decision table, the overrides in sync_execute, and how to check where queries land.
More from this repository10
Connects your AI coding tool to PostHog, so you can ask about your app's usage stats, errors, user session recordings and logs in plain words. Setup takes one command, and each PostHog product has a free monthly tier.
A skill for implementing agent modes in PostHog, where modes manage agent context and inject tools, prompts, and behaviors for specific products, use cases, or areas like Product Analytics and SQL.
Rules for writing database changes for ClickHouse, the analytics database used in the PostHog codebase. It helps you pick the right group of servers for each table, so changes land in the right place and do not break other jobs.
Skill
Skill
Skill
Skill
Skill
Skill
Skill