Partition Count (Top 10 Tables), ClickHouse

​

At a glance

A ranked table of your ten tables with the most partitions, where a partition is a logical slice of a MergeTree table defined by its PARTITION BY expression (commonly one per month, or one per day). Partition count is a design-health signal, not a real-time load signal. A table with hundreds or thousands of partitions almost always has a too-granular partition key (partitioning by hour or by a high-cardinality column instead of by month), which fragments data, inflates part counts, slows merges and queries, and pushes the table toward the Too Many Parts cliff. This card is how you catch a partitioning mistake before it becomes an ingest outage.

​

Calculation

SELECT
    table,
    uniqExact(partition_id)            AS partitions,
    count()                            AS active_parts,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active
GROUP BY table
ORDER BY partitions DESC
LIMIT 10;

​

Worked example

-- the offending table
CREATE TABLE page_events (...)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(event_time)   -- DAILY partitioning, plus a high-cardinality twist
ORDER BY (event_time, user_id);
-- worse: an earlier version used PARTITION BY (toDate(event_time), site_id)
-- with ~40 site_ids, multiplying partitions ~40x

1. This is a design fault, not load. 1,247 partitions in two weeks is impossible from daily partitioning alone (that would be ~14). The site_id in the partition key multiplied the slices: ~14 days x ~40 sites plus historical backfill produced ~1,200 partitions. The fix is the PARTITION BY, not more hardware.
2. The parts-to-partitions ratio confirms it. 1,389 parts across 1,247 partitions is ~1.1 parts per partition. ClickHouse cannot merge across partitions, so each tiny slice is stuck as its own near-unmergeable unit. That fragments the 04 GB across ~1,400 files and pushes the table toward the Too Many Parts cap even though the data volume is trivial.
3. It will become an outage if ignored. As ingest continues, parts climb. Pair with Too Many Parts Errors (24h): once a single partition’s part count breaches the cap, inserts to page_events start failing with code 252 and the pipeline stalls.

Why this is over-partitioned:
  - Table age:                    14 days
  - Partitions:                   1,247  (impossible from date alone -> key includes site_id)
  - Active parts:                 1,389
  - Parts per partition:          ~1.1   (each slice is its own near-unmergeable unit)
  - Data size:                    04 GB  (tiny, the problem is fragmentation, not volume)
  => Redesign PARTITION BY to monthly (toYYYYMM(event_time)) and drop site_id from
     the partition key (move it into ORDER BY instead). Expected result: ~1 partition.

​

Sibling cards platform teams should reference together

​

Reconciling against the source

system.parts for the authoritative count: SELECT table, uniqExact(partition_id) AS partitions, count() AS parts FROM system.parts WHERE active GROUP BY table ORDER BY partitions DESC. This is exactly what the card runs. system.parts per partition to see the offender’s shape: SELECT partition, count() AS parts, sum(rows) FROM system.parts WHERE active AND table = 'page_events' GROUP BY partition ORDER BY parts DESC. SHOW CREATE TABLE <name> to read the PARTITION BY expression that produced the partitions, the root-cause artefact. system.tables for partition_key and engine metadata across tables. ClickHouse Cloud: the same system.parts reads apply; the Cloud console also surfaces table-level part and partition statistics per service.

​

Known limitations / FAQs

​

Tracked live in Vortex IQ Nerve Centre