ClickHouse Tiered-Storage TTL and Backfill

Important: If you’re using or considering the enterprise version of Opik or Comet, reach out to Sales@comet.com to access the enterprise deployment documentation.

With the cold tier enabled, the Opik Helm chart can move old trace data from the local (hot) disk to S3 (cold) automatically. It does this with a ClickHouse move TTL on each table: data older than a set number of weeks moves to cold.

There are two parts:

• The TTL, which applies to new data. Once it is set, ClickHouse moves data to cold in the background as it ages.
• The backfill, for data written before the TTL existed. That data carries no TTL information, so ClickHouse never moves it until a MATERIALIZE TTL runs. The chart can run it for you (opt-in), or you can run it by hand.

Setting the TTL

clickhouse:
  tieredStorage:
    enabled: true
    ttl:
      enabled: true
      tables:
        - name: traces_local
          moveAfterWeeks: 13

A post-upgrade hook (or ArgoCD PostSync hook) then, for each listed table:

1. Attaches the tiered_replicated storage policy, if the table is not on it yet.
2. Sets the TTL id_at + INTERVAL <moveAfterWeeks> WEEK TO DISK 'cold', without touching existing data.
3. Checks that every replica has the policy and the TTL.

It is idempotent: when everything is already in place it changes nothing, so it is safe on every upgrade. A table that does not exist yet is skipped.

Backfilling existing data

The backfill reads data on the hot disk and writes it to S3. On a large table that is a lot of disk I/O, S3 traffic and background work, so when to run it is your decision. Choose one of the two ways below.

Option 1: let the chart run it (small installations)

clickhouse:
  tieredStorage:
    ttl:
      enabled: true
      materialize:
        enabled: true

On the next upgrade, after setting the TTL, the hook runs ALTER TABLE ... ON CLUSTER '{cluster}' MATERIALIZE TTL for each table, but only when:

• some part of the table, on any replica, has no TTL information yet, and
• no MATERIALIZE TTL is already running on that table.

The hook only starts the operation; the data moves in the background afterwards. Once every part carries the TTL, later upgrades and syncs do nothing, so you can leave the flag on. If a run is interrupted, the next upgrade starts it again.

Option 2: run it by hand (large installations)

Leave materialize.enabled: false and, at a quiet time, run:

ALTER TABLE <db>.<table> ON CLUSTER '{cluster}' MATERIALIZE TTL

Run it once for each table in ttl.tables, as the chart does. <db> is the Opik ClickHouse database (ANALYTICS_DB_DATABASE_NAME in the backend’s environment).

In the queries below, <table> is a table from ttl.tables (for example traces_local) and <moveAfterWeeks> its number of weeks.

What to expect

• The MATERIALIZE TTL itself is fairly quick. It reads the column the TTL uses (id_at) from every part, computes each part’s TTL time and records it. The chart only sets TTLs that move data, so no rows are deleted or rewritten, and only a small part of the table is read.
• The moves come after it. ClickHouse’s background mover copies each part whose data is old enough to S3, one part at a time per mover thread, and then frees it from the hot disk.
• How long this takes depends on the amount of old data and on S3 throughput. Run it on a smaller environment first and measure, before you run it on a large one.
• Queries keep working throughout. Data on cold is read through the local cache.

What to monitor

Progress (size per disk, and parts that don’t carry the expected TTL yet, including parts with a different TTL from an earlier moveAfterWeeks):

SELECT disk_name, count() AS parts, formatReadableSize(sum(bytes_on_disk)) AS size,
       countIf(NOT has(move_ttl_info.expression, 'id_at + toIntervalWeek(<moveAfterWeeks>)')) AS without_expected_ttl
FROM system.parts
WHERE database = '<db>' AND table = '<table>' AND active
GROUP BY disk_name

The MATERIALIZE TTL run:

SELECT mutation_id, command, create_time, is_done, parts_to_do, latest_fail_reason
FROM system.mutations
WHERE database = '<db>' AND table = '<table>' AND command LIKE '%MATERIALIZE TTL%'
ORDER BY create_time DESC LIMIT 5

Moves in flight, and moves done in the last day (with failures):

SELECT table, part_name, target_disk_name, elapsed FROM system.moves WHERE database = '<db>';
SELECT count() AS moves, formatReadableSize(sum(size_in_bytes)) AS moved, countIf(error != 0) AS failed
FROM system.part_log
WHERE database = '<db>' AND table = '<table>' AND event_type = 'MovePart'
  AND event_time > now() - INTERVAL 1 DAY

How busy the mover is:

SELECT metric, value FROM system.metrics
WHERE metric IN ('BackgroundMovePoolTask', 'BackgroundMovePoolSize')

Run these on every replica: each replica moves its own copy of the data.

The backend can also export per-partition gauges, including opik.clickhouse.partition.cold_bytes and opik.clickhouse.partition.ttl_move_due_parts (parts old enough to move but still on hot). Turn them on with PARTITION_METRICS_ENABLED=true in the backend’s environment. Watch the hot disk’s free space, and S3 errors in the ClickHouse logs.

Risks and mitigation

Configuration reference

All keys are under clickhouse.tieredStorage.ttl:

Need Help?

If you encounter issues, please check the troubleshooting guide or reach out for support.