This article describes how to delete data from the Clickhouse database.
Scope
FortiSIEM, Clickhouse.
Solution
The steps below are to be followed in order to delete data quickly from the Clickhouse database, usually to release disk space.
Note:It is very important to note that the data will be removed definitively, and hence, cannot be recovered after following the steps below:
Get the list of partitions to be deleted, in this example, the parts to be deleted are older than '2025-10-30 00:00:00':
clickhouse-client SELECT DISTINCT partition FROM system.parts WHERE active AND (`table` = 'events_replicated') AND (max_time <= '2025-10-30 00:00:00') INTO OUTFILE '/tmp/paritions.txt'
exit
From the shell:
for partition in `cat /tmp/paritions.txt`; do clickhouse-client -q "alter table fsiem.events_replicated drop partition $partition"; done
Check that the partitions were dropped:
clickhouse-client SELECT DISTINCT partition FROM system.parts WHERE active AND (`table` = 'events_replicated') AND (max_time <= '2025-10-30 00:00:00')
for partition in `cat /tmp/paritions.txt`; do clickhouse-client -q "alter table fsiem.events_replicated drop partition $partition"; done
