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.

Backups are an exception: Cloud storage lifecycle policies are a valid strategy for Backup Lifecycle management. See Backup Strategy for more details.

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.

Lifecycle policies cause data lossEnabling object lifecycle policies on buckets used by ClickHouse Private has caused production outages where databases appeared to vanish. ClickHouse tracks its own objects in metadata; if the underlying objects are deleted externally, the metadata becomes inconsistent and the data is irrecoverable.

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

Goal	Correct approach	Incorrect approach
Auto-delete old data	Table-level `TTL ... DELETE`	S3/GCS lifecycle expiration rules
Remove a specific time range	`ALTER TABLE ... DROP PARTITION`	Deleting objects from the bucket
Limit storage costs	Combine TTL with appropriate partition granularity	Cloud-provider object lifecycle management

ClickHouse Cloud

Bring Your Own Cloud

ClickHouse Private

Do Not Use Cloud Object Lifecycle Policies

TTL (Time-to-Live) Rules

Delete Data After a Fixed Period

Conditional TTL

Add TTL to an Existing Table

Best Practices for TTL

Partition Management

List Partitions

Drop a Partition

Detach and Re-attach a Partition

Monitoring Storage Usage

Summary

​Do Not Use Cloud Object Lifecycle Policies

​TTL (Time-to-Live) Rules

​Delete Data After a Fixed Period

​Conditional TTL

​Add TTL to an Existing Table

​Best Practices for TTL

​Partition Management

​List Partitions

​Drop a Partition

​Detach and Re-attach a Partition

​Monitoring Storage Usage

​Summary

Do Not Use Cloud Object Lifecycle Policies

TTL (Time-to-Live) Rules

Delete Data After a Fixed Period

Conditional TTL

Add TTL to an Existing Table

Best Practices for TTL

Partition Management

List Partitions

Drop a Partition

Detach and Re-attach a Partition

Monitoring Storage Usage

Summary