PostHog 中的 ClickHouse Materialized Columns 实战指南：从自动物化到手工运维

PostHog 中的 ClickHouse Materialized Columns 实战指南：从自动物化到手工运维

【免费下载链接】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 仓库中的 materialized-columns.md 手册为核心，系统讲解 ClickHouse 物化列（Materialized Columns）的核心原理、自动物化机制、基于 Dagster 的手动物化流程与配置参数，并结合仓库源码剖析其底层实现。读完本文，你将掌握：物化列为什么能让 JSON 属性查询提速（手册给出的典型数据为最高 25x）、PostHog 如何自动分析慢查询并物化属性列、如何在生产环境通过 Dagster 手工创建物化列并安全回填历史数据，以及如何用环境变量控制整个物化流程。

背景：为什么需要物化列

PostHog 的核心事件数据以 JSON 形式存储在 ClickHouse 的字符串列中。事件属性、用户属性、群组属性等都被塞进properties、person_properties、group_properties这类"胖"JSON 列中，查询时依赖JSONExtract*系列函数在读取阶段实时解析 JSON。由于这些列体积巨大，解析成本高，导致查询缓慢。

物化列（Materialized Columns）的思路是：把 JSON 中高频使用的特定属性在写入/变更时提前抽取出来，落盘为独立的物理列。这样查询时直接读取扁平列，无需再对整块 JSON 做解析，读取速度可提升一个数量级——手册明确给出"reading these columns up to 25x faster than normal properties"的实测结论。

从源码可以印证这一设计：columns.py 中的MaterializedColumn.get_expression_and_parameters()展示了物化列默认表达式的两种形态：

• 非 nullable 场景：JSONExtractRaw(properties, %(property)s)，抽取 JSON 中属性的原始值；
• nullable / 显式指定类型场景：JSONExtract(properties, %(property_name)s, %(property_type)s)，按目标类型（如Nullable(String)）强类型抽取。

这两个表达式最终被拼进ADD COLUMN ... DEFAULT <expression>，即列默认值即 JSON 抽取表达式——这正是"物化"的本质：列数据由表达式生成并持久化到磁盘。

手册中附带的 ClickHouse JSON 使用手册 与相关博客是进一步阅读的入口；本文聚焦物化列的机制与运维。

物化列在实践中的两大路径

物化列在 PostHog 中承担着为大数据量客户优化查询性能的重任。它存在两条使用路径：

1. 自动物化：一个定时任务自动分析上周的慢查询，从中识别出高频属性并自动物化；
2. 手动物化：通过 Dagster 作业按需创建物化列（生产环境即以此为主）。

无论哪条路径，都有一个前提——物化列必须回填（backfill）历史数据才能生效。回填意味着对集群上大量历史分区执行数据重写，会显著增加集群负载，因此手册强调这类操作最好安排在周末执行。

自动物化：慢查询驱动的 cron 任务

自动物化的代码位于 ee/clickhouse/materialized_columns/analyze.py。核心入口是materialize_properties_task()，它大致分三步：

1. 分析慢查询：调用_analyze(since_hours_ago, min_query_time, team_id)，从 ClickHousesystem.query_log中检索最近一周（默认）的查询，用正则抽取 SQL 中的JSONExtract*调用，定位"哪个表、哪一列、哪个属性"被频繁读取；
2. 去重过滤：通过get_materialized_columns(table)拿到已物化的列，跳过已经存在的属性，避免重复物化；
3. 物化与回填：对筛选出的候选属性（默认最多 100 个）调用materialize()建列，随后调用backfill_materialized_columns()回填指定天数的历史数据。

_analyze的过滤条件非常讲究，体现了生产环境的工程取舍（见 analyze.py）：

• 只统计失败/超时的查询：异常码159（TIMEOUT EXCEEDED）与160（TOO SLOW），或query_duration_ms超过阈值；
• 只统计"重活"：read_bytes > 20GB且read_rows > 5,000,000，保证物化只针对真正昂贵的大扫描；
• 排除person_distinct_id2旧式关联查询、排除personal_api_key与 celery 内部查询；
• 只关注properties、person_properties、group0~4_properties这几类 JSON 属性列；
• 每个候选需满足"超时失败至少 1 次或慢查询至少 10 次"才进入物化候选，并用LIMIT 100限制单轮物化列数，防止一次性加几百列把集群压垮。

自动物化的调度与环境变量

自动物化由 celery 定时任务驱动，posthog/tasks/tasks.py 中在条件满足时调用materialize_properties_task()。相关调度与阈值全部可通过环境变量调整，定义见 ee/settings.py：

另有两个与物化系统运行相关的开关位于 posthog/settings/dynamic_settings.py：

• MATERIALIZED_COLUMNS_ENABLED（默认True）：物化列整体功能开关；
• COMPUTE_MATERIALIZED_COLUMNS_ENABLED（默认True）：是否计算（使用）物化列。

手册特别提醒：由于集群问题或正在进行数据迁移，这个 cron 经常会被临时禁用——运维时应注意检查上述开关与调度状态，避免在迁移期间触发大规模物化。

手动物化：Dagster 作业实战

在生产环境中，PostHog 使用Dagster手工执行物化。作业名为create_materialized_column，位于team-clickhouselocation 中（EU 与 US 两个 region 各有实例）。作业定义见 posthog/dags/create_materialized_column.py，其配置类MaterializeColumnConfig直接对应手册中的 YAML 配置。

进入 Dagster playground（对应 region）后，配置create_materialized_columns_op即可，手册给出的完整示例：

