Writing Clickhouse Queries Agent Skill
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.
Install
one command, takes just this skill from the repositorynpx skills add https://github.com/PostHog/posthog --skill writing-clickhouse-queriesThe instruction itself
2 sections, as written by the authorWriting 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.
Other skills for the same job
different authors, same section of the catalogue
Efficient database search tool for bioRxiv preprint server. Use this skill when searching for life sciences preprints by keywords, authors, date ranges, or categories, retrieving paper metadata, downloading PDFs, or conducting literature reviews.
Access BRENDA enzyme database via SOAP API. Retrieve kinetic parameters (Km, kcat), reaction equations, organism data, and substrate-specific enzyme information for biochemical research and metabolic pathway analysis.
Access ClinPGx pharmacogenomics data (successor to PharmGKB). Query gene-drug interactions, CPIC guidelines, allele functions, for precision medicine and genotype-guided dosing decisions.
Query NCBI ClinVar for variant clinical significance. Search by gene/position, interpret pathogenicity classifications, access via E-utilities API or FTP, annotate VCFs, for genomic medicine.
Access COSMIC cancer mutation database. Query somatic mutations, Cancer Gene Census, mutational signatures, gene fusions, for cancer research and precision oncology. Requires authentication.
Query Ensembl genome database REST API for 250+ species. Gene lookups, sequence retrieval, variant analysis, comparative genomics, orthologs, VEP predictions, for genomic research.
Query openFDA API for drugs, devices, adverse events, recalls, regulatory submissions (510k, PMA), substance identification (UNII), for FDA regulatory data analysis and safety research.
Query NCBI Gene via E-utilities/Datasets API. Search by symbol/ID, retrieve gene info (RefSeqs, GO, locations, phenotypes), batch lookups, for gene annotation and functional analysis.
How to use it
Take posthog/writing-clickhouse-queries from the repository into ~/.claude/skills for personal
use, or into .claude/skills inside a project.
The agent identifies a skill by the name field in its header. Two skills with the
same name cannot sit side by side — one of them will be ignored.