Querying Posthog Data

Querying data in PostHog

Use the posthog:execute-sql MCP tool to execute HogQL queries. HogQL is PostHog's variant of SQL that supports most of ClickHouse SQL. We use terms "HogQL" and "SQL" interchangeably. More info is available in the querying-posthog-data skill.

Do not assume that data exists. Use read-data-schema to verify events and properties. For SQL tables, use system.information_schema as described below. Schema discovery does not determine which tool should run the analysis.

Search types

Proactively use different search types depending on a task:

• Grep-like (regex search) with match(), LIKE, ILIKE, position, multiMatch, etc.

• Full-text search with hasToken, hasTokenCaseInsensitive, etc. Make sure you pass string constants to hasToken* functions.

• Dumping results to a file and using bash commands to process potentially large outputs.

Substring search on events-table strings is a full scan: LIKE '%term%', ILIKE '%term%', and position() read the column for every row in the time range, and a leading % makes indexes useless. Before fuzzy-matching a property value, try read-data-schema (event_property_values) to find common exact values. If the requested value is not returned, or the user needs true contains semantics, keep the timestamp window tight, filter event first, and use the substring predicate.

Data Groups

PostHog has two distinct groups of data you can query:

1. System Data (PostHog-Created Data)

Data created directly in PostHog by users - metadata about PostHog setup.

All these tables are prefixed with system.. The most-used entities are system.insights, system.dashboards, system.cohorts, system.feature_flags, system.experiments, system.surveys, system.actions, and system.notebooks — list the full, current set (it drifts as products are added) via information_schema, covered in Schema discovery below.

Example - List insights:

SELECT id, name, short_id FROM system.insights WHERE NOT deleted LIMIT 10

Example - Count insight variables:

SELECT count() AS total FROM system.insight_variables

All entities are scoped by a team by default. You cannot access data of another team unless you switch a team.

2. Captured Data (Analytics Data)

Data collected via the PostHog SDK - used for analytics.

Table | Description events | Recorded events from SDKs persons | Individuals captured by the SDK. "Person" = "user" groups | Groups of individuals (organizations, companies, etc.) sessions | Session data captured by the SDK Data warehouse tables | Connected external data sources and custom views

Discover the columns and relationships of these tables with information_schema, covered in Schema discovery below.

Key concepts:

• Events: Standardized events/properties start with $ (e.g., $pageview). Custom ones start with any other character.

• Properties: Key-value metadata accessed via properties.foo.bar or properties.foo['bar'] for special characters

• Person properties: Access via events.person.properties.foo or persons.properties.foo

• Person property modes: person.properties.* behavior depends on the project's person-on-events setting. Check the project metadata to determine if values are event-time (value at ingestion) or query-time (current value). See Person property modes for details.

• Unique users: Use events.person_id for counting unique users

Example - Weekly active users:

SELECT toStartOfWeek(timestamp) AS week, count(DISTINCT person_id) AS users
FROM events
WHERE event = '$pageview'
  AND timestamp > now() - INTERVAL 8 WEEK
GROUP BY week
ORDER BY week DESC

3. Document Embeddings (Semantic Search)

The document_embeddings table stores text content with vector embeddings, partitioned by model_name. To discover what kinds of data are available:

SELECT product, document_type, count() as cnt
FROM document_embeddings
WHERE model_name = 'text-embedding-3-small-1536'
  AND timestamp >= now() - INTERVAL 1 MONTH
GROUP BY product, document_type
ORDER BY cnt DESC

Run separately for each model. Available models: 'text-embedding-3-small-1536', 'text-embedding-3-large-3072'. You MUST filter on exactly one model_name per query — it routes to the correct underlying ClickHouse table. IN clauses and cross-model queries will fail.

Use embedText(text, model_name) and cosineDistance() for semantic search. See the signals skill for detailed query patterns around the signals product specifically, including required deduplication and metadata extraction.

