Partition and model deliberately

Partitions group parts for operations such as dropping old data. They are not a replacement for the ordering key.

This distinction trips up almost everyone coming from other databases. The ordering key is what makes queries fast. A partition is a lifecycle unit: a chunk of the table you can drop, move, or detach as one cheap operation.

Monthly partitions are common for time data:

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

Now a retention policy is one statement:

ALTER TABLE events DROP PARTITION '202507';

Dropping a partition deletes its parts instantly, with none of the cost of deleting rows one by one.

The too-many-partitions failure

High-cardinality partition keys create too many parts. Partition by day and keep three years of data: 1,095 partitions. Partition by user_id: potentially millions. Every partition holds its own parts that can never merge across partition boundaries, so part counts explode, inserts touching many partitions slow down, and eventually inserts fail with a Too many parts error.

The symptom shows up in system.parts:

SELECT partition, count() AS parts
FROM system.parts
WHERE table = 'events' AND active
GROUP BY partition
ORDER BY parts DESC;

Hundreds of partitions with a handful of tiny parts each means the partition key is too fine. My advice: partition by month, or don’t partition at all. You need a reason — usually retention — to partition, not a reason to skip it.

Denormalize the hot path

Analytical tables often denormalize dimensions used in every query. In a normalized OLTP schema you’d store service_id and join to a services table. Here, if every dashboard query filters or groups by service name, copy the name into the events table.

Dictionaries or joins can still help — a dimension that changes often, or one used rarely, can stay external. But copying stable labels into events may make the hot path simpler: no join to plan, no second table to keep available, and LowCardinality(String) makes the repeated values nearly free to store.

Try it

Choose a partition, ordering key, and retention operation for one year of application events. Estimate the number of partitions before creating the table.

Monthly gives you 12; daily gives you 365. Say the retention rule out loud — “each month, drop the partition from 13 months ago” — and check the operation matches the partition unit. If your retention is expressed in days but your partitions are months, one of the two needs to change.