AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

querying-posthog-data

Explains how to query PostHog data, defaulting to typed runners for supported product analytics. Read it before you write HogQL/SQL. Also read it before you call execute-sql against PostHog. Use it to find or aggregate PostHog entities. These entities include insights, dashboards, cohorts, feature flags, experiments, surveys, hog flows, warehouse data, and persons. Use it for trends, funnels, retention, lifecycle, paths, stickiness, web analytics, error tracking, logs, sessions, and LLM traces. Before you calculate a governed business or telemetry measure, check system.information_schema.metrics for an approved definition. Examples include MRR, activation, billable usage, active organizations, and failure rates. Use the approved definition before you derive a measure from raw events or use a typed domain tool. It also covers HogQL differences, system table schemas, functions, query examples, and schema discovery.

Original source
posthog
License
MIT
Pinned revision
cac7c80a9e5b73484d18eda1bffb47297c938e0c

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

Ask your assistant

Use Agentic Memory skills to read posthog--querying-posthog-data at revision cac7c80a9e5b73484d18eda1bffb47297c938e0c, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 65

references/models-metrics.md

Metrics (OpenTelemetry metric points)

The posthog.metrics table holds OpenTelemetry metric data points from instrumented services. Each row is one observation of a metric (a counter increment, a gauge sample, or a histogram bucket).

Namespacing: Reference this table as posthog.metrics, not bare metrics — it's registered under the posthog. namespace in the HogQL database (see posthog/hogql/database/database.py). The same applies to posthog.metric_attributes. Bare names fail with "Unknown table" at HogQL compile time. (Asymmetric with logs, which is registered at root level.)

For time series (rates, increases, quantiles, formulas), prefer the typed posthog:query-metrics MCP tool. Use HogQL for exemplar lookups, cross-signal joins, and aggregations the typed tool doesn't expose. The schema mirrors logs and posthog.trace_spans deliberately so cross-signal joins are cheap, and trace_id / span_id are first-class columns on every metric row (the OpenTelemetry exemplar pattern).

Exemplars are sparse. Ingestion (rust/capture-logs/src/metric_record.rs) keeps the first exemplar with a valid trace ID on each data point. Only points whose SDK attached an exemplar carry a trace_id / span_id; most rows have ''. When no point in the window has one, anchor cross-signal work on posthog.trace_spans instead.

posthog.metrics

OpenTelemetry metric points. One row per observation. Backed by ClickHouse metrics1 (distributed alias metrics).

Columns

| Column | Type | Description | | ------------------------- | ----------------------------------- | ------------------------------------------------------------------------------- | | uuid | String | Row UUID | | team_id | Int32 | Team this point belongs to | | trace_id | String | OTel trace ID — exemplar trace. Empty string when no exemplar is attached | | span_id | String | OTel span ID — exemplar span | | time_bucket | DateTime | toStartOfDay(timestamp) — first sort key component | | timestamp | DateTime64(6) | Observation time | | observed_timestamp | DateTime64(6) | Ingest time | | service_name | LowCardinality(String) | Emitting service | | metric_name | LowCardinality(String) | Metric name (e.g. http.server.duration, process.cpu.utilization) | | metric_type | LowCardinality(String) | counter, gauge, histogram, summary (OTel data point kind) | | value | Float64 | The metric value. For counters/gauges, the observation. For histograms, the sum | | count | UInt64 | For histograms: number of observations in this point. Defaults to 1 | | histogram_bounds | Array(Float64) | For histograms: explicit bucket boundaries | | histogram_counts | Array(UInt64) | For histograms: per-bucket counts (length = length(histogram_bounds) + 1) | | unit | LowCardinality(String) | OTel unit string (e.g. ms, s, By, 1) | | aggregation_temporality | LowCardinality(String) | delta or cumulative (OTel temporality) | | is_monotonic | Bool | For counters: whether the counter is monotonically increasing | | attributes | Map(String, String) | Metric-point attributes (label dimensions) | | resource_attributes | Map(LowCardinality(String), String) | Resource-level attributes (k8s labels, host info) | | resource_fingerprint | UInt64 | Hash of resource_attributes | | instrumentation_scope | String | Instrumentation library name |

Sort key

