Handling “Too many partitions for single INSERT block”

ClickHouse supports partitioning in MergeTree family of table engines. A partition is a logical/virtual set of records based on a predefined condition. The following error may be common while inserting multiple rows into a MergeTree table with a partition key.

DB::Exception: Too many partitions for single INSERT block (more than 100). The limit is controlled by 'max_partitions_per_insert_block' setting. Large number of partitions is a common misconception. It will lead to severe negative performance impact, including slow server startup, slow INSERT queries and slow SELECT queries. Recommended total number of partitions for a table is under 1000..10000. Please note, that partitioning is not intended to speed up SELECT queries (ORDER BY key is sufficient to make range queries fast). Partitions are intended for data manipulation (DROP PARTITION, etc).

The error message is pretty self explanatory. This error occurs when we perform an INSERT operation with more than 100 unique values for the partition column. Let us try reproducing this error. Create a table and Insert records with more than 100 unique partition values in it.

CREATE TABLE partition_error_example
(col1 Int32,
col2 Int32)
ENGINE MergeTree
ORDER BY col1
PARTITION BY col2;

chistadata :) ​​​​​INSERT INTO partition_error_example
              SELECT * FROM generateRandom('col1 Int32, col2 Int32', 1) 
              LIMIT 1000;

INSERT INTO partition_error_example SELECT *
FROM generateRandom('col1 Int32, col2 Int32', 1)
LIMIT 1000

Query id: a9173978-ef3a-4ac2-9066-85e534f3bd40


0 rows in set. Elapsed: 0.003 sec. 

Received exception from server (version 22.12.3):
Code: 252. DB::Exception: Received from localhost:9000. DB::Exception: Too many partitions for single INSERT block (more than
 100). The limit is controlled by 'max_partitions_per_insert_block' setting. Large number of partitions is a common 
misconception. It will lead to severe negative performance impact, including slow server startup, slow INSERT queries and slow 
SELECT queries. Recommended total number of partitions for a table is under 1000..10000. Please note, that partitioning is not 
intended to speed up SELECT queries (ORDER BY key is sufficient to make range queries fast). Partitions are intended for data 
manipulation (DROP PARTITION, etc).. (TOO_MANY_PARTS)

As described in the error message, the max_partitions_per_insert_block setting is responsible for this. This error is a proactive measure to avoid a sudden increase in server load, thereby degrading the performance. We can increase this setting value (but not too much). We have to set the value in the profile section of users.xml file.

<profiles>
    <!-- Default settings. -->
    <default>
    <max_partitions_per_insert_block>10000</max_partitions_per_insert_block>

    </default>

The above setting will increase the threshold to 10000 parts per insertion. Though these are the workarounds available, this setting should still be handled with care and may cause slowness in production servers, depending on the data and the server specs.