Mastering Custom Partitioning Keys in ClickHouse: A Complete Guide

When working with large-scale data analytics, proper partitioning strategy can make the difference between lightning-fast queries and sluggish performance. ClickHouse’s custom partitioning keys offer powerful capabilities for optimizing data storage and retrieval, but they must be implemented thoughtfully to avoid common pitfalls.

Understanding ClickHouse Partitioning Fundamentals

Partitioning in ClickHouse creates logical combinations of records based on specified criteria, with each partition stored separately to enable efficient data manipulation. This separation allows ClickHouse to use only the smallest necessary subset of partitions when processing queries, dramatically improving performance for queries that include partitioning keys.

Key Benefits of Proper Partitioning

• Query Performance: ClickHouse filters partitions before selecting parts and granules
• Data Management: Simplified operations on specific data subsets
• Storage Optimization: Efficient organization of related data
• Maintenance Operations: Faster backups, deletions, and data lifecycle management

Best Practices for Partition Key Selection

The Golden Rule: Less is More

The most critical principle when designing partition keys is restraint. In most scenarios, you don’t need a partition key at all, and when you do, monthly granularity typically suffices.

-- Recommended: Monthly partitioning
CREATE TABLE user_events
(
    event_date Date,
    user_id UInt64,
    event_type String,
    event_data String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (user_id, event_date);

What NOT to Do: Over-Granular Partitioning

Avoid partitioning by client identifiers, user names, or other high-cardinality fields. This creates excessive partitions that degrade performance rather than improve it.

-- WRONG: Too granular partitioning
CREATE TABLE user_events
(
    event_date Date,
    user_id UInt64,
    event_type String
)
ENGINE = MergeTree()
PARTITION BY user_id  -- This creates too many partitions
ORDER BY event_date;

-- CORRECT: Use high-cardinality fields in ORDER BY instead
CREATE TABLE user_events
(
    event_date Date,
    user_id UInt64,
    event_type String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (user_id, event_date);  -- Client ID as first column in ORDER BY

Implementing Custom Partitioning Keys

Basic Partition Key Syntax

The partition key is defined using the PARTITION BY clause during table creation:

CREATE TABLE visits
(
    VisitDate Date,
    Hour UInt8,
    ClientID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(VisitDate)
ORDER BY Hour;

Advanced: Tuple-Based Partitioning

For more complex scenarios, you can use tuple expressions combining multiple criteria:

CREATE TABLE event_analytics
(
    StartDate Date,
    EventType String,
    CounterID UInt64,
    UserID UInt64,
    Sign Int8
)
ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/analytics', 'replica1', Sign)
PARTITION BY (toMonday(StartDate), EventType)
ORDER BY (CounterID, StartDate, intHash32(UserID));

This example partitions data by both the week (using toMonday()) and event type, providing granular control while maintaining reasonable partition counts.

Handling Floating-Point Partition Keys

By default, floating-point partition keys are disabled for performance reasons. Enable them only when necessary:

-- Enable floating-point partition keys
SET allow_floating_point_partition_key = 1;

CREATE TABLE sensor_data
(
    timestamp DateTime,
    sensor_id UInt32,
    temperature Float64
)
ENGINE = MergeTree()
PARTITION BY round(temperature, 1)  -- Partition by temperature ranges
ORDER BY (sensor_id, timestamp);

-- This query only scans partitions for 2024
SELECT COUNT(*)
FROM user_events
WHERE event_date >= '2024-01-01' AND event_date < '2025-01-01';

-- Daily partitioning for high-volume, recent data
PARTITION BY toYYYYMMDD(event_date)

-- Monthly partitioning for historical analysis
PARTITION BY toYYYYMM(event_date)

-- Weekly partitioning for business reporting
PARTITION BY toMonday(event_date)

CREATE TABLE sales_data
(
    sale_date Date,
    region String,
    product_category String,
    amount Decimal(10,2)
)
ENGINE = MergeTree()
PARTITION BY (toYYYYMM(sale_date), region)
ORDER BY (product_category, sale_date);

SELECT 
    partition,
    count() as parts_count,
    sum(rows) as total_rows,
    formatReadableSize(sum(data_compressed_bytes)) as compressed_size
FROM system.parts
WHERE table = 'your_table_name'
GROUP BY partition
ORDER BY partition;

-- Drop old partitions
ALTER TABLE user_events DROP PARTITION '202301';

-- Optimize specific partitions
OPTIMIZE TABLE user_events PARTITION '202312';

-- Detach and reattach partitions for maintenance
ALTER TABLE user_events DETACH PARTITION '202312';
ALTER TABLE user_events ATTACH PARTITION '202312';