skills.rest
PostHog
PostHogOFFICIAL
Explore all skills by this author →
Website𝕏@PostHogGitHub

writing-clickhouse-queries

Guides writing performant ClickHouse queries and HogQL query runners for PostHog products.

713|118|Updated Aug 11, 2020
Search Tags:#database design#query performance#clickhouse#PostHog#HogQL#materialized columns#skip indexes
One-click install
npx skills add https://github.com/PostHog/posthog-foss --skill writing-clickhouse-queries
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: writing-clickhouse-queries
Source: https://github.com/PostHog/posthog-foss/tree/main/.agents/skills/writing-clickhouse-queries
Command: npx skills add https://github.com/PostHog/posthog-foss --skill writing-clickhouse-queries

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Writing new ClickHouse queries or designing tables for PostHog products without performance guidance leads to slow queries, missing indexes, and poor schema choices that are costly to fix later.

Core Features & Use Cases

  • Query Runner Guidance: Directs engineers writing or reviewing QueryRunner subclasses in posthog/hogql_queries/ or products/*/backend/ to the authoritative performance documentation.
  • Schema Design Support: Covers adding ClickHouse tables or ALTERs via migrations, choosing row ID formats, and adding materialized columns, skip indexes, or projections.
  • Skill Routing: Distinguishes new-query authoring from optimizing existing slow queries, customer-facing HogQL, and migration mechanics, pointing to the right skill for each.
  • Use Case: When adding a new product feature that needs a ClickHouse table, use this Skill to load the handbook guidance on table design, materialized columns, and skip indexes before writing the migration.

Quick Start

Ask the agent to help you write a performant ClickHouse query or design a new table for a PostHog product feature.

Frequently Asked Questions about writing-clickhouse-queries

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I write a performant ClickHouse query in PostHog?▼

Read the authoritative guide at docs/published/handbook/engineering/databases/clickhouse-queries-new-products.md, then pull in related docs on HogQL in Python, materialized columns, and query performance optimization depending on what your task touches.

When should I use materialized columns or skip indexes in ClickHouse?▼

Use them when adding a new table or ALTER for a product where query filters repeatedly extract the same properties. The materialized-columns.md and query-performance-optimization.md handbook docs cover the decision criteria.

Should I use this skill to optimize an existing slow ClickHouse query?▼

No. For optimizing an existing slow query, debugging system.query_log rows, or layer triage, use the optimizing-clickhouse-and-hogql-queries skill instead, which covers smell scanning and measurement on the Test Cluster.

What row ID format should I choose for a new ClickHouse table?▼

The skill directs you to the authoritative new-products query guide in the PostHog handbook, which covers row ID format selection alongside table design, materialized columns, and skip indexes.

Is this skill for customer-facing HogQL or ClickHouse migrations?▼

No. For ad-hoc customer-facing HogQL via Max or execute-sql, use the query-examples skill. For migration mechanics like node roles, engines, and replication, use the clickhouse-migrations skill.

Related Skills

Explore More Software Engineering Skills→
FrancisVargaFrancisVarga

w88 Data Model

Define w88 gaming platform event types, entity relationships, and join keys.

Community
Intermediate
ubay1ubay1
1

perf-optimization

Convert profiling data into prioritized, benchmark-verified code optimizations.

Community
Advanced
chmonitorchmonitor
255

version-upgrade-advisor

Plan ClickHouse version upgrades with pre-upgrade checks and post-upgrade validation.

Official
Intermediate
PostHogPostHog
75

querying-posthog-data

Query PostHog analytics data using HogQL and SQL foundations.

Official
Intermediate