I wanted to understand background merges better. I understand that background merges keep happening until it reaches 150GB as defined by max_bytes_to_merge_at_max_space_in_pool.
I would like to understand following.
- Are all parts ordered by ORDER BY? So for e.g. if ORDER BY is by date and if new inserts have older date it will eventually get merged with corresponding older part that has matching date (even if this older part is of 150GB and needs no merging) or is ordering not maintained across parts so this new insert can be merged in a new 150 GB part?
- In table engines like ReplacingMergeTree if duplicate entry to be removed are part of large parts having 150GB of size, do all of them gets reprocessed (as this deduplication only occurs during merge and there is no need to merge parts that have reached 150GB)?
I am trying to understand all of this as I would like ClickHouse to make no (or minimal) changes to parts once they reach size defined by max_bytes_to_merge_at_max_space_in_pool. Reason for this is because as our data would grow we would want to use slower-larger disks and maybe S3 for older data. I understand I can also create multiple volumes, have ClickHouse move parts to this volume once it reaches 150GB using max_data_part_size_bytes and set prefer_not_to_merge on this volume. What would be disadvantages of doing this?
