Note

Partitioning by modulo in ClickHouse: the DDL I wrote that made queries 13.7 times slower

PARTITION BY supplier_id % 100 sounds like it spreads the load evenly. In practice it destroyed primary-key pruning: a lookup read 64% of the table instead of 3%. Here are the measurements, and why it happens.

2 min read · Markdown

The table and the query

A ReplacingMergeTree with 266 million rows of stock by seller, product, size and warehouse:

• ORDER BY (supplier_id, nm_id, chrt_id, office_id);
• PARTITION BY supplier_id % 100.

I wrote that DDL myself, by analogy with an older table, when we moved the report from PostgreSQL.

The service query asks for about 1,000 (seller, product) pairs at a time:

SELECT …
FROM stock FINAL
WHERE (supplier_id, nm_id) IN (…1,000 pairs…)

The requested sellers' rows were 9.6% of the table. The query read 64% of all granules (20,916 of 32,540) and 4.65 GiB, and took about 2.6 seconds with a warm cache.

Three variants, same data

Without partitions the query was 13.7 times faster and read 25 times less.

The range partitioning did well too, but the divisor was badly chosen: seller IDs are skewed, and 93% of rows landed in partition 0. Something like intDiv(supplier_id, 100000) would spread them better.

Even the access pattern partitioning was supposed to help, one seller at a time (2.7 million rows), lost: 326 granules and 57–71 ms without partitions, against 337–446 granules and 75–129 ms with the modulo. The primary-key prefix already narrows the read to one seller; the partition only adds fragmentation.

Why modulo breaks pruning

ClickHouse skips granules using the [min, max] range of the key columns in each granule (KeyCondition). With supplier_id % 100:

• every part contains every hundredth seller across the whole ID space;
• so within a part, consecutive granules jump between sellers that are far apart in ID terms;
• each granule's [min, max] range for supplier_id becomes about a hundred times wider than in a part ordered over a contiguous range.

A thousand requested pairs then intersect the ranges of almost every granule. The index check knows ranges, not residues; it cannot tell that seller 1,234,567 is absent from a granule spanning sellers 1,200,000 to 1,300,000.

It is not a planner bug, and it is not FINAL: both were ruled out by measurement.

What I do now

• Partition for data management, not for query speed. Retention, dropping old data, backfilling a month: those are partitioning's job. Pruning is ORDER BY's job.
• Never partition by a hash or modulo of a column the queries filter on. It spreads every key's neighbors across all parts and widens every granule's range.
• If you need partitions on an ID, use ranges (intDiv) sized to the actual distribution of the IDs.
• Check with EXPLAIN indexes = 1 how many granules a typical query reads. A number close to the table's total is a design smell, not a slow disk.

All writing · A similar problem? contact@ivanblinov.com