ClickHouse "Too many partitions for single INSERT block": Causes and Fixes

The ClickHouse error "Too many partitions for single INSERT block" means one block of an INSERT contained rows for more than 100 different partitions, so ClickHouse refused to write it. It usually appears in two situations: someone is loading months or years of history into a table partitioned by day, or the table's PARTITION BY is far too granular. In the first case a batched load solves it in minutes. In the second the error is a warning about a schema problem that will hurt you later anyway.

The message is unusually long, because ClickHouse uses it to explain partitioning:

Code: 252. 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. ... (TOO_MANY_PARTS)

What the limit counts

The check lives in MergeTreeDataWriter.cpp, where ClickHouse splits an incoming block of rows by partition key before writing. For every new partition value it finds in the block, it compares the running count with max_partitions_per_insert_block. When the block reaches a 101st distinct partition with the default setting, the insert of that block fails with code 252 (TOO_MANY_PARTS, the same code as the "Too many parts" error).

Three details matter in practice:

• The default is 100, and 0 means unlimited. The limit has existed since 19.5, according to the setting's history in Settings.cpp.
• It counts partitions per block, not per table. A table may have 3,000 partitions and accept inserts all day, as long as each block touches fewer than 101 of them. A large INSERT ... SELECT is processed as several blocks, and each block is checked on its own.
• Each partition in a block becomes a separate part. The partitioning best-practices page describes it directly: instead of one new part per insert, ClickHouse creates one new part for each unique partition key value among the inserted rows. A block spread over 500 partitions means 500 small parts at once, which is the road to "Too many parts".

Find the partition key and the number of partitions

Start by looking at what the table is partitioned by. The partition_key column of system.tables shows the expression:

SELECT database, name, partition_key, sorting_key
FROM system.tables
WHERE engine LIKE '%MergeTree%' AND partition_key != ''
ORDER BY database, name;

Then see how many partitions the tables already have:

SELECT database, table,
       uniqExact(partition) AS partitions,
       count() AS active_parts
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY partitions DESC
LIMIT 20;

If the source table has materialized views, check their target tables too. A view writes its own blocks into its own table, and that table's partition key is what counts there.

Before loading a batch, it costs one query to know whether it will fit:

-- use the exact expression from partition_key
SELECT uniqExact(toYYYYMMDD(event_time)) AS partitions_in_batch
FROM staging.events_import;

Case 1: the key is fine, the batch is too wide

A table partitioned by day (toDate or toYYYYMMDD) is common for logs and events. Daily inserts touch one or two partitions, and nobody notices the limit, until someone loads a year of history in one go. A year is 365 daily partitions in a single statement, so the blocks fail.

The clean fix is to load in slices that stay under the limit:

-- one month per INSERT: at most ~31 daily partitions per block
INSERT INTO analytics.events
SELECT * FROM staging.events_import
WHERE toYYYYMM(event_time) = 202401;

-- then 202402, 202403, ... from a script, checking row counts after each

Each month produces at most about 31 parts per block, which keeps merges calm, and a failed slice is easy to retry. One caution: do not assume a failed big INSERT wrote nothing. The ClickHouse glossary says an insert is atomic only when its rows are written as a single block, and an insert spanning partitions is atomic separately for each partition. A large INSERT that failed on a later block may already have written earlier ones. Compare row counts before you re-run anything, or you will get duplicates.

For a one-off backfill you can also raise the limit for your session only:

-- clickhouse-client session for a one-off backfill only
SET max_partitions_per_insert_block = 1000;
INSERT INTO analytics.events SELECT * FROM staging.events_import;

This is acceptable when the table's partition count is reasonable and you know exactly how many partitions the batch covers. Expect a burst of small parts and a busy merge queue afterwards. Do not put a raised value into the default profile to make a pipeline error go away.

ClickHouse 23.8 LTS and newer also have throw_on_max_partitions_per_insert_block (default true). Set to false, it writes a warning to the server log with the user, the query ID and the number of partitions, instead of failing the insert. The settings reference suggests it for checking the impact on users before you change max_partitions_per_insert_block. It is a measuring tool, not a fix.

Case 2: the partition key is too granular

If ordinary daily inserts hit the limit, the schema is the problem. Typical examples are PARTITION BY customer_id, PARTITION BY (tenant, toDate(ts)), partitioning by hour, or by some ID that just looked like a natural grouping.

ClickHouse's own documentation is blunt about it. The custom partitioning key page says that in most cases you do not need a partition key, and in most other cases nothing more granular than a month, with daily partitions common for observability data. It also says not to partition by client identifiers or names and to put the identifier first in ORDER BY instead. The best-practices guide calls partitioning primarily a data management technique, not a query optimization tool. The error message itself makes the same point: the sort key is what makes range queries fast, and partitions exist for operations like DROP PARTITION and retention.

A partition key is part of the table definition, and changing it in practice means building a new table and moving the data:

CREATE TABLE analytics.events_new
(
    event_time  DateTime,
    customer_id UInt64,
    event_type  LowCardinality(String),
    payload     String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)          -- was: PARTITION BY (customer_id, toDate(event_time))
ORDER BY (customer_id, event_time);

-- copy month by month (same idea as the batched backfill above)
INSERT INTO analytics.events_new
SELECT * FROM analytics.events WHERE toYYYYMM(event_time) = 202401;

-- compare counts, then swap names atomically (Atomic database engine)
EXCHANGE TABLES analytics.events AND analytics.events_new;

• Copy month by month, so the copy itself does not trip the limit.
• Decide what happens to writes arriving during the copy. Either pause them, write to both tables, or copy the last slice again right before the swap.
• EXCHANGE TABLES swaps the names atomically. It works only on the Atomic and Shared database engines; with another engine, plan a short write pause and use RENAME.
• Keep the old table until reports and row counts match, then drop it.

On a replicated cluster the same steps run with ON CLUSTER and replicated engines, and the copy takes real time and disk space. Expect to need free space on the order of the table's size while both copies exist. If you are moving a large legacy dataset into ClickHouse in the first place, it pays to settle the partition key before the first load; we covered that in our guide to moving legacy data into a ClickHouse data warehouse.

How to keep it from coming back

• Review PARTITION BY when a table is created. Ask what you will drop or move as a unit. If the answer is "old months", partition by month.
• Write backfills as batched jobs by default, one partition range per statement, with row-count checks.
• Watch the partition count per table with the system.parts query above. The custom partitioning page warns against more than about a thousand partitions; the error message itself gives 1000..10000 as the total to stay under.
• Search the query log for code 252 to see which users and jobs hit the limit:

SELECT event_time, user, query_id, left(query, 120) AS q
FROM system.query_log
WHERE exception_code = 252
  AND exception LIKE 'Too many partitions%'
  AND event_time BETWEEN now() - INTERVAL 7 DAY AND now()
ORDER BY event_time DESC
LIMIT 50;