PostHog ClickHouse 物化分析实战：从慢查询 JSONExtract 到建列/删列的完整方法论

PostHog ClickHouse 物化分析实战：从慢查询 JSONExtract 到建列/删列的完整方法论

【免费下载链接】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 仓库中.agents/skills/generating-clickhouse-query-performance-reports技能集的references/materialization-analysis.md编写，是该技能「慢查询性能报告」方法论的核心延伸。文章面向负责 PostHog 生产 ClickHouse 集群（US / EU）容量与查询性能的工程师，讲解如何系统性地找出「值得物化的 JSON 属性」（消除昂贵的JSONExtract全扫描）与「值得删除的物化列」（回收磁盘），并给出完整可运行的 SQL、Dagster 作业配置建议与最终报告格式。读完本文，你将掌握一条从候选识别、交叉核对、风险评估到人工执行落地的闭环工作流。

适用范围与前置约定

物化（materialization）分析是 PostHog 慢查询治理中收益最直接的手段之一：用户的属性存放在事件 JSON blob（properties、person_properties、group_properties）里，若每次都靠JSONExtract在查询期解析，ClickHouse 会全量扫描整列 JSON，导致 read_bytes 巨大、查询缓慢甚至 OOM。把高频属性拆成独立的物化列（前缀通常形如mat_*）后，查询即可走普通列扫描与索引。

本分析有几条硬性前置约定，务必遵守：

• US 与 EU 各跑一遍。两地是独立集群，物化列集合与工作负载不同，候选列表必然不同，不能以一方代替另一方。
• 所有查询经由query-clickhouse-via-metabase技能执行（底层为hogli metabase:query --region <us|eu> --database-id <id>，数据库 ID 用hogli metabase:databases发现，并非固定值）。
• 结果写入私有仓库PostHog/query-performance-analysis的analysis/<date>-materialization-candidates.md，不写入本公开仓库。若该兄弟目录不存在，则写入临时目录并告知用户。
• 宽列列表查询要分批：一次只检查约 5–6 个列，避免超出 Metabase 响应截断上限。
• 数据源统一使用posthog.query_log_archive（归档表保留约三周，system.query_log只保留数小时，无法支撑多日分析），且始终过滤is_initial_query以免分布式子查询被重复计数。

分析的整体位置见技能主文档 SKILL.md，报告中各步骤可直接运行的 SQL 模式见 query-patterns.md（其中 §7 提供了 JSONExtract 属性拆解查询）。

Step 1：确定「值得物化」的候选属性

候选列表的来源是query-patterns.md§7 的JSON 提取属性拆解查询：统计慢查询中从 JSON blob 中抽取的属性名，按column（事件properties、person_properties、group_properties）切分，并附上驱动这些查询的团队。物化分析的窗口要放宽到约 30 天——一个属性若被持续命中才值得物化，单日的偶发热点不足以作为依据。

对候选做两层排序：

• 按slow_queries（命中该属性的慢查询次数）排序；
• 按teams（涉及的团队数）排序。

被多个团队的大量慢查询同时命中的属性是最强候选。column字段决定了属性位于哪张表的哪个 JSON 列上，这正是后续 Dagster 作业需要的三元组输入：(table, table_column, property)。

反查候选属性所属的表与列

当需要针对某个具体候选属性恢复其表别名与 JSON 列时，使用下述归档查询（注意query_log_archive中保存的是执行过的 SQL 文本，用extractAll抓取模式）：

SELECT arrayDistinct(extractAll(query, '(\\w+)\\.\\w*properties,\\s*''<PROPERTY>''')) AS table_aliases, arrayDistinct(extractAll(query, '\\w+\\.(\\w*properties),\\s*''<PROPERTY>''')) AS columns FROM posthog.query_log_archive WHERE event_time > now() - INTERVAL 7 DAY AND query LIKE '%<PROPERTY>%' LIMIT 100

把<PROPERTY>替换为候选属性名即可。查询结果给出诸如e.properties中的别名与properties/person_properties/group_properties的实际列名。

补充：仓库内自动化分析器的工作方式

虽然技能文档强调手工、谨慎的分析流程，但仓库中其实也存在一个可自动化的分析器，理解它有助你核对候选列表的合理性：analyze.py 中的_analyze()直接扫描clusterAllReplicas({cluster}, system, query_log)，用extractAll(query, 'JSONExtract[a-zA-Z0-9]*?\\((?:...)?(.*?), .*?\\)')抽取被JSONExtract引用的列，再配合exception_code IN (159,160)、read_bytes > 20e9、read_rows > 5000000、query_duration_ms等过滤条件，最后HAVING要求「超时/过慢命中数 > 0 或慢查询数 > 9」才视为候选，并LIMIT 100防止单轮加列过多。它会刻意排除personal_api_key调用与 celery 后台任务（理由见代码注释：API 请求失败比界面查询失败代价小）。materialize_properties_task()随后与已有物化列集合做差集并回填。这可以视为本文所述人工方法的自动化同源实现。

