Is It Possible to Delete Old Records from a ClickHouse Table?

ClickHouse allows to automatically drop values when some condition happens. This condition is configured as an expression based on any columns, usually just static offset for any timestamp column.

The key advantage of this approach is that it doesn’t need any external system to trigger, once TTL is configured, data removal happens automatically in background.

Note

More details on configuring TTL.

ClickHouse doesn’t have real-time point deletes like in databases. The closest thing to them are mutations. They are issued as or ALTER ... UPDATE queries to distinguish from normal or UPDATE as they are asynchronous batch operations, not immediate modifications. The rest of syntax after prefix is similar.

ALTER DELETE can be issued to flexibly remove old data. If you need to do it regularly, the main downside will be the need to have an external system to submit the query. There are also some performance considerations since mutation rewrite complete parts even there’s only a single row to be deleted.

More details on mutations.

provides a cost-efficient way to drop a whole partition. It’s not that flexible and needs proper partitioning scheme configured on table creation, but still covers most common cases. Like mutations need to be executed from an external system for regular use.

More details on .

More details on table truncation.