# Configure data retention thresholds in Managed ClickHouse®'s tiered storage


Control how your data is distributed between storage devices in the tiered storage of a Managed ClickHouse® service. Configure tables so that ClickHouse automatically writes your data to network-attached block storage or object storage as needed.

If you have [tiered storage enabled](/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/)
on your Managed ClickHouse service, Exoscale distributes your data between two storage
devices (tiers). Data is stored either on network-attached block storage or in object
storage, depending on whether and how you configure this behavior. By default, ClickHouse
moves data from network-attached block storage to object storage when it reaches 80% of
its capacity. You can
[adjust that threshold](/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/#size-based-threshold)
at the service level, or override the behavior per table with a time-based rule.

To change this data distribution behavior per table,
[configure your table's schema by adding a TTL (time-to-live) clause](/product/dbaas/service-specific/clickhouse/how-to/configure-tiered-storage/#time-based-retention-config).
Such a configuration allows ignoring the capacity
threshold for network-attached block storage and moving the data from it to object
storage based on how long the data has been stored there.

To enable this time-based data distribution mechanism, you can set up a
retention policy (threshold) on a table level by using the TTL clause.
For data retention control purposes, the TTL clause uses the following:

- Data item of the `Date` or `DateTime` type as a reference point in
  time
- INTERVAL clause as a time period to elapse between the reference
  point and the data transfer to object storage

## Prerequisites

- [Tiered storage enabled](/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/)
- Command line tool installed
  ([ClickHouse client](/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/))

## Configure the size-based threshold {#size-based-threshold}

The size-based move is controlled by the `tiered_storage_move_factor` service setting: the
fraction of free space on the block storage below which data starts moving to object storage.
The default is `0.2`, meaning moves start when less than 20% of the local disk is free, in
other words when it is 80% full. Values range from `0` to `1`.

Read the current value:

```bash
exo x get-dbaas-service-clickhouse my-clickhouse -z ch-gva-2 -q '"clickhouse-settings"'
```

Change it, here to start moving data when less than 30% of local disk is free:

```bash
echo '{"clickhouse-settings":{"tiered_storage_move_factor":0.3}}' | \
  exo x update-dbaas-service-clickhouse my-clickhouse -z ch-gva-2
```

The new value applies to the storage policy of the service without a restart.

## Configure time-based data retention {#time-based-retention-config}

1. [Connect to your Managed ClickHouse service](/product/dbaas/service-specific/clickhouse/how-to/list-connect-to-service/) using, for example, the ClickHouse client.

1. Select a database for operations you intend to perform.

   ```sql
   USE DATABASE_NAME
   ```

### Add or modify TTL

**Add TTL to a new table**

Create a table with the `storage_policy` setting set to `tiered` (to
[enable tiered storage](/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/)) and TTL
(time-to-live) configured to add a
time-based data retention threshold on the table.

```sql
CREATE TABLE example_table (
    SearchDate Date,
    SearchID UInt64,
    SearchPhrase String
)
ENGINE = MergeTree
ORDER BY (SearchDate, SearchID)
PARTITION BY toYYYYMM(SearchDate)
TTL SearchDate + INTERVAL 1 WEEK TO VOLUME 'remote'
SETTINGS storage_policy = 'tiered';
```

> [!NOTE]
> Managed ClickHouse remaps `MergeTree` to its replicated variant, unconditionally and
> including on single-node services, so `SHOW CREATE TABLE` does not return the definition you
> typed: the engine reads
> `ReplicatedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')` and
> `index_granularity = 8192` is added to the `SETTINGS` clause. See
> [Select a table engine](/product/dbaas/service-specific/clickhouse/how-to/manage-databases-tables/#select-a-table-engine).

**Modify TTL on an existing table**

Add or update a TTL definition with the `ALTER TABLE ... MODIFY TTL` statement:

```sql
ALTER TABLE database_name.table_name MODIFY TTL ttl_expression;
```

After TTL is configured, ClickHouse moves data older than the specified time period from
network-attached block storage to object storage, regardless of available capacity.

## Best practices for tiered storage TTL

Follow these recommendations to optimize performance and efficiency when using TTL with
tiered storage.

### Optimize part sizes for remote storage

Avoid creating many small parts on remote storage, as this can negatively impact performance.
When writing data that will be immediately moved to remote storage (such as during
backfilling of historical data):

- **Use large inserts**: Ensure your data inserts are large enough to create substantial
  parts on remote storage.
- **Temporarily disable TTL moves**: Use the following commands to pause data movement
  while smaller parts merge together:

  ```sql
  -- Stop TTL-based data moves temporarily
  SYSTEM STOP MOVES;

  -- Perform your data operations (inserts, merges)
  -- ... your operations here ...

  -- Resume TTL-based data moves
  SYSTEM START MOVES;
  ```

> [!WARNING]
> Remember to run `SYSTEM START MOVES` after your operations to resume normal TTL behavior.
> Leaving moves disabled will prevent automatic data tiering.

### Configure efficient data deletion

Use the `ttl_only_drop_parts` setting when using TTL for data **deletion**, not just for
moving between tiers:

```sql
CREATE TABLE example_table_deletion (
    SearchDate Date,
    SearchID UInt64,
    SearchPhrase String
)
ENGINE = MergeTree
ORDER BY (SearchDate, SearchID)
PARTITION BY toYYYYMM(SearchDate)
TTL SearchDate + INTERVAL 1 MONTH DELETE
SETTINGS storage_policy = 'tiered', ttl_only_drop_parts = 1;
```

#### How this helps

- **Prevents inefficient partial drops**: Instead of repeatedly rewriting parts as
  individual rows expire, ClickHouse drops entire parts at once.
- **Requires matching partition strategy**: Use a `PARTITION BY` expression that aligns
  with your TTL period so all data in a partition expires simultaneously.
- **Improves performance**: Eliminates the overhead of multiple partial rewrites.

#### Example of aligned partitioning and TTL

```sql
CREATE TABLE example_with_deletion (
    SearchDate Date,
    SearchID UInt64,
    SearchPhrase String
)
ENGINE = MergeTree
ORDER BY (SearchDate, SearchID)
-- Partition by month, TTL deletes data older than 1 month
PARTITION BY toYYYYMM(SearchDate)
TTL SearchDate + INTERVAL 1 MONTH DELETE
SETTINGS storage_policy = 'tiered', ttl_only_drop_parts = 1;
```

This ensures that when data expires, ClickHouse drops entire monthly partitions rather than
removing individual rows from parts.

## What's next

- [Check data volume distribution between different disks](/product/dbaas/service-specific/clickhouse/how-to/check-data-tiered-storage/)

- [About tiered storage in Managed ClickHouse](/product/dbaas/service-specific/clickhouse/overview/clickhouse-tiered-storage/)
- [Enable tiered storage in Managed ClickHouse](/product/dbaas/service-specific/clickhouse/how-to/enable-tiered-storage/)
- [Transfer data between network-attached block storage and object storage](/product/dbaas/service-specific/clickhouse/how-to/transfer-data-tiered-storage/)
- [Manage Data with TTL (Time-to-live)](https://clickhouse.com/docs/concepts/features/operations/delete/ttl)
- [Create table statement, TTL documentation](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl)
- [MergeTree - column TTL](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/mergetree#mergetree-column-ttl)