ClickHouse DB::Exception: Too many partitions for single INSERT block

The "DB::Exception: Too many partitions for single INSERT block" error in ClickHouse occurs when a single INSERT statement attempts to write data into more partitions than the server allows. The TOO_MANY_PARTITIONS error code is triggered when the number of distinct partition key values in one insert block exceeds the max_partitions_per_insert_block threshold, which defaults to 100.

Impact

When this error fires, the entire INSERT is rejected and no rows are written. This commonly disrupts bulk data loads, ETL pipelines, and migrations where historical data spanning many time periods or categories is inserted in a single batch. The table itself remains unaffected since the insert is rolled back atomically.

Common Causes

A partition key based on a high-cardinality column (e.g., toDate(timestamp) over years of data) produces hundreds or thousands of distinct partitions in a single insert
Backfilling or migrating historical data in one large batch that spans many months or years
Using a partition key expression that is too granular, such as toHour(timestamp) combined with a date, which multiplies the number of partitions rapidly
Application logic that batches unrelated rows together, causing a wide spread of partition key values in each insert
Incorrect partition key design that uses a column with unbounded cardinality

Troubleshooting and Resolution Steps

Identify which table and partition key are involved. Check the table's partition expression:

SELECT name, partition_key
FROM system.tables
WHERE database = 'your_database' AND name = 'your_table';

Count how many partitions a given batch would create before inserting:

SELECT count(DISTINCT toYYYYMM(timestamp)) AS partition_count
FROM your_staging_table;

If you need to insert the data as-is, temporarily raise the limit for that session:
```
SET max_partitions_per_insert_block = 1000;
INSERT INTO target_table SELECT * FROM staging_table;
```
Use this as a one-time escape hatch, not a permanent setting.

Split the insert into smaller batches that each touch fewer partitions:

INSERT INTO target_table
SELECT * FROM staging_table
WHERE toYYYYMM(timestamp) BETWEEN 202301 AND 202306;

INSERT INTO target_table
SELECT * FROM staging_table
WHERE toYYYYMM(timestamp) BETWEEN 202307 AND 202312;

If this happens regularly, redesign the partition key to produce fewer partitions. For example, change from daily to monthly partitioning. ClickHouse does not allow changing the PARTITION BY expression of an existing MergeTree table in place, so you must create a new table with the desired partition key and copy the data over:
```
CREATE TABLE your_table_new
(
    -- same columns as your_table
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(timestamp)
ORDER BY (...);

INSERT INTO your_table_new SELECT * FROM your_table;

RENAME TABLE your_table TO your_table_old, your_table_new TO your_table;
```

Check how many partitions the table already has, since too many partitions also hurts query performance:

SELECT count(DISTINCT partition)
FROM system.parts
WHERE database = 'your_database' AND table = 'your_table' AND active;

Best Practices

Design partition keys to produce a manageable number of partitions, typically in the tens to low hundreds total across the table's lifetime.
Use monthly partitioning (toYYYYMM()) rather than daily when data spans long time ranges.
Batch inserts by partition key value so each insert touches only a few partitions.
Avoid raising max_partitions_per_insert_block globally in production. If you must raise it, do so per-session for specific backfill operations.
Monitor the total partition count in system.parts and set alerts when it grows unexpectedly.

Frequently Asked Questions

Q: Is it safe to increase max_partitions_per_insert_block permanently?
A: It is not recommended. The limit exists because creating many partitions in a single insert consumes significant memory and generates many small parts that the merge process must later consolidate. Raising it permanently can degrade insert and merge performance.

Q: Can I change the partition key of an existing table without losing data?
A: ClickHouse does not support changing the partition key in place for MergeTree tables. You need to create a new table with the desired partition key and copy the data over, then rename the tables.

Q: Why is the default limit 100?
A: The default of 100 is a safety net. Writing to many partitions in a single insert creates many small parts, which increases merge pressure and file descriptor usage. Keeping inserts partition-local leads to better overall performance.