Manage Data Lifecycle

This guide explains how to manage the lifecycle of data stored by ClickHouse Private using ClickHouse’s built-in primitives. It covers why cloud-provider object lifecycle policies must not be used and how to achieve the same goals safely through TTL rules and partition management.

Do Not Use Cloud Object Lifecycle Policies

ClickHouse stores data in object storage (S3, GCS, or S3-compatible backends) and manages the lifecycle of those objects internally. Cloud-provider lifecycle policies – such as S3 Lifecycle, GCS Object Lifecycle Management, or equivalent services on other providers – operate independently of ClickHouse and will delete or transition objects that ClickHouse still depends on.

Specifically, do not configure:

• S3 Lifecycle rules (expiration, transition, abort incomplete multipart uploads with short timeouts)
• GCS Object Lifecycle Management (delete, SetStorageClass)
• Intelligent-Tiering or automatic storage class transitions

If you need to control data retention, storage costs, or disk usage, use the ClickHouse primitives described below.

TTL (Time-to-Live) Rules

TTL rules let you define automatic data expiration at the table level. When data meets the TTL condition, ClickHouse removes it during background merges.

Delete Data After a Fixed Period

Add a TTL clause to your table definition to automatically delete rows older than a specified interval:

CREATE TABLE events
(
    event_time DateTime,
    event_type String,
    payload String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time)
TTL event_time + INTERVAL 6 MONTH DELETE;

This deletes rows older than 6 months. Partitioning by month allows ClickHouse to drop entire partitions efficiently rather than rewriting individual data parts.

Conditional TTL

You can apply different retention periods based on data content:

CREATE TABLE events
(
    event_time DateTime,
    event_type String,
    payload String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time)
TTL event_time + INTERVAL 1 MONTH DELETE WHERE event_type != 'error',
    event_time + INTERVAL 12 MONTH DELETE WHERE event_type = 'error';

Add TTL to an Existing Table

ALTER TABLE events
    MODIFY TTL event_time + INTERVAL 6 MONTH DELETE;

Best Practices for TTL

• Align partitions with TTL granularity. If TTL is in months, partition by month (toYYYYMM). If TTL is in days, partition by day (toYYYYMMDD). This allows ClickHouse to drop whole partitions instead of rewriting data parts.
• Use DELETE (the default action) to remove expired rows. Other actions like TO VOLUME or TO DISK move data between storage tiers but do not free space.
• Monitor TTL execution with the system.parts table. Check that parts with expired data are being merged and removed.

For full TTL documentation, see ClickHouse TTL documentation.

Partition Management

For one-off cleanup or data management outside of TTL rules, use partition operations.

List Partitions

SELECT
    partition,
    count() AS parts,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE table = 'events' AND active
GROUP BY partition
ORDER BY partition;

Drop a Partition

Remove all data from a specific partition:

ALTER TABLE events DROP PARTITION '202501';

This is an instant metadata operation – ClickHouse removes references to the data parts and cleans up the underlying storage objects asynchronously.

Detach and Re-attach a Partition

To temporarily remove a partition without deleting the underlying data:

-- Detach (removes from active set, keeps on disk)
ALTER TABLE events DETACH PARTITION '202501';

-- Re-attach later if needed
ALTER TABLE events ATTACH PARTITION '202501';

For full partition management documentation, see ClickHouse partition operations.

Monitoring Storage Usage

Track how much space your tables use in object storage:

SELECT
    database,
    table,
    formatReadableSize(sum(bytes_on_disk)) AS total_size,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    count() AS part_count
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC;

Summary