Step 2：核对「已物化」清单，区分建列与绕过

对每个候选属性，先确认它是否已经有物化列：

SHOW CREATE TABLE sharded_events

将输出与 Step 1 的候选列表交叉核对，即可把候选分成两类：

1. 确实需要物化：尚无对应物化列；
2. 在绕过已有物化列：物化列存在，但查询仍然走JSONExtract。

绕过（bypass）的机理

绕过通常意味着属性以用户手写 HogQL的方式访问：例如JSONExtractString(properties, '$foo')。从源码结构看，这种写法在 HogQL 中会生成一个ast.Call节点，从而跳过visit_property_type()的属性改写逻辑；而写成properties.$foo的字段访问形式才会被改写为物化列引用。相关逻辑见 property_types.py 中的build_property_swapper/PropertySwapper（其_JSON_EXTRACT_SCALAR_CASTS表还展示了JSONExtractString→String、JSONExtractInt→Int64等标量类型映射）。

文档列举的已知绕过来源包括：

• HogQL / DataVisualization 节点（任意用户 SQL 与可视化查询）；
• 部分 survey SQL。

识别出绕过案例后，结论不应是「加物化列」，而应是「把这些查询改成properties.$foo写法」或推动查询发起方修正。

补充：物化列注册表的实现事实

columns.py 维护了物化列的运行时注册表：

• get_materialized_columns(table)从system.columns读取并缓存（缓存 key 为materialized_columns:v3:<table>）；
• MaterializedColumn数据类记录列名、类型、可空性及minmax_/bloom_filter_/ngram_bf_lower_等跳数索引标记；
• 其表达式生成方法返回JSONExtract(table_column, property, type)或JSONExtractRaw(...)——即物化列的取值正是把JSONExtract下沉到写入期；
• 顶层同时维护SHORT_TABLE_COLUMN_NAME映射：properties→p、group_properties→gp、person_properties→pp、group0~4_properties→gp0~gp4。物化列名即以此为前缀（形如mat_*），这就是为何本文各查询都围绕mat_%与mat_<col>模式编写。

Step 3：找出「可以安全删除」的物化列

删除物化列的唯一安全判据是：30 天内零查询引用 且 零数据。用法检查必须覆盖全部查询（含非慢查询），而不仅是慢查询子集。

3.1 用法检查（按列做定向 countIf，避免超时）

-- usage: does any query reference the column? (targeted countIf per column to avoid timeouts) SELECT countIf(query LIKE '%mat_<col>%') AS uses FROM posthog.query_log_archive WHERE event_time > now() - INTERVAL 30 DAY AND is_initial_query

把<col>逐个替换为候选列名执行；不做全列正则扫描而做定向LIKE，是为了避免对归档大表做高代价的逐行正则匹配导致超时。

3.2 磁盘占用（对 system 表使用 clusterAllReplicas）

-- disk size SELECT name, formatReadableSize(sum(data_compressed_bytes)) AS compressed FROM clusterAllReplicas(posthog, system, columns) WHERE table = 'sharded_events' AND name LIKE 'mat_%' GROUP BY name ORDER BY sum(data_compressed_bytes) DESC

该查询只用于非 Distributed 的 system 表（此处是system.columns），因此可以用clusterAllReplicas汇总所有副本上的元数据。

3.3 数据存在性检查（必须读 Distributed 表，且分批）

-- data presence before dropping (batch the column list) SELECT countIf(mat_<col> != '') AS nonempty_<col>, uniqExactIf(team_id, mat_<col> != '') AS teams_<col> FROM events WHERE timestamp > now() - INTERVAL 7 DAY

⚠️ 数据存在性必须从 Distributed 的events表读取，而不是对本地sharded_events用clusterAllReplicas(...)：

• Distributed 表会向每个分片的一个副本扇出，因此每行只被计数一次；
• clusterAllReplicas会命中每个副本，使计数被乘以副本因子而虚高。

3.4 风险分级

结合 3.1–3.3 的结果：

用法（30 天）	数据（7 天窗口内）	结论
零查询	零数据	安全删除，加入 drop 候选主表
零查询	有活跃数据	有风险的可选项。删除本身不会丢数据（原始值仍在 JSON blob 中），但日后若想再物化，需要昂贵的回填（backfill）。应记录为「可选删除」，附带大小与团队信息供决策