For a named business or operational measure, look it up in the data catalog (system.information_schema.metrics) before any schema discovery. Run an approved, non-drifted match with data-catalog-metric-run instead of deriving it. Every other outcome means there is no canonical definition to reuse — no match, a drifted match, or a match that is not approved: derive the measure with the schema workflow below and label the result noncanonical. A project without the data catalog has neither that table nor that tool, so an unknown-table error is that case too, and it holds for the rest of the session: stop checking. Everything else starts with that workflow.

Schema discovery (information_schema)

Don't guess table or column names — they differ per entity and drift over time. Discover the live schema for every data group above (system, captured, and data-warehouse tables) by querying system.information_schema via execute-sql. Four virtual tables carry the schema itself, and each one holds a different set of fields — project a field on the surface that owns it, or the query fails:

• tables — one row per table. Fields: table_catalog, table_schema, table_name, table_type, description, row_count, certification. table_type is one of system, data_warehouse, view, posthog (built-in analytics tables like events / persons), or information_schema. certification is the settled trust mark (certified / deprecated) and lives only here, not on columns.

• columns — one row per column. Fields: table_schema, table_name, column_name, ordinal_position, data_type, is_nullable, is_array, field_kind, description, null_fraction, min_value, max_value, sensitivity. null_fraction, min_value and max_value are profiling statistics and are filled in for data-warehouse columns only. sensitivity names the kind of personal or secret data a synced data-warehouse column likely holds (email, phone, name, address, date_of_birth, ip_address, token, secret, card_number), or NULL. Leave flagged columns out of a query unless the question needs them.

• relationships — one row per joinable relationship. Fields: source_table, source_column, target_table, target_column, relationship_kind, via, confidence, reasoning.

• data_types — one row per HogQL type. Fields: type_name, description.

certification on tables and confidence / reasoning on relationships come from the data catalog. The project catalog, which is what execute-sql reads by default, always carries all three. A caller without data catalog access still selects them, and every value reads NULL. A NULL there never means a wrong field name.

The two surfaces differ in what else a NULL means. On tables, certification reads NULL for a table nobody marked. On relationships, confidence and reasoning hold the review evidence of an accepted relationship proposal. Only a data warehouse join that still matches its proposal carries that evidence. Every built-in join and every field traverser reads NULL for both fields, even on a project with full catalog access. A NULL there means no review evidence, not a broken join: read source_column and target_column, and use the join.

The same namespace carries six more catalog surfaces, each about project state rather than schema: metrics, certifications (the full trust-mark review queue, as opposed to the settled tables.certification mark), relationship_proposals, data_quality_checks, data_quality_check_runs, and data_quality_health. The project serves the three data-quality surfaces only while data quality checks are on for it.

A direct connection queried with connectionId is the runtime that drops surfaces. It serves tables, columns, and data_types only, and its tables has no certification. Leave certification out of a connectionId query. Do not read relationships there before a join, because the surface is absent and the query fails on an unknown table.

Every surface describes itself, so its live field set is always discoverable — ask the catalog instead of trusting the lists above:

SELECT column_name, data_type
FROM system.information_schema.columns
WHERE table_name = 'system.information_schema.tables'
ORDER BY ordinal_position

List tables — filter table_type to target a group (system, data_warehouse, view, posthog):

SELECT table_name, description
FROM system.information_schema.tables
WHERE table_type = 'system'
ORDER BY table_name

Find a table by what its docs say — names are often opaque (especially data-warehouse tables), so search the description text instead of guessing names. The documentation lives in system.information_schema.tables.description (the catalog) — not on the system.data_warehouse_tables entity, which only holds connection metadata:

SELECT table_name, description, certification
FROM system.information_schema.tables
WHERE table_type = 'data_warehouse' AND description ILIKE '%canonical mrr%'

Column docs are searchable the same way via system.information_schema.columns.description (the system. prefix is required — a bare information_schema.columns is an unknown table). Prefer an ILIKE filter over dumping the whole catalog and scanning it yourself.

Inspect a table's columns:

