Writing Clickhouse Queries
Developmentwriting-clickhouse-queriesWriting Clickhouse Queries is an Agent Skill for development published in the PostHog/posthog repository on GitHub: a SKILL.md instruction file an AI agent can load to do this task.
Guide for writing performant ClickHouse queries in PostHog product code. Use when writing HogQL query runners, 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.
How to use this skill
Bring this guide into your coding agent with a prompt tailored to the tool you use.
- Open your project in Codex.
- Copy and paste the prompt below.
- Review files and risks before approving any action.
Inspect the Agent Skill at https://github.com/PostHog/posthog/blob/HEAD/.agents/skills/writing-clickhouse-queries/SKILL.md and all supporting files. Report provenance, permissions, scripts, dependencies, and security risks. Propose an exact installation into .agents/skills/writing-clickhouse-queries, but wait for my approval before writing files or running scripts.
Copying this prompt does not install or run the skill. Review third-party files before use.
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 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.