Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 35 ยท Tables and MergeTree Engines

Partitioning and Parts

Learn what PARTITION BY does in ClickHouse, why it is for data management and not speed, how to choose a partition key, and how to drop or move partitions.

Partitioning is the most misunderstood feature of MergeTree. People coming from other databases assume PARTITION BY is a performance tool. In ClickHouse it is mainly a data management tool: it groups parts into logical buckets that you can drop, move or archive as a unit. It can help some queries, but the primary key does that job far better. Used badly, partitioning creates thousands of tiny parts and makes everything slower.

ClickHouse cannot run on this site. The DDL and ALTER statements are read-only. The runnable blocks use SQLite to show how rows group by partition key and how many partitions a key creates.

What a partition is

SQL
CREATE TABLE events
(
    event_time DateTime,
    user_id    UInt64,
    kind       LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (kind, event_time);

Each row goes to the partition named by the expression: May 2026 rows go to partition 202605. Every part belongs to exactly one partition, and parts in different partitions are never merged together. Within a partition, parts merge as usual.

flowchart TB
    T["events table"] --> P1["Partition 202604"]
    T --> P2["Partition 202605"]
    P1 --> A["part 202604_1_5_1"]
    P1 --> B["part 202604_6_6_0"]
    P2 --> C["part 202605_7_9_1"]

Part names start with the partition id. Merges stay inside a partition.

You can see the layout:

SQL
SELECT partition, count() AS parts, sum(rows) AS rows,
       formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE table = 'events' AND active
GROUP BY partition
ORDER BY partition;

What partitioning is good for

  1. Cheap retention. Dropping a whole month is a metadata operation: instant, no row-by-row delete.

    SQL
    ALTER TABLE events DROP PARTITION 202501;
    
  2. Moving and archiving. DETACH PARTITION, ATTACH PARTITION, MOVE PARTITION ... TO TABLE, FETCH PARTITION and REPLACE PARTITION let you rotate data between tables or storage tiers, rebuild one month safely or back up one slice.

  3. Partition pruning. A filter on the partition expression (for example a date range) lets ClickHouse skip whole partitions with a min-max check before touching the primary key. It is a bonus, not the main purpose.

  4. Per-partition TTL and merge control for time-based data. See TTL, codecs and mutations.

What it is not good for

  • Speeding up arbitrary filters. Only filters on the partition expression benefit.
  • Splitting data by a high-cardinality column such as user_id, url or a timestamp. That creates thousands of partitions, each with its own small parts. Parts never merge across partitions, so you get the "Too many parts" problem.
  • Replacing the sorting key.

A solid rule: aim for tens to a few hundred partitions in total, not thousands. Monthly partitions are a good default for most event data; daily partitions only for very large tables whose retention is a number of days. If you have no retention or archive need, do not partition at all.

Choosing the expression

Data Reasonable key
Logs, events with 90-day retention toYYYYMMDD(ts) or toMonday(ts)
Events kept for years toYYYYMM(ts)
Small table, no retention no PARTITION BY
Multi-tenant, drop a tenant at once Generally better: separate table or tenant_id in the sort key, not as partition

The expression must be cheap and deterministic. toYYYYMM(ts) returns an integer like 202605; toStartOfWeek(ts) returns a date. Both are fine.

See partition keys with a stand-in

Compute how many rows land in each partition for two candidate keys. A key that yields very many tiny groups is a warning sign.

SQL Stand-in: rows per monthly partition (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

SQL Stand-in: a bad key creates many tiny partitions

Press Run (or Ctrl + Enter) to see the result here.

With 8 rows, partitioning by timestamp gives 8 partitions of one row each. The same effect with billions of rows is thousands of one-part partitions that never merge.

Partition operations

SQL
ALTER TABLE events DROP PARTITION 202501;                -- delete
ALTER TABLE events DETACH PARTITION 202502;              -- keep files, hide from queries
ALTER TABLE events ATTACH PARTITION 202502;              -- bring back
ALTER TABLE events MOVE PARTITION 202503 TO TABLE events_archive;
ALTER TABLE events FREEZE PARTITION 202504;              -- hard-link snapshot for backup
OPTIMIZE TABLE events PARTITION 202505 FINAL;            -- force a merge (use sparingly)

DROP PARTITION is immediate and permanent; check the partition id first with the system.parts query. The partition id for string or tuple expressions is a hash, so the partition column is the one to read.

Inserts that span partitions

One INSERT of rows from three months writes three parts (one per partition touched). Inserting data for many partitions at once multiplies parts, so for backfills load one partition's worth at a time, sorted by time.

A limit protects you: max_partitions_per_insert_block (default 100) rejects a single insert that touches more than that many partitions. If you hit it, your key is probably too fine.

Common beginner mistakes

  • Partitioning by a high-cardinality column.
  • Believing partitions make queries faster in general.
  • Daily partitions on a small table.
  • Forgetting that dropping a partition cannot be undone.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main purpose of PARTITION BY in MergeTree?

    Partitions are for data management such as retention; the primary key is for query speed.
  2. 2. Are parts from different partitions ever merged together?

    Each part belongs to one partition and merges only with parts of the same partition.
  3. 3. Which partition key is most likely to cause 'Too many parts' problems?

    A high-cardinality key creates huge numbers of tiny partitions that never merge.
  4. 4. How do you quickly delete a month of old data?

    Dropping a partition is a fast metadata operation compared with deleting rows.

Summary

  • PARTITION BY groups parts into logical partitions; parts in different partitions never merge.
  • Its strengths are retention, archiving and moving data; query speed comes from the sorting key.
  • Aim for tens to hundreds of partitions; monthly is a good default; skip partitioning if you have no use for it.
  • DROP, DETACH, ATTACH, MOVE and REPLACE PARTITION manage data cheaply.

Next: Replacing and collapsing engines