> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://www.comet.com/docs/opik/llms.txt.

# 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](mailto:Sales@comet.com) to access the enterprise deployment documentation.

With the [cold tier](/self-host/clickhouse-cold-tier-migration) 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

```yaml
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.

> **Warning**
>
> The TTL the Job sets **replaces the table's whole TTL clause**. List only tables without a TTL of their own, such as `traces_local`. A table that already has one, for example a delete TTL for retention, would lose it.

## 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)

```yaml
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:

```sql
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`):

```sql
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:

```sql
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):

```sql
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:

```sql
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

| Risk                                                          | What to do                                                                                                                                                                                                                                                                                                                    |
| ------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Moves load the disk, S3 or the network while users are active | Pause moves on the table with `SYSTEM STOP MOVES <db>.<table>`, and resume later with `SYSTEM START MOVES <db>.<table>`. Run the backfill by hand at a quiet time.                                                                                                                                                            |
| A `MATERIALIZE TTL` is stuck, or you need to stop it          | Find it in `system.mutations` (above) and run `KILL MUTATION WHERE database = '<db>' AND table = '<table>' AND mutation_id = '<id>'`. Parts already done keep their TTL.                                                                                                                                                      |
| Moves fail (S3 permissions, network)                          | `system.part_log` shows the error. Failed moves are retried; data stays on the hot disk until a move succeeds. Fix the cause, for example the cold bucket's IAM access.                                                                                                                                                       |
| Data has to come back to the hot disk                         | `ALTER TABLE <db>.<table> MOVE PARTITION '<partition_id>' TO VOLUME 'default'`, on each replica. It needs free hot-disk space for the data. While the TTL is set, ClickHouse will move it to cold again, so remove the TTL first (`ALTER TABLE <db>.<table> ON CLUSTER '{cluster}' REMOVE TTL`) and set `ttl.enabled: false`. |

## Configuration reference

All keys are under `clickhouse.tieredStorage.ttl`:

| Value                                      | Default                  | Description                                                                        |
| ------------------------------------------ | ------------------------ | ---------------------------------------------------------------------------------- |
| `enabled`                                  | `false`                  | Run the TTL hook. Needs `clickhouse.tieredStorage.enabled: true`.                  |
| `tables`                                   | `traces_local`, 13 weeks | Tables to put on the cold tier, each with `name` and `moveAfterWeeks`.             |
| `materialize.enabled`                      | `false`                  | Also backfill existing data with `MATERIALIZE TTL` (see above). Must be a boolean. |
| `activeDeadlineSeconds`                    | `600`                    | Kubernetes hard cap on the hook Job.                                               |
| `resources`, `nodeSelector`, `tolerations` | empty                    | Scheduling for the hook Job.                                                       |

## Need Help?

If you encounter issues, please check the [troubleshooting guide](/self-host/troubleshooting) or reach out for support.