Understand Partition Query Performance Trade-offs

Understand Partition Query Performance Trade-offs

Impact: MEDIUM

Partitioning can help or hurt query performance:

• Potential improvement: Queries filtering by partition key may benefit from partition pruning
• Potential degradation: Queries spanning many partitions increase total parts scanned

ClickHouse automatically builds MinMax indexes on partition columns. Data merges occur within partitions only, not across them.

Incorrect (query scans all partitions):

-- Query must scan all partitions
SELECT count(*) FROM events
WHERE event_type = 'click';  -- No partition pruning

Correct (query prunes to single partition):

-- Query prunes to single partition
SELECT count(*) FROM events
WHERE timestamp >= '2024-01-01' AND timestamp < '2024-02-01'
  AND event_type = 'click';

Reference: Choosing a Partitioning Key