:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Executes HogQL — PostHog's variant of SQL that supports most of ClickHouse SQL. "HogQL" and "SQL" are used interchangeably.
When to use execute-sql
Use SQL for record inspection, custom calculations, joins, existing SQL, or requests for SQL. SQL results can inform how you construct a later typed query. Typed query tools cannot accept SQL results as input. SQL cases include:
- Searching or listing existing PostHog entities — insights, dashboards, cohorts, feature flags, experiments, surveys. No typed query tool covers these; query the
system.*tables. - Multi-event joins or aggregations across event types that do not fit a single series.
- Sophisticated queries beyond typed query schemas — custom grouping, window functions, non-trivial CTEs, data warehouse joins.
For governed measures, check for a matching approved metric before deriving a new calculation.
Default to typed runners for new product analytics and dashboard insights whenever their schemas support the requested calculation, including simple event counts, unique users, property sums, breakdowns, and time series (query-trends). Use the matching typed runner for funnels, retention, stickiness, paths, and lifecycle; do not approximate their standard calculation rules with SQL.
When both methods fit, use the typed runner. SQL familiarity, SQL examples, or an earlier SQL discovery call do not justify SQL for the final analysis. Use SQL directly for the SQL-specific tasks above, without requiring a failed typed-query attempt. Keep valid existing SQL when it fits the task, and reassess when the task changes.
Both typed queries and SQL support visualizations. Save supported dashboard analyses as native query nodes. A chart or table request, or a harness needing a separate rendering call, does not justify SQL.
{guidelines}
{schema_discovery}
Common pitfalls
- For
system.*entities,information_schemastores the fully-qualifiedtable_name:table_name = 'system.insights'works alone. The bare name works only with a schema filter:table_schema = 'system' AND table_name = 'insights'. A baretable_name = 'insights'alone returns zero rows. - HogQL rejects the ClickHouse
SETTINGSclause outright — appendingSETTINGS ...(e.g. to tunemax_execution_timeorjoin_algorithm) always fails withUnsupported: SelectStmt.settingsClause(). Don't include it. toDate()takes exactly one argument — it does not accept ClickHouse'stoDate(value, timezone)form. Convert timezone first withtoTimeZone(), then wrap intoDate():toDate(toTimeZone(timestamp, 'US/Pacific')), nottoDate(timestamp, 'US/Pacific').- Width-suffixed conversion functions aren't supported —
toInt64,toInt32,toFloat64,toUInt8, etc. (and theirOrNull/OrZerovariants) always fail. Use the unsuffixed form instead:toInt(),toFloat(),toUInt(),toIntOrNull(),toFloatOrZero(), and so on.
Format SQL for readability
Write SQL a human can scan: multi-line with indentation, one column/CTE per line, and inline -- comments for non-obvious logic. This matters most for queries you save via view-create / view-update — the SQL editor stores and renders the string verbatim, so a minified one-liner stays unreadable for whoever opens the view later.
Handling large results
Large JSON values in results (notably full properties objects) are truncated by default. If you anticipate a large result set, or you are selecting the full properties object (e.g., SELECT properties FROM events), dump the results to a file and process them with bash rather than returning them inline. Alternatively, cherry-pick specific keys (properties.$browser) instead of the whole object.
Large LLM trace fields live on the posthog.ai_events table, not events.properties
For LLM events ($ai_generation, $ai_trace, $ai_span, etc.) the heavy keys are not stored on events.properties — they live as native columns on a dedicated ClickHouse table. Like posthog.trace_spans / posthog.metrics, reference it as posthog.ai_events (a bare FROM ai_events errors with "Unknown table"):
events property |
ai_events column |
|---|---|
$ai_input |
input |
$ai_output |
output |
$ai_output_choices |
output_choices |
$ai_input_state |
input_state |
$ai_output_state |
output_state |
$ai_tools |
tools |
Other AI properties (token counts, costs, model, $ai_trace_id) stay on events in all regimes and are safe to query there.
posthog.ai_events is ORDER BY (team_id, trace_id, timestamp), so anchor on trace_id, never scan by timestamp. Rows are dropped after the retention period (30 days by default), so older traces have no content. Nothing restricts which heavy columns an event can carry, but the typical shape is: $ai_generation carries input / output_choices / tools (embeddings carry input); $ai_span and $ai_trace carry input_state / output_state.
Single trace —
SELECT input, output_choices FROM posthog.ai_events WHERE trace_id = '<id>' ORDER BY timestamp.Batch / analytics — filter the timestamp-indexed
eventstable first to get the trace IDs, then fetch heavy content fromposthog.ai_eventsanchored ontrace_id:WITH matching_traces AS ( SELECT DISTINCT properties.$ai_trace_id AS trace_id FROM events WHERE event = '$ai_generation' AND timestamp >= now() - INTERVAL 7 DAY AND properties.$ai_model = 'gpt-4o' ) SELECT a.trace_id, a.span_id, a.model, a.input, a.output_choices FROM posthog.ai_events AS a WHERE a.trace_id IN (SELECT trace_id FROM matching_traces) ORDER BY a.trace_id, a.timestamp
The query-llm-trace / query-llm-traces-list tools read posthog.ai_events for you; prefer them when a single trace's content is all you need.
Observability data-plane tables: logs, posthog.trace_spans, posthog.metrics
PostHog ingests OpenTelemetry signals into three ClickHouse-backed tables that are queryable via HogQL. Note the namespacing asymmetry — logs is registered at the root, while trace_spans and metrics live under the posthog. namespace and must be referenced as such (e.g. FROM posthog.trace_spans):
logs— log entries. Common fields:body(also exposed asmessage),severity_text,severity_number,service_name,attributes,resource_attributes,trace_id,span_id,timestamp. Preferposthog:query-logsfor filtered list queries; reach for SQL for aggregations across services or joins withposthog.trace_spans/posthog.metricsbytrace_id.posthog.trace_spans— OpenTelemetry spans. Common fields:trace_id,span_id,parent_span_id,is_root_span,name,service_name,kind(0-5),status_code(0 Unset, 1 OK, 2 Error),duration_nano,timestamp,end_time,attributes,resource_attributes. Preferposthog:query-apm-spans/posthog:apm-trace-getfor span listing and full-trace fetches; reach for SQL for joins withlogs/posthog.metricsbytrace_id, exemplar lookups, or aggregations the typed tools don't expose.posthog.metrics— OpenTelemetry metric points. Common fields:metric_name,metric_type(counter/gauge/histogram),value,count,histogram_bounds,histogram_counts,unit,aggregation_temporality(deltaorcumulative),is_monotonic,service_name,trace_id,span_id,attributes,resource_attributes,timestamp. Preferposthog:query-metricsfor time series (rates, increases, quantiles, formulas); reach for SQL for exemplar lookups, joins withlogs/posthog.trace_spans, or aggregations the typed tool doesn't expose. A projection pre-aggregates by(team_id, time_bucket, toStartOfMinute(timestamp), service_name, metric_name, metric_type, resource_fingerprint)withcount/sum/min/max(value), so per-minute aggregations grouped by those keys are very cheap. Important for counters: checkaggregation_temporalitybefore aggregating —SUM(value)over a window is only correct fordelta. Forcumulativecounters, the value is a running total, so useargMax(value, timestamp)(ormax(value) - min(value)for a rate) to avoid double-counting. Always filter or split byaggregation_temporalitywhen both regimes can appear for a givenmetric_name.
All three share team_id, time_bucket, service_name, resource_fingerprint, and where applicable trace_id. trace_id on posthog.metrics is the OpenTelemetry exemplar pattern — a metric anomaly can be drilled into via a sample trace_id to pull the full trace and correlated logs. Ingestion keeps the first exemplar of each data point, so only points whose SDK attached an exemplar carry a trace_id; most rows have ''. Filter trace_id != '', and when no point in the window has one, anchor on posthog.trace_spans instead, as in the example below.
trace_id format: Both logs and posthog.trace_spans store trace_id as a 24-character base64-encoded 16-byte value (e.g. hex 21EDB3A025A9ECD32ADF3E5D7548A4F4 encodes to Ie2zoCWp7NMq3z5ddUik9A==). The MCP API layer converts to hex via hex(tryBase64Decode(trace_id)) for display. Raw HogQL queries see the base64 form. Unset = 'AAAAAAAAAAAAAAAAAAAAAA=='. posthog.metrics.trace_id uses the same base64 form when an exemplar is present and is '' when unset. Joins are direct equality on trace_id.
User HogQL queries against logs, posthog.trace_spans, and posthog.metrics are capped at 50 GB read per query to prevent unbounded scans.
Always pass a tight timestamp window. Default to the last hour and widen only when justified.
Example: three-signal correlation (span-anchored)
User: An error spike on `checkout` over the last hour — show me a sample slow error trace and the logs in that request. Assistant: I'll pick the slowest error root span as the anchor, then pull the spans and logs in one stitched query.WITH anchor AS (
SELECT trace_id
FROM posthog.trace_spans
WHERE service_name = 'checkout'
AND is_root_span
AND status_code = 2
AND timestamp >= now() - INTERVAL 1 HOUR
ORDER BY duration_nano DESC
LIMIT 1
)
SELECT 'span' AS source, name AS detail, service_name, duration_nano, status_code, NULL AS severity_number, timestamp
FROM posthog.trace_spans
WHERE trace_id = (SELECT trace_id FROM anchor)
AND timestamp >= now() - INTERVAL 1 HOUR
UNION ALL
SELECT 'log', body, service_name, NULL, NULL, severity_number, timestamp
FROM logs
WHERE trace_id = (SELECT trace_id FROM anchor)
AND timestamp >= now() - INTERVAL 1 HOUR
ORDER BY timestamp
- Anchoring on `posthog.trace_spans` always works because spans always carry a populated `trace_id`. To drill from a metric spike instead, swap the CTE for `argMax(trace_id, value) FROM posthog.metrics WHERE … AND trace_id != ''`; it returns a trace only when the service's SDK sends exemplars.
- `trace_id` is stored as base64 in both `logs` and `posthog.trace_spans`, so direct equality works — no decoding needed.
- `UNION ALL` with a `source` discriminator avoids three separate calls and keeps the timeline interleaved.
- Note `logs` is root-level while `posthog.trace_spans` and `posthog.metrics` require the namespace prefix.
- The inner `WHERE` clauses repeat the time window so the optimizer keeps both legs of the UNION efficient.