关于「有数据但零查询」要提醒一句：7 天窗口内非空只能说明该列仍被写入新事件；真正判断风险要在报告中写明其压缩大小（回填代价的量级）与来源团队，把决策权交给集群负责人。

Step 4：Dagster 作业——交给人工执行

这两类作业必须由具备 Dagster 权限的人工操作员运行，分析者自身不能代为执行。运行前，操作员应与#team-clickhouse确认集群不处于异常状态——建列回填与删列都会增加负载，绝不能落在集群本就吃紧的时间点。给操作员的配置应作为建议而非代为执行。

仓库中两个作业的实现可作为配置参数的事实依据：

• 建列作业 create_materialized_column.py：MaterializeColumnConfig含table（默认events，可选person）、table_column（默认properties，可选group_properties/person_properties）、properties: list[str]、backfill_period_days（默认 90）、dry_run（默认False）与is_nullable（默认True）。作业内部调用ee.clickhouse.materialized_columns.analyze.materialize_properties_task，先dry_run校验再逐属性materialize(...)并按backfill_period_days回填。
• 删列作业 drop_materialized_column.py：DropMaterializedColumnConfig含table、column_names: list[str]，且dry_run默认为True（影响最小）。内部调用ee.clickhouse.materialized_columns.columns.drop_column。

给操作员的建议要点：

• Create：使用create_materialized_column（team-clickhouse location，按 region 分别运行）。由于回填会给集群增加负载，安排在周末执行。为每个新物化列准备一行(table, table_column, property)三元组配置。
• Drop：使用drop_materialized_column，保持其dry_run: true默认值，先打印将删除的列清单供确认，再在复核后关闭 dry_run 真正执行。

可选删除项的处理

「零查询但有活跃数据」的可选删除项，不要直接执行，而是写进同一份 Dagster 配置里，作为注释掉的条目并附带大小与团队信息——这样既不丢数据，也把信息留在将来可执行的位置。

报告格式

最终产物（写入query-performance-analysis的analysis/<date>-materialization-candidates.md）的结构顺序为：

1. 四张推荐表（先表后细节）：
   • 新建物化建议表（US-new、EU-new 分开）；
   • 删除候选表（US-drop、EU-drop 分开）。
2. Dagster 配置建议。
3. 调查过程细节。

推荐表字段如下。

新建物化建议：

Table	Column	Property	Slow queries (30d)	Teams	Timeouts	Avg read	Max read	Est. column size

删除候选：

Column	Compressed	Non-empty events	Teams	Safe to drop?

要点回顾：

• Column告诉 Dagster 作业属性落在哪个 blob（对应table_column）；
• 选列时以Slow queries与Teams为优先级核心；Timeouts/Avg read/Max read用于量化收益，Est. column size用于量化存储成本；
• 可选删除（无查询但有数据）进入 dagster 配置的注释条目，附Compressed与Teams信息。

结合源码的纵深建议

若要追查候选属性背后的查询链路，可继续在仓库中阅读：

• 慢查询报告与 SQL 模式母版：query-patterns.md（§7 提供 JSONExtract 属性拆解的完整 SQL 与按属性钻取每个使用团队的下钻查询）；
• HogQL 任意用户/AI SQL 的专项分析方法：hogql-deep-dive.md；
• 物化列注册表与类型推断：columns.py（含列名短前缀映射、索引类型minmax_/bloom_filter_/ngram_bf_lower_、缓存 key）；
• 自动化分析器：analyze.py（_analyze+materialize_properties_task，含回填参数语义）；
• 两个 Dagster 作业：create_materialized_column.py 与 drop_materialized_column.py；
• HogQL 属性改写与物化列替换逻辑：property_types.py（理解properties.$foo与JSONExtractString(properties, '$foo')走向不同分支的根因）。

小结：一次物化分析的执行清单

1. US、EU 分别执行query-patterns.md§7 的 JSON 属性拆解查询，窗口放宽至 30 天，得到(column, property, slow_queries, teams)候选；
2. 用SHOW CREATE TABLE sharded_events交叉核对已物化列，区分「待物化」与「绕过」两类；
3. 对每个「待物化」候选反查table/table_column/property三元组；
4. 对存量mat_%列执行三层检查：30 天用法（定向countIf）、压缩大小（system.columns+clusterAllReplicas）、数据存在性（Distributedevents表，分批）；
5. 按「零查询 + 零数据 → 安全删除」「零查询 + 有数据 → 注释化的可选删除」分级；
6. 向操作员提交四张推荐表 + Dagster 配置建议，Create 安排在周末、Drop 保持dry_run: true复核后执行；
7. 结果写入analysis/<date>-materialization-candidates.md。

【免费下载链接】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