Technical Tip: How to delete data from Clickhouse database

Description

This article describes how to delete data from the Clickhouse database.

Scope

FortiSIEM, Clickhouse.

Solution

Follow the steps below to delete data quickly from the Clickhouse database, especially to release disk space.

Important note: The data will be removed definitively, and therefore cannot be recovered after following the steps below:

Connect to one worker on each shard:

1. 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 --query "SELECT DISTINCT partition FROM system.parts WHERE active AND table='events_replicated' AND max_time <= '2025-09-01 00:00:00' FORMAT TabSeparated" > /tmp/partitions.txt

exit

2. From the shell:

for partition in `cat /tmp/partitions.txt`; do clickhouse-client -q "alter table fsiem.events_replicated drop partition $partition"; done

3. 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