# 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.*

## 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:

```sql
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

| Table | Granules read | Time | Data read |
|---|---|---|---|
| `PARTITION BY supplier_id % 100` | 20,916 of 32,540 (64%) | ~2.6 s | 4.65 GiB |
| No partitioning | 972 of 31,729 (3.1%) | 191 ms | 189 MiB |
| `PARTITION BY intDiv(supplier_id, 5000000)` | 1,758 of 32,036 (5.5%) | 221 ms | — |

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.

---

Source: https://ivanblinov.com/writing/clickhouse-modulo-partitioning/