SELECT column_name, data_type, is_nullable, description
FROM system.information_schema.columns
WHERE table_name = 'events'

This works for system.* entity tables too — query them by full name, e.g. WHERE table_name = 'system.insights'. Their column sets differ per entity and from REST/tool response fields, so confirm columns before projecting them. If a required field is absent, use a tool that exposes it or explain the limitation; do not remove the filter or substitute a different field just to make the query succeed.

Discover how a table joins to others:

SELECT source_table, source_column, target_table, target_column, relationship_kind
FROM system.information_schema.relationships
WHERE source_table = 'events'

relationship_kind is either lazy_join (a foreign-key-style join to a related table, e.g. events.person_id to persons) or field_traverser (an alias that hops to another field on the same row); via names the resolver when one applies.

Interpret a data type — data_types describes the possible values of columns.data_type (String, Integer, Float, Decimal, Boolean, Date, DateTime, UUID, JSON, Array, Struct, Expression, VirtualTable, Unknown):

SELECT type_name, description FROM system.information_schema.data_types

information_schema covers table and column structure. To verify which events, properties, and property values actually exist in captured data, use read-data-schema (see Schema verification below).

Querying guidelines

Schema verification

Before writing analytical queries, always verify that:

• The required event names or actions exist.

• Properties and property values of events, persons, sessions, and groups data exist.

Follow this workflow:

1. Fetch the tool schema - Use posthog:read-data-schema to get the latest schema from the MCP.

2. Verify data exist - Use posthog:read-data-schema with different data types to check if the data you need is captured

3. Only then write the query - Once you've confirmed the data exists, write and execute your analytical query

<example> User: how many times the tool search was used? Assistant: 1. First, verify the events exist: - Call `posthog:read-data-schema` with `kind: events` - Look for events/actions matching the request 2. If required events don't exist, inform the user immediately instead of running queries that will return empty results 3. If events exist, like "tool executed", verify the properties: - Call `posthog:read-data-schema` with `kind: event_properties` and `event_name: tool executed` - Look for properties indicating a tool 4. Check other events/actions or return if required properties don't exist 5. If events exist, like "tool_name", verify the property values: - Call `posthog:read-data-schema` with `kind: event_property_values`, `event_name: tool executed`, and `property_name: tool_name` - Follow the pattern from the sample or dig deeper into existing properties with SQL queries. 6. Only then write and execute the analytical SQL query

<reasoning> Assistant should verify the data schema to write a correct SQL query, as the data schema varies over time. </reasoning> </example>

<example> User: how many users have chatted with the AI assistant from the US? Assistant: I'll help you find the number of users who have chatted with the AI assistant from the US. Let me create a todo list to track this implementation. 1. Find the relevant events to "chatted with the AI assistant" 2. Find the relevant properties of the events and persons to narrow down data to users from specific country 3. Retrieve the sample property values for found properties 4. Create the insight schema by using the data retrieved in the previous steps 5. Generate the insight 6. Analyze retrieved data <reasoning> The task list helps the assistant to stay on track. </reasoning> </example>

This prevents wasted API calls and gives users immediate feedback when the data they're looking for doesn't exist.

Progressive exploration

For unfamiliar or potentially large datasets, use a short timestamp window to check the data and intended query shape before widening it:

1. Count first — SELECT count() FROM events WHERE timestamp >= now() - INTERVAL 1 DAY AND event = 'foo'. Confirms the data exists and gives a sense of volume.

2. Small sample — inspect a handful of rows (LIMIT 10) to verify property shapes and values match expectations.

3. Aggregation: test the intended aggregation over that short window before widening it. Keep LIMIT to bound the output.

Counts and samples check volume and property shapes; they do not prove a join or aggregation will fit in memory. LIMIT bounds returned rows. Joins and aggregations can still process the full input before applying it.

Recovering from memory failures

Follow the error's advice: wait and retry once when it reports temporary server capacity. The memory_limit category alone cannot distinguish server pressure from a per-query limit.

