Partition and model deliberately

Partitions group parts so you can operate on a chunk of the table at once. Dropping old data is the typical use. They are not a replacement for the ordering key.

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

Monthly partitions are the common choice 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. There is no row-by-row delete and no rewrite.

Too many partitions

High-cardinality partition keys create too many parts. Partition by day and keep three years: 1,095 partitions. Partition by user_id: potentially millions. Parts never merge across partition boundaries, so part counts explode, inserts that touch many partitions slow down, and eventually they 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 key is too fine. My advice: partition by month, or don’t partition at all. You need a reason to partition, and that reason is almost always retention. You don’t need a reason to skip it.

Denormalize the hot path

Analytical tables copy the dimensions used in every query straight into the events. 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, put the name in the events table.

Dictionaries and joins still have a place. A dimension that changes often, or one you rarely query, can stay external. But copying stable labels into events makes the hot path simpler. No join to plan, no second table that has to be available, and LowCardinality(String) makes the repeated values nearly free to store.

Plan before you create the table

Try this with one year of application events. Choose a partition key, an ordering key, and the retention operation, and estimate the number of partitions before you write the CREATE TABLE.

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