# ClickHouse Basics: MergeTree and Tiered Storage with S3

> How ClickHouse MergeTree stores data as sorted parts, and how a storage policy plus TTL TO VOLUME moves old data to S3 automatically, with a Docker quick start.

Source: https://sheng.page/en/posts/clickhouse-mergetree-and-s3-tiering/ · Published: 2025-07-13 · Author: Sheng

ClickHouse is a columnar OLAP database built for fast aggregation over large datasets. These notes cover a minimal local setup, how MergeTree stores data, and how to move older data to AWS S3.

## A quick start with Docker

The fastest route is compose. Using a ready-made template ([clickhouse_4.8](https://github.com/yshengliao/docker-compose-templates/tree/main/clickhouse_4.8), running `clickhouse/clickhouse-server:24.8`):

```bash
git clone https://github.com/yshengliao/docker-compose-templates
cd docker-compose-templates/clickhouse_4.8
docker compose up -d
```

Once it's up there are two interfaces: `8123` for HTTP and `9000` for the native TCP protocol. Connect and run SQL:

```bash
docker exec -it clickhouse clickhouse-client
# or over the HTTP interface
curl 'http://localhost:8123/?query=SELECT%20version()'
```

The template mounts `clickhouse-server/` as the config directory — that's where the S3 config goes shortly.

## MergeTree: parts and background merges

MergeTree is ClickHouse's workhorse engine. Its core behaviour: an INSERT doesn't append rows into one big file — it creates a **separate part, sorted by the primary key**; a background process then **merges** those parts into larger ones over time. That's where the name comes from, and conceptually it's close to an LSM-tree.

```sql
CREATE TABLE events
(
    ts       DateTime,
    user_id  UInt64,
    event    LowCardinality(String),
    payload  String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (user_id, ts);
```

`ORDER BY` is the sorting key, and by default it doubles as the primary key. The MergeTree family also has some interesting variants: `ReplacingMergeTree` (deduplicates by key on merge), and `SummingMergeTree` / `AggregatingMergeTree` (sum or aggregate same-key rows at merge time). Giving the merge step semantics is one of ClickHouse's neat ideas.

## The sparse primary index

ClickHouse's primary index is **sparse**: it doesn't index every row, but every `index_granularity` (8192 by default) rows as one granule, recording only each granule's start. So even with billions of rows the index is small enough to stay in memory.

When a query's condition lands on a prefix of the `ORDER BY`, ClickHouse skips the irrelevant granules (data skipping) and reads only the blocks it needs. So how you choose `ORDER BY` largely decides how fast queries run — put the columns you filter on most up front.

## PARTITION BY and TTL

`PARTITION BY toYYYYMM(ts)` slices data by month. It isn't an index; it lets you operate on whole chunks: partition pruning when you query a specific month, an instant `DROP PARTITION` to delete old data, and movement across storage tiers a partition at a time.

`TTL` has two uses. One is expiring rows so they're deleted:

```sql
ALTER TABLE events MODIFY TTL ts + INTERVAL 90 DAY;
```

The other is more interesting — when the time comes, **move data to another storage tier**. That's the hook for hot/cold tiering (next section).

## Tiered storage with S3

Hot data on a local SSD queries fast; old data on S3 is cheap — ClickHouse makes this declarative via `storage_configuration`. Drop a `storage.xml` into `clickhouse-server/config.d/` (the template already mounts that directory):

```xml
<clickhouse>
  <storage_configuration>
    <disks>
      <s3_cold>
        <type>s3</type>
        <endpoint>https://YOUR_BUCKET.s3.ap-northeast-1.amazonaws.com/clickhouse/</endpoint>
        <access_key_id>YOUR_KEY</access_key_id>
        <secret_access_key>YOUR_SECRET</secret_access_key>
      </s3_cold>
    </disks>
    <policies>
      <hot_cold>
        <volumes>
          <hot><disk>default</disk></hot>   <!-- local SSD -->
          <cold><disk>s3_cold</disk></cold> <!-- AWS S3 -->
        </volumes>
        <move_factor>0.2</move_factor>
      </hot_cold>
    </policies>
  </storage_configuration>
</clickhouse>
```

`move_factor` means "when free space on a volume drops below this fraction (0.2 = 20%), data starts moving to the next volume automatically". Attach the policy to the table, then use TTL to say when data moves to the cold tier:

```sql
CREATE TABLE events ( /* …as above… */ )
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (user_id, ts)
TTL ts + INTERVAL 30 DAY TO VOLUME 'cold'
SETTINGS storage_policy = 'hot_cold';
```

Data under 30 days stays on the local SSD (hot); after that the background moves it to S3 (cold), with no change to your query syntax — cold data is just a little slower. For three tiers — hot / warm / cold — add a local-HDD volume in the middle and write the TTL as `… + 7 DAY TO VOLUME 'warm', … + 30 DAY TO VOLUME 'cold'`.


## Notes

- ClickHouse is a columnar OLAP store; MergeTree is the workhorse: an INSERT creates a sorted part, and the background merges parts into larger ones (close to an LSM).
- The primary index is sparse (one granule per 8192 rows), so it's small and memory-resident; your `ORDER BY` choice decides how much data a query can skip.
- `PARTITION BY` lets you prune, drop, and move data a partition at a time; the MergeTree family (Replacing / Summing / Aggregating) gives the merge step semantics.
- `TTL ... TO VOLUME` plus `storage_configuration` gives you tiering: hot data on SSD, old data auto-moved to S3, with unchanged query syntax.
- The [clickhouse_4.8](https://github.com/yshengliao/docker-compose-templates/tree/main/clickhouse_4.8) template with `docker compose up -d` is the fastest start; the S3 config takes effect once dropped into `clickhouse-server/config.d/`.

---

> Written by Sheng with Claude's help on prose and examples · environment ClickHouse 24.8 (LTS) + Docker · part of the 2026-06-13 blog renovation, paint still drying.