ClickHouse Basics: MergeTree and Tiered Storage with S3

· · 5 min read

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.

Correction (2 November 2026, 18:45 Taipei): automatic moves between storage tiers are now described as happening per part, with MOVE PARTITION for moving a whole partition by hand; the 30-day TTL now moves a part only once all its rows have expired, and move_factor can move data earlier (suggested by Codex (gpt-6-astra), decided by a vote of Codex (gpt-6-astra), Grok and Claude Fable). The 8192 granule size is now given as the default maximum row count, also capped by index_granularity_bytes (suggested by Codex (gpt-6-astra), checked by Claude).

A quick start with Docker

The fastest route is compose. Using a ready-made template (clickhouse_4.8, running clickhouse/clickhouse-server:24.8):

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:

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.

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 splits the data into granules and records only each granule’s start. A granule holds at most index_granularity rows (8192 by default); adaptive granularity is on by default, so index_granularity_bytes (10 MiB by default) also caps its size, and wide rows give granules of fewer than 8192 rows. 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 ALTER TABLE … MOVE PARTITION to move a whole partition’s parts to another tier by hand. Automatic moves, from TTL or from low free space, work part by part.

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

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):

<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:

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';

Moves happen per part: the background moves a part to S3 (cold) only once all of its rows are older than 30 days, so the timing depends on the newest row in the part and on background scheduling. When free space on the hot volume falls below move_factor, younger parts can be moved early too. The 30-day figure is only a condition for moving, and it does not guarantee that newer data stays on the local SSD. Query syntax doesn’t change, and cold data is just a little slower. For three tiers (hot, warm and 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 (a granule holds at most 8192 rows by default, also capped at 10 MiB), so it’s small and memory-resident; your ORDER BY choice decides how much data a query can skip.
  • PARTITION BY lets you prune and drop data a partition at a time and move a partition by hand with MOVE PARTITION, while automatic moves work per part; the MergeTree family (Replacing / Summing / Aggregating) gives the merge step semantics.
  • TTL ... TO VOLUME plus storage_configuration gives you tiering: a part moves to S3 once all its rows have expired, or earlier under space pressure, with unchanged query syntax.
  • The 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.