: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.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
-- Notebooks ClickHouse observability — query_log_archive query pack.
--
-- Every notebook data-plane query is tagged `product=notebooks, feature=query`
-- (sql_v2_data_plane.py wraps both enqueue paths in tags_context; the tags ride the
-- Celery task argument on the inline path and the Temporal activity's context on the
-- materialize path), so `lc_product = 'notebooks'` is the one filter that scopes
-- everything below.
--
-- Run these through the internal Metabase against "PostHog ClickHouse PROD <REGION>
-- Data Tier" (see the querying-production-databases-via-metabase skill):
-- hogli metabase:login --region us
-- hogli metabase:databases --region us
-- hogli metabase:query --region us --database-id <id> < this-file
--
-- Reading notes that apply throughout:
-- * `is_initial_query` drops the per-shard sub-queries a distributed query fans out
-- into. The initiator row already sums read_bytes/read_rows across replicas, so
-- without this filter every cost number roughly doubles.
-- * `query_log_archive` is a Distributed read table pointing at the OPS cluster and is
-- readable from any cluster. Always bound `event_date` — it is partitioned by month.
-- * Rows are QueryFinish / ExceptionBeforeStart / ExceptionWhileProcessing only
-- (the MV filters out QueryStart), so one row is one finished query attempt.
-- * The two write modes are distinguishable by `query_kind`: 'Select' is the
-- phase-1 worker-relay stream, 'Insert' is the phase-2 CH-writes
-- `INSERT INTO FUNCTION s3`. This matters for result columns — see query 4.
-- * `lc_temporal__attempt` is the Temporal retry attempt, so re-executions of the
-- same materialization are visible and attributable.
-- ---------------------------------------------------------------------------
-- 1. Headline: notebook query volume, latency, and ClickHouse cost per hour.
-- This is the panel to watch during a rollout.
-- ---------------------------------------------------------------------------
SELECT
toStartOfHour(event_time) AS hour,
count() AS queries,
countIf(exception_code != 0) AS errors,
round(quantile(0.50)(query_duration_ms)) AS p50_ms,
round(quantile(0.95)(query_duration_ms)) AS p95_ms,
round(quantile(0.99)(query_duration_ms)) AS p99_ms,
formatReadableSize(sum(read_bytes)) AS scanned_total,
formatReadableSize(quantile(0.95)(read_bytes)) AS scanned_p95,
formatReadableSize(sum(result_bytes)) AS returned_total,
sum(result_rows) AS rows_returned,
formatReadableSize(quantile(0.95)(memory_usage)) AS memory_p95
FROM query_log_archive
WHERE event_date >= today() - 7
AND lc_product = 'notebooks'
AND is_initial_query
GROUP BY hour
ORDER BY hour;
-- ---------------------------------------------------------------------------
-- 2. Rollout verification: which path, pool, and ClickHouse identity is actually
-- serving notebook queries. The frame-store prerequisites
-- (dedicated `notebooks` CH user, offline pool) fail *open* and silently — this
-- is the ClickHouse-side confirmation that they engaged.
-- user — 'notebooks' once the dedicated CH user is provisioned, else the default
-- lc_workload — 'offline' once CLICKHOUSE_OFFLINE_CLUSTER_HOST is set on the worker fleet
-- query_kind — Select = worker relay (phase 1); Insert = CH writes (phase 2)
-- ---------------------------------------------------------------------------
SELECT
toStartOfHour(event_time) AS hour,
user AS ch_user,
lc_workload AS workload,
query_kind,
lc_temporal__workflow_type AS workflow,
count() AS queries,
countIf(exception_code != 0) AS errors
FROM query_log_archive
WHERE event_date >= today() - 7
AND lc_product = 'notebooks'
AND is_initial_query
GROUP BY hour, ch_user, workload, query_kind, workflow
ORDER BY hour, queries DESC;
-- ---------------------------------------------------------------------------
-- 3. Failures by ClickHouse exception code. The budget codes the materialize path
-- treats as terminal: 158 TOO_MANY_ROWS, 159 TIMEOUT_EXCEEDED, 160 TOO_SLOW,
-- 241 MEMORY_LIMIT_EXCEEDED, 307 TOO_MANY_BYTES, 396 TOO_MANY_ROWS_OR_BYTES
-- (the result-bytes cap). 202 TOO_MANY_SIMULTANEOUS_QUERIES means the cluster
-- or the notebooks user's own quota is saturated, not that the query is bad.
-- ---------------------------------------------------------------------------
SELECT
exception_code,
errorCodeToName(exception_code) AS exception_name,
count() AS occurrences,
uniq(team_id) AS teams_affected,
round(avg(query_duration_ms)) AS avg_ms_before_failing,
formatReadableSize(sum(read_bytes)) AS wasted_scan,
max(event_time) AS last_seen
FROM query_log_archive
WHERE event_date >= today() - 7
AND lc_product = 'notebooks'
AND is_initial_query
AND exception_code != 0
GROUP BY exception_code
ORDER BY occurrences DESC;
-- ---------------------------------------------------------------------------
-- 4. Bytes and rows delivered to the user, split by write mode.
-- The two modes populate different columns and averaging them together is wrong:
-- * Select (worker relay): the frame leaves CH as a result set -> result_bytes / result_rows
-- * Insert (CH writes s3): the frame is written to S3 by CH -> written_rows, and
-- ProfileEvents['WriteBufferFromS3Bytes'] for the bytes actually pushed to S3
-- (result_bytes is ~0 for an INSERT).
-- `posthog_notebooks_frame_object_bytes` is the mode-independent equivalent, but
-- only on the success path — this query also sees the runs that died.
-- ---------------------------------------------------------------------------
SELECT
query_kind,
count() AS queries,
formatReadableSize(quantile(0.95)(result_bytes)) AS result_bytes_p95,
quantile(0.95)(result_rows) AS result_rows_p95,
quantile(0.95)(written_rows) AS written_rows_p95,
formatReadableSize(quantile(0.95)(ProfileEvents_WriteBufferFromS3Bytes)) AS s3_written_p95,
formatReadableSize(sum(ProfileEvents_WriteBufferFromS3Bytes)) AS s3_written_total
FROM query_log_archive
WHERE event_date >= today() - 7
AND lc_product = 'notebooks'
AND is_initial_query
GROUP BY query_kind;
-- ---------------------------------------------------------------------------
-- 5. Parallel notebook queries per team — exact concurrency, not a rejection count.
-- Prometheus only counts limiter *rejections*; this reconstructs actual occupancy
-- by replaying each query as a +1 at its start and a -1 at its end and taking a
-- running sum. Compare the peak against the per-team ceiling of 2
-- (MATERIALIZE_PER_TEAM_CONCURRENCY) and global 10.
-- Swap `team_id` for `lc_user_id` to get the per-user view.
-- Note: there is no notebook_short_id in the query tags today, so a per-notebook
-- breakdown is not possible.
-- ---------------------------------------------------------------------------
WITH notebook_queries AS (
SELECT
team_id,
query_start_time_microseconds AS started,
query_start_time_microseconds + toIntervalMillisecond(query_duration_ms) AS ended
FROM query_log_archive
WHERE event_date >= today() - 1
AND lc_product = 'notebooks'
AND is_initial_query
),
edges AS (
SELECT team_id, started AS t, 1 AS delta FROM notebook_queries
UNION ALL
SELECT team_id, ended AS t, -1 AS delta FROM notebook_queries
)
SELECT
team_id,
max(running) AS peak_concurrent_queries
FROM (
SELECT
team_id,
t,
sum(delta) OVER (
PARTITION BY team_id ORDER BY t
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running
FROM edges
)
GROUP BY team_id
ORDER BY peak_concurrent_queries DESC
LIMIT 20;
-- ---------------------------------------------------------------------------
-- 6. The whales: most expensive individual notebook queries.
-- `lc_client_query_id` is the data-plane query_id, so a row here joins straight to
-- the `notebook_frame_materialized` structured log and to the Temporal workflow.
-- NB: the `query` column is user-authored SQL — internal triage only, never paste
-- it into a public PR or issue.
-- ---------------------------------------------------------------------------
SELECT
event_time,
team_id,
lc_user_id AS user_id,
lc_client_query_id AS data_plane_query_id,
lc_temporal__attempt AS temporal_attempt,
query_duration_ms,
formatReadableSize(read_bytes) AS scanned,
read_rows,
formatReadableSize(memory_usage) AS memory,
peak_threads_usage,
exception_code,
substring(query, 1, 200) AS query_head
FROM query_log_archive
WHERE event_date >= today() - 1
AND lc_product = 'notebooks'
AND is_initial_query
ORDER BY read_bytes DESC
LIMIT 25;
-- ---------------------------------------------------------------------------
-- 7. Retry amplification. A deterministically-failing whale can re-execute up to 10
-- times (the workflow's maximum_attempts), re-scanning on every attempt. Any row
-- here with attempts > 1 is ClickHouse work spent on a query that was already
-- doomed — the terminal-vs-retryable error classification is what bounds it.
-- ---------------------------------------------------------------------------
SELECT
lc_client_query_id AS data_plane_query_id,
team_id,
max(lc_temporal__attempt) AS attempts,
count() AS ch_executions,
formatReadableSize(sum(read_bytes)) AS total_scanned,
sum(query_duration_ms) AS total_ms,
groupUniqArray(exception_code) AS exception_codes
FROM query_log_archive
WHERE event_date >= today() - 1
AND lc_product = 'notebooks'
AND is_initial_query
AND lc_temporal__workflow_type = 'notebook-frame-materialize'
GROUP BY data_plane_query_id, team_id
HAVING attempts > 1
ORDER BY total_scanned DESC
LIMIT 25;
-- ---------------------------------------------------------------------------
-- 8. Notebooks' share of the offline pool — the "am I hurting batch exports?"
-- question the pool-isolation decision rests on.
-- ---------------------------------------------------------------------------
SELECT
toStartOfHour(event_time) AS hour,
if(lc_product = 'notebooks', 'notebooks', 'other offline work') AS bucket,
count() AS queries,
formatReadableSize(sum(read_bytes)) AS scanned,
round(sum(query_duration_ms) / 1000) AS ch_seconds,
round(sum(ProfileEvents_OSCPUVirtualTimeMicroseconds) / 1e6) AS cpu_seconds
FROM query_log_archive
WHERE event_date >= today() - 3
AND lc_workload = 'offline'
AND is_initial_query
GROUP BY hour, bucket
ORDER BY hour, bucket;
-- ---------------------------------------------------------------------------
-- 9. Transport A/B: worker relay (phase 1) vs ClickHouse writing S3 itself
-- (phase 2), on identical work.
--
-- `query_kind` is the discriminator and needs no new instrumentation: the relay
-- is a Select whose rows stream back through a worker, the CH-writes path is an
-- `INSERT INTO FUNCTION s3(...)` that returns no result set.
--
-- `query_kind` alone is not enough to scope the arms, though. `lc_product =
-- 'notebooks'` also covers the direct lane (`sql_v2_direct.py`) and the data
-- plane's inline transport, and both of those are Selects too. Without the
-- workflow filter below they all land in the worker-relay arm and corrupt its
-- counts, latency, and scan size. Only the materialize activity runs under
-- `notebook-frame-materialize`, so that filter is what makes the two arms
-- comparable rather than one arm plus everything else.
--
-- That difference is also why the delivered-size columns differ per mode, and why
-- averaging the two together is wrong. A Select reports what it handed back in
-- `result_bytes`/`result_rows`; an Insert leaves both at zero and reports what it
-- pushed to S3 in `WriteBufferFromS3Bytes`/`written_rows`. `delivered_*` below
-- coalesces the pair so one column is comparable across modes.
--
-- Read `scanned` as the control: the two modes run the same user query, so a
-- materially different scan between them means the arms are not comparable
-- (different date ranges, or ClickHouse cache state) and the latency delta is
-- not yet evidence.
-- ---------------------------------------------------------------------------
SELECT
if(query_kind = 'Insert', 'ch_writes', 'worker_relay') AS mode,
count() AS queries,
round(quantile(0.5)(query_duration_ms)) AS p50_ms,
round(quantile(0.95)(query_duration_ms)) AS p95_ms,
formatReadableSize(quantile(0.5)(read_bytes)) AS p50_scanned,
formatReadableSize(
quantile(0.5)(
if(query_kind = 'Insert',
ProfileEvents['WriteBufferFromS3Bytes'],
result_bytes)
)
) AS p50_delivered_bytes,
round(quantile(0.5)(
if(query_kind = 'Insert', written_rows, result_rows)
)) AS p50_delivered_rows,
round(quantile(0.95)(memory_usage) / 1e6) AS p95_memory_mb,
countIf(exception_code != 0) AS failures
FROM query_log_archive
WHERE event_date >= today() - 1
AND lc_product = 'notebooks'
AND is_initial_query
AND lc_temporal__workflow_type = 'notebook-frame-materialize'
GROUP BY mode
ORDER BY mode;