Understanding clickhouse partitions

Question

I see that clickhouse created multiple directories for each partition key(in each node). Documentation says the directory name format is : partition ID_minimum block number_maximum block number_level. Any idea what is level here? 347 distinct partition keys on one node(for one table) created 1358 directories. (custom partitioning)

The documentation recommends not to have more than 1000 partitions. Should we just keep in mind the number of partitions keys or the number of directories also?

Also, Is there a configuration on how to control this number of directories?

Answer 1

Any idea what is level here?

Level is a concept of LSM-tree. MergeTree tables have mechanisms to merge data parts into bigger and deeper (w.r.t level) ones.

Should we just keep in mind the number of partitions keys or the number of directories also?

Well I don't think that's a good idea as this method doesn't scale well. You'd better choose a low-cardinality column or expression as the partition key.

Also, Is there a configuration on how to control this number of directories?

No explicit settings for that. But you can easily use modular expression to limit the total number of partitions.

Answer 2

Adding to this discussion, you can check parts and partition in the following ways :

For active partition :

select count(distinct partition)  from system.parts  where the table in ('table_name') and active

For Active parts :

select count() from system.parts  where   table in ('table_name') and active

Inactive parts will be removed soon in less than 10 minutes.

Furthermore, you can also read more here about parts, partition and how merging happens.

To view table parts and partition together :

SELECT
    partition,
    name,
    active
FROM system.parts
WHERE table = 'table_name'