DELETE TTL not working with ClickHouse 25.5.6 and prefer_not_to_merge=1

[OhJuhun]

Problem

DELETE TTL is not working when using ClickHouse 25.5.6 (the default version in SigNoz) with prefer_not_to_merge=1 setting.

This is a known issue in ClickHouse reported in: ClickHouse/ClickHouse#85636

Root Cause

ClickHouse takes into account the prefer_not_to_merge setting when filtering part ranges in the merge selector. Parts are no longer deleted by TTL on volumes with prefer_not_to_merge=1 enabled.

Impact

• TTL DELETE operations do not execute on cold storage volumes
• Data that should be deleted according to TTL policies remains on disk
• Storage costs increase as old data is not cleaned up
• Affects all ClickHouse versions from 25.1 onwards, including 25.5.6 currently used in SigNoz

Solution

Upgrade ClickHouse version to 25.12.4.35 or later.

Workaround

Temporarily disable prefer_not_to_merge setting on volumes where TTL DELETE is needed (note: this may cause regular merges on cold storage).

References

• Upstream ClickHouse issue: TTLDelete + ttl_only_drop_parts does not work for volumes with prefer_not_to_merge=true ClickHouse/ClickHouse#85636
• Fix PR: Unblock ttl part drops for cold volumes ClickHouse/ClickHouse#90059