CONCEPT Cited by 1 source

ClickHouse MergeTree partition by time¶

Definition¶

The canonical idiom for ingesting time-series data into a ClickHouse MergeTree table: declare PARTITION BY toYYYYMM(timestamp) (monthly) or PARTITION BY toDate(timestamp) (daily) on the MergeTree() engine so that ClickHouse stores rows from the same time bucket together on disk. Partitions are both the unit of fast pruning at query time (WHERE timestamp BETWEEN ... skips whole partitions) and the unit of archival / eviction via DETACH PARTITION or TTL.

Canonical schema from the 2025-12-09 Redpanda IoT pipeline post:

CREATE TABLE telemetry_events (
    timestamp DateTime,
    device_id String,
    value Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp);

Verbatim framing: "PARTITION BY toYYYYMM(timestamp) or toDate(timestamp) will allow you to segment data monthly or daily. The above code creates a table named telemetry_events that groups its rows into monthly partitions like 202501 or 202502." (Source: sources/2025-12-09-redpanda-streaming-iot-and-event-data-into-snowflake-and-clickhouse)

Why time-partition¶

Partition pruning — queries with a predicate on the partition key read only matching partitions. Month-level retention + hot-cold query patterns benefit most.
Archival granularity — whole partitions can be detached ("ALTER TABLE telemetry_events DETACH PARTITION '202501'") and moved to external storage (S3, NAS) while the active dataset stays queryable.
TTL ergonomics — MODIFY TTL timestamp + INTERVAL 12 MONTH DELETE drops whole partitions rather than doing expensive row-level deletes on a non-partitioned table.
Compaction locality — the MergeTree background merge process works within a partition; smaller partitions = faster merges but more metadata overhead.

Choosing granularity¶

Monthly (toYYYYMM) — smaller partition count, larger partitions. Ideal when retention is measured in months-to- years and the WHERE predicates naturally span months. Marketing analytics, billing rollups.
Daily (toDate) — more partitions, smaller ones. Ideal when retention is measured in days-to-weeks, dashboards filter on "last 24 hours", or archival cadence is daily. Logs, alerts, high-frequency telemetry.
Hourly (toStartOfHour) — rarely used; overhead is usually too high.

Composes with¶

concepts/clickhouse-detached-partition-archival — partition granularity is archival granularity.
concepts/clickhouse-ttl-policy — TTL + partition together provide O(1) eviction of expired data.
concepts/hot-cold-tier-compression-codec-split — hot partitions stay under LZ4 codec on local SSD; aged partitions are moved to S3 with ZSTD codec.
concepts/clickhouse-index-granule-skipping — the ORDER BY key inside each partition provides granule-level skipping; partition pruning is the coarser skip above it.

Caveats¶

Cardinality of partition key matters. Too many small partitions (e.g. toStartOfMinute on high-volume ingest) explodes metadata and slows background merges.
Partition key must be a deterministic function of row columns. You cannot re-partition without rewriting the table.
Detach is a metadata operation, not a copy. Detached partitions live in the detached/ directory on the same storage; to actually move them to cold storage you need a separate copy step (S3 CLI, rsync) or an external-storage volume binding.

Seen in¶

sources/2025-12-09-redpanda-streaming-iot-and-event-data-into-snowflake-and-clickhouse — canonical wiki introduction. Redpanda IoT-pipeline tutorial post names toYYYYMM(timestamp) and toDate(timestamp) as the canonical time-partitioning idioms for long-term columnar storage of IoT/event data.