references/guidelines.md
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 tohasToken*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.barorproperties.foo['bar']for special characters - Person properties: Access via
events.person.properties.fooorpersons.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_idfor 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.certificationis the settled trust mark (certified/deprecated) and lives only here, not oncolumns.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. The last three are profiling statistics and are filled in for data-warehouse columns only.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:
- Fetch the tool schema - Use
posthog:read-data-schemato get the latest schema from the MCP. - Verify data exist - Use
posthog:read-data-schemawith different data types to check if the data you need is captured - Only then write the query - Once you've confirmed the data exists, write and execute your analytical query
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:
- 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. - Small sample — inspect a handful of rows (
LIMIT 10) to verify property shapes and values match expectations. - Aggregation: test the intended aggregation over that short window before widening it. Keep
LIMITto 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
How you should NOT write queries
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
How you should NOT join data
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
propertiesof events, persons, or groups, so we don't get OOMs. Never select the fullpropertiesobject (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
ASand descriptive column aliases:min(timestamp) AS first_seen_atinstead ofmin(timestamp) first. Some short names, such asfn, are reserved even afterAS; 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
eventsby timestamp - Correctness: count unique users with
uniq(person_id)on events, neveruniq(distinct_id)(one person has many distinct_ids, so distinct_id overcounts users) - Memory: avoid
GROUP BYon unbounded high-cardinality expressions (raw URLs, ids, free text) over wide windows: the aggregation holds every distinct value in memory regardless ofLIMIT; 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