support custom retention for logs / traces

[Vadman97]

we should allow retaining logs / traces for custom time windows (not just 30 days)

we can do this by adjusting the TTL setting on the clickhouse tables to be based on a column value rather than having it be static for the entire table. this will also allow row-level retention, so we can retain a given row indefinitely.

we should also support row-level retention for sessions / errors by deleting data from clickhouse for those records

[linear]

HIG-4660 support custom retention for logs / traces

[greptile-apps]

To support custom retention for logs and traces, adjust the TTL settings in the ClickHouse table creation scripts to be based on a column value. This will allow row-level retention. Additionally, support row-level retention for sessions and errors by deleting data from ClickHouse for those records.

1. Logs Table (/backend/clickhouse/migrations/000007_drop_logs_old.down.sql):

   TTL toDateTime(Timestamp) + INTERVAL LogRetentionDays DAY

2. Traces Table (/backend/clickhouse/migrations/000041_create_traces_table_new.up.sql):

   TTL toDateTime(Timestamp) + INTERVAL TraceRetentionDays DAY

3. Sessions and Errors: Implement row-level deletion logic based on custom retention settings for sessions and errors in the respective tables.

References

/backend/clickhouse/migrations/000007_drop_logs_old.down.sql
/backend/clickhouse/migrations/000041_create_traces_table_new.up.sql
/backend/clickhouse/migrations/000024_create_logs_sampling_table.up.sql
/backend/clickhouse/migrations/000045_create_trace_attributes.up.sql
/backend/clickhouse/migrations/000026_create_log_attributes.up.sql
/backend/clickhouse/migrations/000083_create_new_traces_by_id_mv.up.sql
/backend/clickhouse/migrations/000047_create_trace_keys.up.sql
/backend/clickhouse/migrations/000043_create_traces_sampling_table.up.sql
/backend/clickhouse/migrations/000058_create_trace_metrics_mv.up.sql
/backend/clickhouse/migrations/000059_create_traces_by_id_mv.up.sql

Ask Greptile