Clickhouse: PARTITION BY vs ORDER BY

[simPod]

I was studying docs for a while but can't really figure out, how PARTITION BY and ORDER BY impact performance exactly of MergeTree.

I managed to figure out that ORDER BY specifies primary key of the table and therefore the index so when filtering using those columns in PREWHERE and WHERE it can leverage the index.

PARTITION BY says how to merge and allocate partitions. But how to choose the proper partitioning? What is the rule there? Also how to determine whether to specify only some DateTime partitioning key or include other keys as well? The only information I managed to find is not to make partitions to much granular ("This means that you shouldn't make overly granular partitions (more than about a thousand partitions), or SELECT will perform poorly").

And what is the role of filtering by datetime in all this?

I made following assumptions:

1) In ORDER BY I should specify columns that I plan to usually filter by. This also means more columns more disk space occupied. But the search is faster then.
2) PARTITION BY says how things are merged together so I should probably set it so it merges data that usually go together. (?) Let's say I usually want to find data for certain month in certain country so partitioning by month and country would make sense. Or is it better to partition by month only? Why would I want PARTITION BY toMonday()?
3) Filtering by datetime ... (?) Does it play any role in PARTITION/ORDER BY settings?

Can anyone make it more clear please? Are those assumption correct? Maybe we can extend the documentation after that.

Thanks!

[filimonov]

PARTITION BY - declare how to split data in table to a parts which can be manipulated separately. Partitions should be nor too small nor too big. Usually it's months / week / days - that allow you to drop old partitions, or do other manipulation with partition level. Clickhouse allows you to drop / optimize / freeze / detach / attach parttions, clear certain columns in that. I.e. partition is kind of element you can manipulate with.

When you use a condition which allows to read less partitions - that will be used. I.e. if you have 12 monthly partitions and you use condition where event_date = '2018-01-01' then only one partition, containing that date will be read. If you will not provide extra conditions on primary key - whole partition will be read.

Inside each partition data is physically sorted according to your primary key, and there is an index on that. If you will provide conditions containing fields used in primary key - clickhouse will read only some range of rows inside that partition which contain primary key you provided.

In all other case fullscans are used.

[simPod]

That clears it up for me. Thanks, much appreciated!