PostHog 实验漏斗 metric_events 预计算：从每次全表扫描到懒加载缓存表的工程实践

PostHog 实验漏斗 metric_events 预计算从每次全表扫描到懒加载缓存表的工程实践【免费下载链接】posthog: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.项目地址: https://gitcode.com/GitHub_Trending/po/posthog导读本文讲解 PostHog 实验Experiments后端查询链路中的一项关键性能优化将实验漏斗/均值/留存指标中原本每次查询都要全量扫描 ClickHouseevents事件表的metric_eventsCTE改为一次扫描、落表缓存、后续查询直读的懒计算lazy computation方案。读完本文你将理解experiment_metric_events_preaggregated预聚合表的表结构设计、写路径与读路径的完整实现、转换窗口conversion window与 TTL 处理以及它在有序漏斗、均值指标、留存指标上的适用范围与回退策略。问题背景metric_events CTE 是每次查询的热点一个实验漏斗查询在 PostHog 中由三个 CTE 组成exposures曝光、metric_events指标事件、entity_metrics实体指标。其中exposuresCTE 已经通过懒计算系统完成预计算见 LAZY_COMPUTATION.md而metric_eventsCTE 仍然在每次查询时直接扫描events表metric_events AS ( SELECT person_id AS entity_id, properties.$feature_flag_response AS variant, timestamp, uuid, properties.$session_id AS session_id, step_0, -- 1 if this is an exposure event, else 0 step_1, -- 1 if this matches funnel step 1, else 0 step_2 -- 1 if this matches funnel step 2, else 0 FROM events WHERE (exposure_predicate OR funnel_steps_filter) )这个 CTE 会扫描所有满足曝光条件或任意漏斗步骤例如pageview、purchase的事件。对于高流量实验这可能意味着数百万行数据且每次查询都要重复扫描——这正是性能瓶颈所在。解决方案一次扫描 预聚合表缓存解决方案的核心思路非常直接将 events 表只扫描一次把匹配的事件存储到experiment_metric_events_preaggregated表中后续查询直接从该表读取从而把每次全量扫描变成首次构建 缓存读取。存储结构每事件一行步骤标记打包进数组预聚合表为每个匹配事件存一行步骤指示器被打包进一个Array(UInt8)数组┌──────────┬──────────┬───────────┬─────────────────────┬───────────┬────────────┬───────────┐ │ team_id │ job_id │ entity_id │ timestamp │ event_uuid│ session_id │ steps │ ├──────────┼──────────┼───────────┼─────────────────────┼───────────┼────────────┼───────────┤ │ 123 │ job-A │ user-1 │ 2026-01-02 10:00:00 │ evt-111 │ sess-1 │ [1, 0, 0] │ ← exposure │ 123 │ job-A │ user-1 │ 2026-01-02 11:00:00 │ evt-222 │ sess-1 │ [0, 1, 0] │ ← pageview │ 123 │ job-A │ user-1 │ 2026-01-02 12:00:00 │ evt-333 │ sess-1 │ [0, 0, 1] │ ← purchase │ 123 │ job-A │ user-2 │ 2026-01-02 14:00:00 │ evt-444 │ sess-2 │ [1, 0, 0] │ ← exposure │ 123 │ job-A │ user-2 │ 2026-01-02 15:00:00 │ evt-555 │ sess-2 │ [0, 1, 0] │ ← pageview │ ... │ │ │ │ │ │ │ └──────────┴──────────┴───────────┴─────────────────────┴───────────┴────────────┴───────────┘steps [1, 0, 0]表示该事件匹配 step_0曝光但不匹配 step_1 或 step_2。数组的索引位置与漏斗步骤一一对应读路径用arrayElement(steps, N)取出每一位。数据流对比有无预计算的两条路径无预计算直接扫描┌────────────────────────────────┐ │ events table │ │ (millions of rows) │ └───────┬───────────────┬────────┘ │ │ │ scan for │ scan for pageview, │ exposures │ purchase, etc. │ │ ▼ ▼ exposures CTE metric_events CTE ◄── THIS IS THE EXPENSIVE PART │ │ └───────┬───────┘ │ LEFT JOIN ▼ entity_metrics CTE (aggregate_funnel_array UDF) │ ▼ Final result有预计算缓存读取FIRST QUERY (precomputes): events table ──scan──▶ experiment_metric_events_preaggregated (stores matching events with step indicators) SUBSEQUENT QUERIES (reads from cache): ┌─────────────────────────┐ ┌───────────────────────────────────┐ │ experiment_exposures │ │ experiment_metric_events │ │ _preaggregated │ │ _preaggregated │ │ (already cached) │ │ (newly cached) │ └──────────┬──────────────┘ └────────────────┬──────────────────┘ │ │ ▼ ▼ exposures CTE metric_events CTE │ │ └──────────┬─────────────────────────┘ │ LEFT JOIN ▼ entity_metrics CTE ← same UDF, same logic │ ▼ Final result ← identical output关键点在于下游的entity_metricsCTE、aggregate_funnel_arrayUDF 以及最终聚合逻辑完全不变读路径只是把metric_eventsCTE 的数据来源从events表换成了预聚合表因此两条路径产出的结果是一致的测试文件中专门有_precompute_and_compare辅助函数对两条路径的结果逐项断言相等见 test_experiment_funnel_metric_events_preaggregation.py。表结构源码experiment_metric_events_preaggregated预聚合表的 ClickHouse 定义位于 experiment_metric_events_sql.py从源码可以确认完整字段设计CREATE TABLE IF NOT EXISTS {table_name} ( team_id Int64, job_id UUID, -- Per-event data entity_id String, timestamp DateTime64(6, UTC), event_uuid UUID, session_id String, -- Mean/ratio metrics store the computed value here (default 0 for funnels) numeric_value Float64 DEFAULT 0, -- Funnel metrics store step indicators here (default empty for non-funnels) -- e.g. [1, 0, 1] means this event matches step_0 and step_2 steps Array(UInt8) DEFAULT [], -- When this row was computed (used as ReplacingMergeTree version) computed_at DateTime64(6, UTC) DEFAULT now(), -- TTL: rows are automatically deleted after expires_at expires_at Date DEFAULT today() INTERVAL 7 DAY ) ENGINE {engine}值得注意的实现细节引擎为ReplacingMergeTree(vercomputed_at)以computed_at作为版本字段同一行重复写入时保留最新版本使重试 INSERT 具备幂等性懒计算执行器的注释也明确说明quorum-wait 超时后的重试插入在 ReplacingMergeTree 下是幂等的。分区分页与 TTLPARTITION BY toYYYYMMDD(expires_at)按过期日期分区ORDER BY (team_id, job_id, entity_id, timestamp, event_uuid)保证同一 job 内按实体和时间有序TTL expires_at配合ttl_only_drop_parts 1实现整分区过期删除index_granularity8192与主表对齐。分布式与分片线上使用分布式表experiment_metric_events_preaggregated 分片表sharded_experiment_metric_events_preaggregated分片键为cityHash64(entity_id)部署在CLICKHOUSE_AUX_CLUSTER辅助集群上。HogQL 层的注册该表通过 experiment_metric_events_preaggregated.py 注册为 HogQL 可查询表并设置了load_balancingin_order其中steps字段在 HogQL 中没有类型化的数组字段注释明确说明需用arrayElement()访问各个步骤指示器。工作原理写路径与读路径写路径get_funnel_metric_events_query_for_precomputation()查询构建器会产出一个带{time_window_min}和{time_window_max}占位符的查询模板SELECT person_id AS entity_id, timestamp AS timestamp, uuid AS event_uuid, $session_id AS session_id, [toUInt8(if(exposure_pred, 1, 0)), toUInt8(if(step_1_pred, 1, 0)), toUInt8(if(step_2_pred, 1, 0))] AS steps FROM events WHERE timestamp {time_window_min} AND timestamp {time_window_max} AND (exposure_predicate OR funnel_steps_filter)随后由懒计算系统ensure_precomputed()核心实现位于 lazy_computation_executor.py将实验日期范围按天拆分为多个时间窗口填充时间占位符并把 SELECT 包进 INSERTINSERT INTO experiment_metric_events_preaggregated (team_id, job_id, entity_id, timestamp, event_uuid, session_id, steps, expires_at) SELECT 123 AS team_id, job-uuid AS job_id, ... -- the SELECT from above每个日窗口对应一个独立 job已经计算过的窗口会被跳过因此数据是按天增量构建的后续查询只需补算缺失窗口。构建器侧的分派逻辑在 experiment_query_builder.py 的get_metric_events_query_for_precomputation()按指标类型分派到漏斗/均值/留存的专属写查询。读路径_build_funnel_query_legacy()当metric_events_preaggregation_job_ids被设置时metric_eventsCTE 改为从预计算表读取而不再扫描 eventsmetric_events AS ( SELECT toUUID(t.entity_id) AS entity_id, t.timestamp AS timestamp, t.event_uuid AS uuid, t.session_id AS session_id, arrayElement(t.steps, 1) AS step_0, arrayElement(t.steps, 2) AS step_1, arrayElement(t.steps, 3) AS step_2 FROM experiment_metric_events_preaggregated AS t WHERE t.job_id IN (job-A, job-B) AND t.team_id 123 )arrayElement(t.steps, N)从打包数组中提取各个步骤指示器。从源码结构看该方法被称为_build_funnel_query_legacy是因为它早于单次扫描的优化路径存在但它恰恰是预计算查询的主路径——曝光与指标事件两个 CTE 都可以在这里读取预计算表。查询的其余部分entity_metricsCTE、aggregate_funnel_arrayUDF、最终聚合完全不变。编排_get_experiment_query()运行器runner在 experiment_query_runner.py 中统筹两次预计算if should_precompute and not is_data_warehouse_query: # 1. Precompute exposures (already existed) result self._ensure_exposures_precomputed(builder) if result.ready: builder.preaggregation_job_ids result.job_ids # 2. Precompute metric events (new — ordered funnels only) if is_ordered_funnel: result self._ensure_metric_events_precomputed(builder) if result.ready: builder.metric_events_preaggregation_job_ids result.job_ids两次预计算复用同一套懒计算系统日窗口、job 管理、TTL。实际源码中_get_experiment_query()对应行约 L550-L644的逻辑更细先执行_should_precompute()判断开关再依次调用_ensure_exposures_precomputed()与_ensure_metric_events_precomputed()且数据仓库data warehouse指标与按组聚合group-aggregated实验会被整体跳过预计算——前者因为预计算表缺少关联数据仓库表所需的 join key后者因为构建 INSERT 中的$group_N在INSERT ... SELECT分析路径上无法解析sharded_events上是物化列会报UNKNOWN_IDENTIFIER错误。转换窗口指标事件可以发生在实验结束之后漏斗步骤可能发生在实验结束日期之后在转换窗口内。例如实验 1 月 15 日结束转换窗口为 7 天那么 1 月 20 日发生的购买仍然计入结果。因此运行器在预计算指标事件时会把time_range_end向后扩展一个转换窗口date_to experiment.end_date timedelta(secondsconversion_window_seconds)具体实现位于_ensure_metric_events_precomputed()date_to先取experiment_window_end(experiment, as_of)再通过builder.get_metric_events_window_extension_seconds()取得扩展秒数并追加。扩展秒数的计算规则experiment_query_builder.py 的get_metric_events_window_extension_seconds()漏斗/均值指标取转换窗口留存指标在此基础上还要叠加留存窗口详见下文留存小节。而曝光预计算不需要这个扩展——曝光只会发生在实验日期范围内。与曝光预计算的关键区别ExposuresMetric eventsGranularity1 row per user per job1 row per event per jobRe-aggregation on readYes —argMin/min/maxacross jobsNo — events are unique per daily windowTableexperiment_exposures_preaggregatedexperiment_metric_events_preaggregatedTime rangeExperiment start → endExperiment start → end conversion windowStores variantYesNo — variant comes from exposures CTE最值得留意的是第二行指标事件在读取时不需要跨 job 重新聚合。每个事件在日窗口内是唯一的事件本身带uuid所以读路径直接用WHERE t.job_id IN (...)把多个 job 的事件合并即可而曝光数据是每用户每 job 一行需要在读取时用argMin/min/max跨 job 归并。另外指标事件表不存 variant——实验变体归属由 exposures CTE 提供避免冗余存储。适用范围与回退策略从 experiment_query_runner.py 的_metric_events_precompute_applicable()约 L484-L537可以确认完整的适用矩阵支持预计算有序漏斗ordered funnelscount/sum/avg/min/max 数值型均值指标——构建查询存储的每事件数值与数学类型无关数学运算在读取时由build_value_aggregation_expr统一应用因此五种数学类型存的是完全相同的行dau/unique_session 均值指标——读取时对entity_id/session_id做 distinct 计数而每个均值构建都会存这两个字段numeric_value存的是与 count 指标相同的常量所以同源的 count 指标与 ID 数学指标可以共享构建 job留存指标retention——受默认关闭的特性开关控制。不支持 / 回退到直接扫描无序漏斗、unique-group 数学、HogQL 数学、比率指标ratio带 breakdowns、启用 CUPED 或数据仓库数据源的查询永远回退到直接扫描。CUPED 需要把指标扫描回溯lookback_days来获取曝光前协变量而预计算的指标事件表只覆盖实验窗口因此直接跳过预计算让构建器发起一次全新扫描。另外_should_precompute()约 L416-L437还包含一组前置门控查询级precomputation_mode显式覆盖PRECOMPUTED/DIRECT 团队级experiment_precomputation_enabled配置 最小运行时长门槛MIN_PRECOMPUTATION_DURATION_SECONDS12 小时 激活模式曝光flag→激活的时序跨日边界无法按天缓存 未完成物化的 cohort动态 cohort 无完成版本、静态 cohort 正在填充时若预计算会冻结撕裂快照长达 60 天。这些门控在_precompute_skip_reason()中都有对应的PrecomputeSkipReason供查询性能 UI 展示跳过原因。留存指标的特殊处理留存指标预计算实现见 experiment_retention_query_builder.py 的get_retention_metric_events_query_for_precomputation()存储每个匹配 start_event 或 completion_event 谓词的事件各一行steps中恰好两个标志位steps[1] 匹配 start_eventsteps[2] 匹配 completion_event一个事件可以同时匹配两者。读路径把两个原始事件 CTE 数据源替换为对预计算表的标志位过滤读取而 start 锚定FIRST_SEEN/LAST_SEEN、每用户留存窗口、成熟度门槛maturity gate、同事件排除等逻辑全部保留在读取时执行因此两条路径行为一致。留存有两点独特设计扫描扩展范围更大conversion_window retention_window_end而非仅转换窗口——因为 completion 事件最多可以在最后一个曝光之后conversion_window retention_window_end才落地独立开关受默认关闭的experiments-retention-metric-events-preaggregation特性开关控制fail-safe 设计开关缺失或求值失败一律走直接扫描从而可以在不影响漏斗/均值预计算的前提下单独关闭留存预计算。实现见_retention_metric_events_precomputation_enabled()约 L462-L482且METRIC_EVENTS_MAX_WINDOW_EXTENSION_SECONDS90 天会封顶扫描扩展范围防止用户提交的超大留存窗口把预计算地平线拉出数千个日 job。懒计算执行器与运行参数整个预计算构建由 lazy_computation_executor.py 的ensure_precomputed()驱动实验中使用的运行参数可以在 experiment_query_runner.py 顶部常量中看到TTL 调度DEFAULT_EXPOSURE_TTL_SECONDS分档——0 天窗口 15 分钟、1 天窗口 1 小时、2-4 天窗口 18 小时略短于约 24 小时的预热节奏让迟到的曝光事件在窗口冻结前被每日 warmer 吸收、默认 60 天数据冻结。TTL 过期后 ClickHouse 自动删除行expires_at默认today() 7 DAY但实验中按调度覆盖。Job 宽度上限PRECOMPUTE_MAX_WINDOW_DAYS 7。若不加限制冻结带4 天以上统一 TTL 的区域会合并成单个 INSERT冷启动或 TTL 过期的长周期实验会在一条查询里扫描数百天对高流量团队必然超出资耗限制或 600 秒超时。7 天宽度在控制每查询扫描量的同时比逐日分块产生更少的 job也更少的读时重聚合行数且已完成块持久保留宽回填可跨运行收敛而非每次原子失败。TTL 抖动PRECOMPUTE_TTL_JITTER_SECONDS 14 天分散冻结块的过期时间避免一个实验的历史数据同时过期。执行保护MAX_EXECUTION_TIME 600秒、MAX_BYTES_BEFORE_EXTERNAL_GROUP_BY 37 GB构建 INSERT 设置spill_to_diskTrue以在高基数场景把 GROUP BY 溢写到磁盘而非 OOM生产环境 INSERT 使用insert_quorumauto保证副本确认后才返回配合 2 秒 quorum 超时快速失败重试在 ReplacingMergeTree 下幂等调用方同时回退到实时查询。测试侧test_experiment_funnel_metric_events_preaggregation.py 提供了强验证_precompute_and_compare让同一实验分别走直接扫描与预计算两条路径并断言number_of_samples、sum等统计量逐项相等如二步漏斗test_basic_two_step_funnel、三步漏斗test_three_step_funnel、带转换窗口的test_funnel_with_conversion_window还有测试验证experiment_date_to哨兵占位符让运行中实验的 job 哈希在as_of前移时保持稳定test_metric_events_precomputation_hash_ignores_moving_experiment_end以及已停止实验在as_of变化时完全复用同一批 jobtest_metric_events_precomputation_for_stopped_experiment_uses_end_date。关键文件速查文件职责experiment_query_builder.py写路径get_funnel_metric_events_query_for_precomputation()/ 分派入口get_metric_events_query_for_precomputation()读路径预计算 CTE 在_build_funnel_query_legacy()窗口扩展get_metric_events_window_extension_seconds()experiment_query_runner.py_ensure_metric_events_precomputed()、_get_experiment_query()编排、_metric_events_precompute_applicable()适用性判断、TTL/窗口宽度/门控常量experiment_retention_query_builder.py留存指标写查询与conversion retention_window_end扩展秒数lazy_computation_executor.py懒计算核心ensure_precomputed()、日窗口 job 管理、TTL 调度、INSERT quorum 与幂等性experiment_metric_events_sql.pyClickHouse 表定义ReplacingMergeTree、分区、TTL、分布式/分片experiment_metric_events_preaggregated.pyHogQL schema 注册test_experiment_funnel_metric_events_preaggregation.py双路径结果一致性、job 哈希稳定性、转换窗口测试【免费下载链接】posthog: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.项目地址: https://gitcode.com/GitHub_Trending/po/posthog创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考