Search CTRL + K

ClickHouse 表 TTL

目前在 ClickHouse 中只有 MergeTree 表支持 TTL。最开始 TTL 只是用于自动删除数据,后续添加了重压缩、移动到另一个磁盘、聚合等能力,甚至添加了列 TTL。[1]

需要注意的是,删除、聚合的 TTL 并不会实时触发,而是在合并 parts 时触发。对于不会主动合并的表(比如没有插入),也有触发方案:

也就是说默认配置下,每 4 小时会触发一次 TTL 删除/聚合。

修改 TTL

当执行 ALTER TABLE tbl MODIFY (ADD) TTL 时,ClickHouse 会执行两步:

  1. 更新表元信息:更新 schema .sql、更新 zookeeper 中的元信息。这一步通常很轻量、快。更新后新插入的数据会按照新的 TTL 规则来计算。
  2. 重新计算所有现有 parts:读取所有列数据,重新计算 TTL,执行 TTL 规则。这一步通常很重。

由于第二步比较重,对于较大的表执行 TTL 变更时有可能会在 clickhouse client 处收到超时提示,但 ClickHouse 实际会在后台执行改 mutation,可以查询 system.mutations 表查看变更进度。

可以修改用户会话配置来临时禁止第二步:SET materialize_ttl_after_modify=0;。这种情况下,现有数据仍然会按照旧 TTL 执行逻辑。

在第 2 步中,ClickHouse 分为两步去执行:

同样,可以通过修改 MergeTree 表配置来禁止 2.2 步:ALTER TABLE tbl MODIFY SETTING materialize_ttl_recalculate_only=1。这种情况下,只有在合并 parts 时会执行 TTL 更新。

Manage Data with TTL (Time-to-live)

TTL (time-to-live) refers to the capability of having rows or columns moved, deleted, or rolled up after a certain interval of time has passed. While the expression "time-to-live" sounds like it only applies to deleting old data, TTL has several use cases:[2]

  • Removing old data: no surprise, you can delete rows or columns after a specified time interval
  • Moving data between disks: after a certain amount of time, you can move data between storage volumes - useful for deploying a hot/warm/cold architecture
  • Data rollup: rollup your older data into various useful aggregations and computations before deleting it
TTL Expressions

The MergeTree is currently the only family of engines that support TTL expressions.

A TTL expression is simply an SQL expression that must evaluate to Date or DateTime data type.[3]


  1. https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl ↩︎

  2. https://clickhouse.com/docs/en/guides/developer/ttl ↩︎

  3. https://altinity.com/blog/2020-3-23-putting-things-where-they-belong-using-new-ttl-moves ↩︎