(team_id, time_bucket, service_name, metric_name, resource_fingerprint, timestamp). Queries that filter on service_name + metric_name + a time window are very efficient.

Per-minute aggregate projection

The table has a projection projection_aggregate_counts that pre-aggregates by:

(team_id, time_bucket, toStartOfMinute(timestamp), service_name, metric_name, metric_type, resource_fingerprint)

with count() AS event_count, sum(value) AS total_value, min(value) AS min_value, max(value) AS max_value.

Queries that group by those exact keys hit the projection and are near-free. Use this for sparklines, top-N services by metric value, and per-minute breakdowns. The query optimizer picks the projection automatically when the SELECT and GROUP BY match.

Important notes

  • Unit is metric-dependent. Always check unit — http.server.duration may be reported in ms, s, or ns depending on the SDK. Don't assume.
  • trace_id is empty string on points without an exemplar, which is most rows (see the note above). Filter trace_id != '' to keep only exemplar points.
  • trace_id is base64-encoded when set, matching logs and posthog.trace_spans. Joins to those tables are direct equality on trace_id. Use hex(tryBase64Decode(trace_id)) to display in hex.
  • Histograms store histogram_bounds and histogram_counts per row — you need to expand them for quantile estimation. For a quick p95-ish summary, value / count gives the mean per-point.
  • Choose the right temporality. delta metrics measure activity in the interval; cumulative metrics are running totals. Summing value over time only makes sense for delta.
  • User HogQL queries on posthog.metrics are capped at 50 GB read per query.

posthog.metric_attributes

AggregatingMergeTree rollup of metric attribute values, partitioned by service and 10-minute bucket. Same pattern as log_attributes / posthog.trace_attributes. Same posthog. namespacing rule — reference as posthog.metric_attributes.

Columns

| Column | Type | Description | | ---------------------- | ------------------------------------ | ----------------------------------------------------- | | team_id | Int32 | Team | | time_bucket | DateTime64(0) | 10-minute bucket | | service_name | LowCardinality(String) | Emitting service | | resource_fingerprint | UInt64 | Resource identity hash | | attribute_key | LowCardinality(String) | Attribute name | | attribute_value | String | Attribute value | | attribute_type | LowCardinality(String) | metric or resource | | attribute_count | SimpleAggregateFunction(sum, UInt64) | Number of metric points where this attribute appeared |

Use this for cheap discovery of which attribute keys exist on which services.

Common query patterns

List metric names emitted by a service in the last hour:

SELECT metric_name, metric_type, count() AS n
FROM posthog.metrics
WHERE service_name = 'checkout'
  AND timestamp >= now() - INTERVAL 1 HOUR
GROUP BY metric_name, metric_type
ORDER BY n DESC

Per-minute mean value of a non-histogram metric, broken down by service (projection-friendly — count = 1 per row, so count() maps to the projection's event_count):

SELECT
    toStartOfMinute(timestamp) AS minute,
    service_name,
    sum(value) / count() AS mean_value
FROM posthog.metrics
WHERE metric_name = 'http.server.duration'
  AND timestamp >= now() - INTERVAL 1 HOUR
GROUP BY minute, service_name
ORDER BY minute, mean_value DESC

The projection_aggregate_counts projection stores count() AS event_count and sum(value) AS total_value. sum(count_column) (the per-row UInt64) is not in the projection — using it forces a base-table scan. For histograms (where count per row is the bucket observation count), compute the mean separately and don't expect projection acceleration.

Top services by counter rate in the last hour:

SELECT service_name, sum(value) AS total
FROM posthog.metrics
WHERE metric_name = 'http.server.request.count'
  AND metric_type = 'counter'
  AND aggregation_temporality = 'delta'
  AND timestamp >= now() - INTERVAL 1 HOUR
GROUP BY service_name
ORDER BY total DESC
LIMIT 10

Pick the slowest exemplar trace for a metric in a window (exemplar lookup):

SELECT argMax(trace_id, value) AS exemplar_trace_id, max(value) AS peak
FROM posthog.metrics
WHERE service_name = 'checkout'
  AND metric_name = 'http.server.duration'
  AND timestamp >= now() - INTERVAL 10 MINUTE
  AND trace_id != ''

Pair this with posthog:apm-trace-get (or a SQL join on trace_spans) to inspect the trace that drove the spike.