If the error says the query itself ran out of memory, shorten the time range in WHERE and test one adjusted query before running more windows. Avoid parallel retries of the same failing query shape. Even a query filtered by time or person can be too large. If the smaller window still fails, reduce join inputs or grouping cardinality only where that preserves the requested calculation.

When splitting a requested range, use non-overlapping windows such as timestamp >= start AND timestamp < end with the same filters. Split only when each window can be computed independently without changing the calculation. Combine complete counts and sums by adding them, and compute averages from totals and counts. Do not sum distinct counts or combine per-window quantiles, funnels, or retention results as if they covered the full range. Rows cut off by LIMIT or truncation are incomplete. Track which windows succeeded and disclose missing windows or reduced scope; do not present partial coverage as the full answer.

Skipping index

You should use the skipping index signature to write optimized analytical queries.

Time ranges

All analytical queries and subqueries must always have time ranges set for supported tables (events). If the user doesn't state it, assume default time range based on the data volume, like a day, week, or month.

The bound must be a WHERE predicate on timestamp. A time condition that appears only inside an aggregate argument, like countIf(event = 'x' AND timestamp > now() - INTERVAL 1 DAY), filters nothing: every historical row is still read. Put the outer window in WHERE and keep only the split inside the aggregate.

Point lookups need a time bound too. Filtering on a session id, trace id, distinct_id, or a property value without a timestamp bound scans the team's entire history, because those filters don't align with the table's date-first sort key. Derive the window from context (the session's day, the incident's date), or start with a recent window and widen only if the result is empty.

How you should use time ranges

<example> User: Find events from returning browsers - browsers that appeared both yesterday and today Assistant: ```sql SELECT event FROM events WHERE timestamp >= now() - INTERVAL 1 DAY and properties['$browser'] IN (SELECT properties['$browser'] FROM events WHERE timestamp >= now() - INTERVAL 2 DAY and timestamp < now() - INTERVAL 1 DAY) ``` </example>

How you should NOT write queries

<example> User: List 10 events with SQL Assistant: ```sql SELECT event, timestamp, distinct_id, properties FROM events ORDER BY timestamp DESC LIMIT 10 ``` </example>

JOINs

General guidelines

Keep in mind that the right expression is loaded in memory when joining data in ClickHouse, so the joining query or table must always fit in memory. Common strategies:

• Analytical functions and combinators.

• Subqueries as a source or filter.

• Arrays (arrayMap, arrayJoin) and ARRAY JOIN.

A subquery used as a join/correlation source must pre-filter and, where possible, pre-aggregate — push the time range, WHERE, and any GROUP BY inside it so the right side stays small in memory. Wrapping a full table in a subquery without narrowing it gains nothing. When you only need a single match per row (enrichment lookups, e.g. attaching one attribute from system data), use LEFT ANY JOIN — it stops at the first match, using less memory and running faster than a regular join.

System data

You are allowed joining system data. Insights are the most used entity, so keep it on the left.

Analytical data

Prefer using analytical functions and subqueries for joins. Do not use raw joins on the events table.

Scan events once per question where you can: conditional aggregates (countIf, sumIf, uniqIf, argMax) or a window function over one scan replace a self-join, repeated subqueries over the same rows, and UNIONs of the same range. CTEs are inlined, not materialized: a CTE referenced twice executes twice. Subqueries that correlate different events (like the example below) are fine.

How you should join data

<example> User: Find ai traces with feedback Assistant: ```sql SELECT g.properties.$ai_trace_id as trace_id FROM events AS g WHERE timestamp >= now() - INTERVAL 1 WEEK AND g.event = '$ai_generation' AND trace_id IN (SELECT properties.$ai_trace_id FROM events WHERE event = '$ai_feedback' AND timestamp >= now() - INTERVAL 1 WEEK) ``` <reasoning>A subquery is used instead of a JOIN clause. Both queries have the timestamp filters.</reasoning> </example>

How you should NOT join data

