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

querying-tophog

Query the tophog ClickHouse table to identify hot or expensive ingestion actors during incident triage.

713|118|Updated Aug 11, 2020
Search Tags:#Kafka#clickhouse#metabase#PostHog#ingestion pipeline#incident triage#Query
One-click install
npx skills add https://github.com/PostHog/posthog-foss --skill querying-tophog
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: querying-tophog
Source: https://github.com/PostHog/posthog-foss/tree/main/.agents/skills/querying-tophog
Command: npx skills add https://github.com/PostHog/posthog-foss --skill querying-tophog

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Fleet-level Prometheus metrics cannot answer "which team or distinct_id is causing this" during ingestion incidents because per-metric label cardinality is unbounded. This Skill lets you query the tophog heavy-hitter table in ClickHouse to pinpoint hot or expensive actors (team_id, distinct_id, session_id, partition) behind ingestion lag, hot Kafka partitions, expensive person processing, and merge storms.

Core Features & Use Cases

  • Internal Metabase access path: Authenticate via the engineer's own SSO session using hogli commands, discover the correct ClickHouse database id, and run queries without standing credentials.
  • Schema and metric discovery: Full tophog table schema, read-side aggregation semantics per type (sum, max, avg), and live metric inventory discovery since metrics evolve with deploys.
  • Cost-vs-volume triage lens: Rank actors by summed timer value and per-event cost to distinguish hot keys (volume problems) from expensive actors (merge-heavy or fat person properties).
  • Use Case: During an incident where a single Kafka partition is lagging, run the canned top-actors-by-person-processing-cost query scoped to that partition to find the distinct_id driving merge storms, then hand off to the pganalyze MCP for Postgres-level analysis.

Quick Start

Ask the agent to find the top teams and distinct_ids by person-processing cost in the last hour using the tophog table via Metabase.

Frequently Asked Questions about querying-tophog

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

FAQPage Schema
How do I find which team or distinct_id is causing ingestion lag?▼

Query the tophog ClickHouse table ranking actors by summed process_persons_time value, grouped by team_id and distinct_id. Compute ms per event to distinguish hot keys (high volume, normal cost) from expensive actors (low volume, high per-event cost).

How do I query ClickHouse through Metabase with hogli?▼

Run hogli metabase:login with your region to capture your SSO session, then hogli metabase:databases to find the PostHog ClickHouse PROD data tier database id, and finally hogli metabase:query with your SQL. The cookie is read internally and never enters the transcript.

Why is there no ClickHouse datasource in Grafana for tophog?▼

Production ClickHouse clusters hold customer data, so there is deliberately no Grafana datasource for agents. The sanctioned path is internal Metabase using the engineer's own SSO session, which provides per-person identity and attributable query history.

Why is the partition key missing from my tophog query results?▼

Dimensions are deploy-gated and rows are immutable. The partition key on process_persons_time and merge metrics only exists in data written after that deploy reached the environment, so verify its presence with a countIf check before relying on it.

What are the limitations of querying tophog for incident triage?▼

The table retains only 30 days of data and is partitioned by day, so always bound the timestamp filter. Results contain customer PII like distinct_id and session_id, restricting use to internal triage, and queries appear in your Metabase query history.

Related Skills

Explore More Software Engineering Skills→
ivanshamaevivanshamaev
14

apache-kafka

Configure Apache Kafka topics, producers, consumers, connectors, and monitoring.

Community
Advanced
crbnoscrbnos
2.4k

supabase-postgres-best-practices

Provides Postgres performance optimization rules and SQL examples for queries, schemas, and RLS policies.

Official
Intermediate
israel-salgadoisrael-salgado
2

dt-obs-problems

Correlate Dynatrace AI-detected problems with logs and metrics for root cause analysis.

Community
Advanced
haJ1thaJ1t

mcp-db-connector

Query databases and inspect schemas over the MCP protocol.

Community
Advanced