Opik Data Retention

[GopiSharma]

What component(s) are affected?

• Opik Python SDK
• Opik Typescript SDK
• Opik Agent Optimizer SDK
• Opik UI
• Opik Server
• Documentation

Opik version

Latest

Describe the problem

Just wondering how can we set retention for Opik data in self hosted server in AWS Ec2 via docker-compose. We have mounted Clickhouse/Mysql/Minio to EBS and just want to keep last 7 days of traces.

Reproduction steps and code snippets

No response

Error logs or stack trace

No response

Healthcheck results

No response

[github-actions]

Hi, I'm Scout 🦉, the comet-ml/opik repository agent. Let me look into this.

Solution / Workaround

There is no built-in UI or configuration option for data retention in self-hosted Opik as of the current version. However, you can enforce retention manually using ClickHouse TTL (the primary store for traces/spans), and MinIO lifecycle policies (for attachments). MySQL holds metadata (projects, prompts, etc.) and typically grows much more slowly, so TTL there is less critical.

Option 1: Add TTL to ClickHouse tables (Recommended)

Connect to your ClickHouse instance and run ALTER TABLE to add TTL expressions on the created_at column. For a 7-day retention on the two main tables:

-- Traces
ALTER TABLE opik.traces
  MODIFY TTL toDateTime(created_at) + INTERVAL 7 DAY;

-- Spans
ALTER TABLE opik.spans
  MODIFY TTL toDateTime(created_at) + INTERVAL 7 DAY;

-- Feedback scores (optional, if you want consistency)
ALTER TABLE opik.feedback_scores
  MODIFY TTL toDateTime(created_at) + INTERVAL 7 DAY;

⚠️ Important: traces and spans use ReplacingMergeTree / ReplicatedReplacingMergeTree. ClickHouse TTL works correctly with these engines — rows are deleted once the TTL expression is satisfied and a merge occurs. You can force an immediate cleanup with:

OPTIMIZE TABLE opik.traces FINAL;
OPTIMIZE TABLE opik.spans FINAL;

To make this persistent across restarts, you can add a SQL initialization script or add it to the ClickHouse clickhouse-init container step in docker-compose.yaml.

Option 2: MinIO lifecycle policy (for stored attachments)

The mc service in docker-compose.yaml already runs at startup to configure the MinIO bucket. You can extend its entrypoint to set a lifecycle rule:

entrypoint: >
  /bin/sh -c "
  sleep 10s;
  /usr/bin/mc alias set s3 http://minio:9000 ... ;
  /usr/bin/mc mb --ignore-existing s3/public;
  /usr/bin/mc anonymous set download s3/public/;
  /usr/bin/mc ilm rule add --expire-days 7 s3/public;
  "

Option 3: Use ClickHouse PARTITION BY for efficient bulk deletes

For higher-volume deployments, adding PARTITION BY toYYYYMMDD(created_at) to the tables and then running ALTER TABLE ... DROP PARTITION for old partitions is more efficient than TTL merges. However, this requires a table recreation via a new migration, which is more involved.

Code Investigation

The relevant files are:

• deployment/docker-compose/docker-compose.yaml — defines all services. The clickhouse-init and mc containers are good hook points for startup configuration.
• deployment/docker-compose/clickhouse_config/additional_config.xml — ClickHouse server config (cluster/ZooKeeper/macros). TTL is not configured here.
• apps/opik-backend/src/main/resources/liquibase/db-app-analytics/migrations/000001_init_script.sql — creates traces and spans tables with ReplacingMergeTree(last_updated_at) ordered by (workspace_id, project_id, id). No TTL clause is present.

The deletion_events_local table (000096_create_deletion_events_local.sql) is the only ClickHouse table that currently has a TTL (TTL toDateTime(event_time + INTERVAL 2 YEAR)), confirming the pattern is known and used internally but not applied to trace/span data.

There is no test coverage for retention/TTL behavior.

Next Steps

This is a real gap in the self-hosted offering. Two concrete improvements would help:

1. Short-term: Document the manual ALTER TABLE ... MODIFY TTL approach above in the self-hosting docs.
2. Medium-term: Expose a OPIK_DATA_RETENTION_DAYS environment variable in docker-compose.yaml that is passed to the backend, which then applies TTL via a Liquibase migration or an on-startup SQL command.

This would be a useful feature request — the pattern for TTL already exists in the codebase (deletion_events_local), so extending it to traces, spans, and feedback_scores is straightforward. The mc entrypoint pattern is also already in place for MinIO.

Was this helpful? React to this comment with 👍 or 👎 to rate my response.

---

View Scout trace in Opik

[GopiSharma]

Hi Team, thanks for your detail answer.

Is applying TTL is just three table is traces, spans, and feedback_scores is good for data retention and complete cleanup.

Should we consider applying TTL to other tables as well .

alert_logs
annotation_queue_items
annotation_queues
assertion_results
attachments
authored_feedback_scores
automation_rule_evaluator_logs
cipx_spend_blocks
cipx_spends
cipx_trace_identities
comments
dataset_item_versions
dataset_items
deletion_events_local
experiment_aggregates
experiment_item_aggregates
experiment_items
experiments
guardrails
optimizations
trace_threads
workspace_configurations

[A1exZabr]

Three of the tables you listed already ship with a TTL, so they are not yours to configure:

Table	TTL	Where it comes from
automation_rule_evaluator_logs	timestamp + 6 months	000010, carried over when the table moved to a replicated engine in 000017
alert_logs	timestamp + 6 months	000043
deletion_events_local	event_time + 2 years	000096

For the rest, read the schema off your own instance instead of trusting a list built from the migration files. 000017 renames tables as it moves them to replicated engines, so the file history no longer lines up with a running database. From the clickhouse container:

SELECT name,
       engine,
       create_table_query LIKE '%TTL %' AS has_ttl
FROM system.tables
WHERE database = 'opik'
ORDER BY has_ttl, name;

That is the real list for the version you are on.

I would run this one first, though, so the effort goes where the bytes actually are:

SELECT table,
       formatReadableSize(sum(bytes_on_disk)) AS size,
       sum(rows) AS rows
FROM system.parts
WHERE database = 'opik' AND active
GROUP BY table
ORDER BY sum(bytes_on_disk) DESC;

On which tables need a TTL at all, the schema splits in two. spans is keyed by trace_id, traces by thread_id, and feedback_scores, comments, guardrails and attachments all hang off entity_id. Those grow with traffic, and that is where retention belongs.

The other half does not. experiments and optimizations are keyed to dataset_id, and the configuration and queue tables are per workspace or per project. Rows there are old because you created them a while ago, not because they are stale, and dropping them by age deletes things you still open from the UI.

Two things I could not check, because I read this from the repository and not from a running instance. attachments holds metadata while the payloads sit in MinIO, so a TTL on it frees no disk by itself and needs a MinIO lifecycle rule next to it. And since feedback_scores and comments key on entity_id rather than trace_id, their TTL runs independently of the one on traces: set different intervals and you get scores pointing at traces that are already gone.