> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

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

<Note>
  Backups are an exception: Cloud storage lifecycle policies are a valid strategy for Backup Lifecycle management. See [Backup Strategy](/docs/cloud/clickhouse-private/explanation/backup-strategy) for more details.
</Note>

***

## 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](https://docs.aws.amazon.com/AmazonS3/latest/userguide/object-lifecycle-mgmt.html), [GCS Object Lifecycle Management](https://cloud.google.com/storage/docs/lifecycle), or equivalent services on other providers -- operate independently of ClickHouse and **will delete or transition objects that ClickHouse still depends on**.

<Warning>
  **Lifecycle policies cause data loss**

  Enabling 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.
</Warning>

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:

```sql theme={null}
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:

```sql theme={null}
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

```sql theme={null}
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](https://clickhouse.com/docs/guides/developer/ttl).

***

## Partition Management

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

### List Partitions

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
-- 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](https://clickhouse.com/docs/sql-reference/statements/alter/partition).

***

## Monitoring Storage Usage

Track how much space your tables use in object storage:

```sql theme={null}
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 |