<example> User: Find ai traces with feedback Assistant: ```sql SELECT g.properties.$ai_trace_id FROM events AS g INNER JOIN (SELECT properties.$ai_trace_id as trace_id FROM events WHERE event = '$ai_feedback') AS f ON g.properties.$ai_trace_id = f.trace_id WHERE g.event = '$ai_generation' ``` <reasoning>Join is not necessary here. The assistant could've used a subquery.</reasoning> </example>

Other constraints

• Your query results are capped at 100 rows by default. You can request up to 500 rows using a LIMIT clause. If you need more data, paginate using LIMIT and OFFSET in subsequent queries.

• You should cherry-pick properties of events, persons, or groups, so we don't get OOMs. Never select the full properties object (e.g., SELECT properties FROM events) and dump it into the conversation output. Instead, select only the specific properties you need (e.g., properties.$browser, properties.$os). If you must inspect the full properties object, dump the query results to a file and use bash commands to explore it.

• When query results contain large JSON blobs (e.g., AI trace inputs/outputs, full property objects), always dump them to a file rather than outputting them directly. Use bash commands to process the file.

HogQL Differences from Standard SQL

Property access

-- Simple keys
properties.foo.bar

-- Keys with special characters
properties.foo['bar-baz']

Unsupported/changed functions

Don't use | Use instead toFloat64OrNull(), toFloat64() | toFloat() toDateOrNull(timestamp) | toDate(timestamp) LAG(), LEAD() | lagInFrame(), leadInFrame() with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING count(*) | count() cardinality(bitmap) | bitmapCardinality(bitmap) split() | splitByChar(), splitByString()

JOIN constraints

Relational operators (>, <, >=, <=) are forbidden in JOIN clauses. Use CROSS JOIN with WHERE:

-- Wrong
JOIN persons p ON e.person_id = p.id AND e.timestamp > p.created_at

-- Correct
CROSS JOIN persons p WHERE e.person_id = p.id AND e.timestamp > p.created_at

Syntax extensions and HogQL functions

Find the reference for Sparkline, SemVer, Session replays, Actions, Translation, HTML tags and links, Text effects, and more.

Other rules

• Use explicit AS and descriptive column aliases: min(timestamp) AS first_seen_at instead of min(timestamp) first. Some short names, such as fn, are reserved even after AS; use a descriptive name or backtick-quote the alias and its references.

• WHERE clause must come after all JOINs

• No semicolons at end of queries

• toStartOfWeek(timestamp, 1) for Monday start (numeric, not string)

• Always handle nulls before array functions: splitByChar(',', coalesce(field, ''))

• Performance: always filter events by timestamp

• Correctness: count unique users with uniq(person_id) on events, never uniq(distinct_id) (one person has many distinct_ids, so distinct_id overcounts users)

• Memory: avoid GROUP BY on unbounded high-cardinality expressions (raw URLs, ids, free text) over wide windows: the aggregation holds every distinct value in memory regardless of LIMIT; normalize the value (strip ids from paths) or narrow the window

SQL Variables

Review the reference for SQL variables and dashboard filters.

Available HogQL functions

Verify what functions are available using the reference list with suitable bash commands.

Examples

Weekly active users with activation event:

SELECT week_of, countIf(weekly_event_count >= 3)
FROM (
   SELECT person.id AS person_id, toStartOfWeek(timestamp) AS week_of, count() AS weekly_event_count
   FROM events
   WHERE event = 'activation_event'
     AND properties.$current_url = 'https://example.com/foo/'
     AND toStartOfWeek(now()) - INTERVAL 8 WEEK <= timestamp
     AND timestamp < toStartOfWeek(now())
   GROUP BY person.id, week_of
)
GROUP BY week_of
ORDER BY week_of DESC

Find cohorts by name:

SELECT id, name, count FROM system.cohorts WHERE name ILIKE '%paying%' AND NOT deleted

List feature flags:

SELECT key, name, rollout_percentage
FROM system.feature_flags
WHERE NOT deleted
ORDER BY created_at DESC
LIMIT 20