Partition for deletion, sort for reading
Most time-series tables I see in review have one of two problems: thousands of tiny partitions, or one giant partition nobody can expire. Both come from asking the partition key to do a job that belongs to the sort key.
Two keys, two jobs
The partition key is about lifecycle. It answers: what unit of data do I delete, move to cold storage, or rewrite together? For event data that is almost always a time bucket, usually a month and sometimes a day.
The sort key is about access. It answers: how do my queries narrow the data down? If most queries filter by account and then by a time range, that is the sort key.
CREATE TABLE events
(
account_id UInt64,
ts DateTime64(3),
kind LowCardinality(String),
payload String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (account_id, ts)
TTL toDateTime(ts) + INTERVAL 13 MONTH DELETE;
Monthly partitions keep the part count sane and make the TTL cheap: whole parts can be dropped once they age out instead of being rewritten. ORDER BY (account_id, ts) lets the primary index skip almost everything for a query like "events for account 42 last week".
The tempting mistake
PARTITION BY (account_id, toDate(ts)) looks like it does both jobs. It does neither well. With ten thousand accounts you get ten thousand new partitions a day, each with its own parts; merges never get a chance to consolidate them, inserts slow down, and eventually the table refuses writes with "too many parts" at the worst possible moment.
Iceberg has the same split
Hidden partitioning makes the mistake harder to make, not impossible. days(ts) is usually the right transform. If one column dominates reads, bucket(16, account_id) can be added, because the bucket count is bounded and the raw id is not. Then set a write order so files within a partition are clustered:
ALTER TABLE lake.events WRITE ORDERED BY account_id, ts;
A rule I keep coming back to: if you can't say how many partitions the table will have a year from now, the partition key is wrong.