ops: create_materialized_columns_op: config: backfill_period_days: 90 dry_run: false properties: - $browser_language_prefix - $app_namespace table: events table_column: properties

配置项详解

对照 create_materialized_column.py 中的配置类，各字段含义与约束如下：

• table：要物化的 ClickHouse 表，可选值为events或person，默认events；
• table_column：包含属性的 JSON 列，可选值为properties、group_properties、person_properties，默认properties（对应源码中DEFAULT_TABLE_COLUMN = "properties"）；
• properties：需要物化为列的属性名列表（必填），如$browser_language_prefix、$app_namespace；
• backfill_period_days：回填多少天的历史数据，默认90；
• dry_run：置为true时只预览将物化哪些列、不做任何变更，默认false；
• is_nullable：新建物化列是否为 nullable，默认true（源码中is_nullable: bool = True，自动物化任务默认False）。

其中dry_run对应 analyze.py 中的if not dry_run:分支：dry-run 模式下只记录日志、跳过materialize()与回填。Dagster op 也会显式输出"Dry run: No changes to the tables will be made!"警告日志，适合在正式操作前先预览。

执行流程：从 op 到底层 DDL

当 YAML 配置提交后，create_materialized_columns_op会把(table, table_column, property)三元组打包传给materialize_properties_task()，随后走通如下链路：

1. materialize()（columns.py）：
   • 校验属性是否已物化（重复会抛ValueError），校验table_column是否合法；
   • 生成唯一列名：person表前缀pmat_，events表前缀mat_，若table_column非默认还会插入短码（p/pp/gp/gp0~gp4，见SHORT_TABLE_COLUMN_NAME），属性名中的非法字符替换为_，冲突时追加随机短后缀；
   • 在数据节点上执行ALTER TABLE ... ADD COLUMN IF NOT EXISTS <col> <type> DEFAULT <JSONExtract 表达式>，并附带 COMMENT（格式为column_materializer::<table_column>::<property>[::disabled]，这是系统识别物化列的依据）；
   • 若为分片表（events），还会在所有查询节点上为分布式表补建同名空列；
   • 默认同时创建minmax跳数索引（create_minmax_index=not TEST），并可选创建bloom_filter、ngram_bf_v1(lower(col))、bloom_filter(lower(col))等索引（后两者因 ClickHouse 索引大小写敏感、不支持 nullable，需用lower(coalesce(col,''))包装，详见NgramLowerIndex/BloomFilterLowerIndex）。
2. backfill_materialized_columns()（columns.py）：
   • 对数据表执行ALTER TABLE ... UPDATE <col> = <col> WHERE timestamp > <cutoff>的轻量变更（mutation），由 ClickHouse 在后台异步重写分区数据；
   • 回填窗口由backfill_period_days换算为截止日期传入；对events表按天数截断，person表则全表回填（源码中if table == "events"的注释标记了这一点）；
   • 该方法注释明确写道 "This will require reading and writing a lot of data on clickhouse disk"，再次印证回填对磁盘 IO 的巨大压力。
3. 缓存失效：变更完成后调用_clear_materialized_columns_cache()清除物化列元数据缓存，确保后续查询立即感知新列。

物化列的查询侧机制

查询时，PostHog 的查询引擎会先通过get_materialized_columns(table)/get_enabled_materialized_columns()（columns.py，15 分钟缓存 + 后台刷新）读取物化列清单，将JSONExtract表达式改写为直接读列。物化列的元数据来自system.columns，靠 COMMENT 中的column_materializer::标记识别（见MaterializedColumn._get_all的 SQL）。is_disabled机制允许在不删列的情况下临时停用某列（通过update_column_is_disabled()改写 COMMENT 追加disabled标记），而drop_column()则负责彻底删除列及其关联索引（先删索引再删列，分布式表与数据表分别处理）。

运维建议与注意事项

综合手册与源码，生产环境操作物化列时需注意：

1. 回填是重操作，务必避开高峰：backfill_period_days: 90意味着对 90 天的历史数据执行 mutation 重写，集群 IO 压力巨大，手册建议安排在周末；
2. 先 dry_run 再执行：任何手动物化前先将dry_run: true跑一遍，确认要创建的列与回填窗口符合预期；
3. 用MATERIALIZE_COLUMNS_*环境变量控制自动物化：大数据量集群若担心自动物化失控，可调低MATERIALIZE_COLUMNS_MAX_AT_ONCE、关闭MATERIALIZED_COLUMNS_ENABLED，或在数据迁移期间直接禁用该 cron；
4. 列名与索引自动管理：列命名、minmax 索引创建、缓存清理均由materialize()一站式完成，运维无需手工拼 DDL；如需停用列而非删除，优先使用update_column_is_disabled()，保留数据以降低重建成本；
5. 测试覆盖完整：仓库在 ee/clickhouse/materialized_columns/test/ 下提供了test_columns.py、test_analyze.py、test_query.py等测试，覆盖列创建、分析逻辑与查询改写，可作为理解各函数行为的参考。

结语

物化列是 PostHog 在"JSON 属性查询慢"这一典型 ClickHouse 痛点上的工程解法：以磁盘空间和回填 IO 为代价，换取查询时免解析的极速读取。理解自动物化的慢查询分析逻辑、Dagster 手动物化的配置语义，以及底层ADD COLUMN ... DEFAULT JSONExtract(...)+ mutation 回填的实现链路，就能在大数据量场景下安全、高效地运用这套工具，让慢查询分析物化流程真正服务于性能优化。

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