ClickHouse Partitioning: When, How, and What Goes Wrong

Q: What is the best partition key for time-series data in ClickHouse?

Monthly partitioning with toYYYYMM(event_time) is the default for time-series tables. Daily partitions are reasonable only with a clear daily retention or backfill story, and high-cardinality keys like user_id or event_type are almost always wrong. Aim for dozens of partitions, tolerate hundreds, never thousands.

Q: What causes the "Too many parts" error in ClickHouse?

Inserts fail with Too many parts when active parts per partition exceed parts_to_throw_insert, which defaults to 300. Over-partitioning is a leading cause because every insert batch is split into one new part per partition value it touches; short-term fixes are batching inserts and async inserts, and the long-term fix is usually repartitioning.

Q: Can I change PARTITION BY on an existing ClickHouse table?

No, the partition key is fixed at CREATE TABLE time. To repartition without downtime, create a new table with the desired PARTITION BY, backfill it with INSERT... SELECT one source partition at a time, copy the final delta, and swap with EXCHANGE TABLES (ClickHouse 21.7+).

Q: How does daylight saving time affect ClickHouse partitions?

DateTime columns without an explicit timezone use the server timezone, so functions like toYYYYMM and toDate can assign rows near midnight to the wrong partition during DST transitions. Store timestamps as DateTime64(3, 'UTC') and apply local-timezone bucketing with toTimezone at query time.

A decision tree for ClickHouse partitioning: when it pays off (retention, archival, tiered storage), when it backfires (too many parts, slow inserts), and how to fix a wrongly-partitioned table without downtime.

ClickHouse Partitioning: When, How, and What Goes Wrong

Partitioning is the ClickHouse feature most teams reach for first and regret later. The mental model from PostgreSQL or Snowflake says "partition for query performance," but in ClickHouse the primary index handles range pruning through ORDER BY, and partitions are mainly a data lifecycle tool. Partition the wrong way and you trade a fast table for a slow one with Too many parts errors at insert time.

This post is the decision tree we walk through with clients: when partitioning pays off, when it actively hurts, and how to migrate a wrongly-partitioned table without downtime. The short version: partition by month for time-series, skip partitioning for everything else, and never use the partition key as a substitute for the sorting key.

What Partitioning Actually Does in ClickHouse

In ClickHouse, a partition is a logical grouping of data parts that share the same value of a PARTITION BY expression. Each unique partition value gets its own directory on disk, and ClickHouse never merges parts across partition boundaries. That last sentence is the entire reason partitioning is dangerous: high cardinality in the partition key directly translates into more unmerged parts on disk.

Compare this with the ORDER BY clause, which controls how rows are sorted within a part and feeds the sparse primary index. Range filters on the sort key are pruned through the index regardless of partitioning. So when a query filters by WHERE event_time BETWEEN ... AND ..., ClickHouse already skips most granules through the primary index. Adding PARTITION BY toYYYYMM(event_time) on top mostly helps with operations on whole months, not with normal SELECT queries.

The headline use cases for partitioning are operational, not analytical:

Retention: drop a month of logs in milliseconds with ALTER TABLE ... DROP PARTITION.
Archival and tiered storage: move cold partitions to cheaper disks with MOVE PARTITION TO DISK.
Atomic swaps: rebuild a slice of data in a staging table, then REPLACE PARTITION into production.
Backups and freezes: snapshot a single partition with FREEZE PARTITION instead of the whole table.

If none of those apply to your workload, you probably do not need partitioning at all.

The Partitioning Decision Tree

Before adding PARTITION BY to a CREATE TABLE, walk through this checklist. If you do not get a clear "yes" on at least one row, leave the table unpartitioned.

Question	Yes	No
Do you delete or archive data on a time boundary (e.g., drop data older than 90 days)?	Partition by that boundary (`toYYYYMM`, `toMonday`)	Skip partitioning
Do you move cold data to a slower disk or object storage?	Partition by the time unit you tier on	Skip partitioning
Do you regularly rebuild or backfill a contiguous slice of data?	Partition so the slice maps to whole partitions	Skip partitioning
Will the partition key produce more than ~100 active partitions?	Reconsider - that is too many	N/A
Is the partition key the same column you filter on in queries?	That is the wrong reason - use `ORDER BY` instead	N/A

The last row is where most teams go wrong. Partition pruning helps, but it is a coarse filter compared to the granule-level skipping the primary index already does. If your goal is "faster queries on event_time," put event_time in ORDER BY, not in PARTITION BY.

Choosing a Partition Key That Will Not Hurt

A few rules cover most production cases:

Keep cardinality low: aim for dozens of partitions, tolerate hundreds, never thousands. Monthly (toYYYYMM) is the default for time-series. Daily (toDate) is reasonable only when you have a clear retention or backfill story for daily slices and inserts batch by day.
Match retention boundaries: if you keep 13 months of data, monthly partitions let you drop one partition per month. Daily partitions in the same scenario give you 395 partitions doing the work of 13.
Avoid high-cardinality keys: PARTITION BY user_id or PARTITION BY event_type is almost always wrong. If you really need data-isolation per tenant, use projections or separate tables.
Tuple keys are usually overkill: PARTITION BY (toYYYYMM(ts), region) multiplies cardinality by the number of regions. Only do this if you truly drop data by (month, region).

-- Time-series with 90-day retention: monthly is fine
  CREATE TABLE events
  (
      event_time DateTime64(3, 'UTC'),
      user_id    UInt64,
      payload    String
  )
  ENGINE = MergeTree
  PARTITION BY toYYYYMM(event_time)
  ORDER BY (user_id, event_time)
  TTL toDateTime(event_time) + INTERVAL 90 DAY;

The ORDER BY (user_id, event_time) does the heavy lifting for WHERE user_id = ... AND event_time > ... queries. The partition key exists so TTL can drop whole months in milliseconds.

DST and Timezone Gotchas

Time-based partitioning has a quiet failure mode that only surfaces twice a year: daylight saving time. ClickHouse DateTime columns store timestamps in the server timezone unless you explicitly attach one. Functions like toYYYYMM and toDate apply the column's timezone to compute the partition value, so a row inserted in a non-UTC timezone can land in the wrong month near midnight on the last day.

The defensive pattern is to store everything in UTC:

-- Force UTC storage so partition assignment is timezone-stable
  event_time DateTime64(3, 'UTC')
  
  -- Apply local-timezone bucketing only at query time
  SELECT toStartOfDay(toTimezone(event_time, 'America/New_York')) AS day_local,
         count()
  FROM events
  GROUP BY day_local;

DST also breaks any partition expression that uses local-time bucketing on a column without an explicit timezone. During spring-forward, an hour does not exist; during fall-back, an hour repeats. If you partition by toYYYYMMDD(event_time) on a column declared as DateTime (no timezone) on a server in America/New_York, two rows with the same wall-clock timestamp during fall-back can map to slightly different points and you will see surprising behavior in retention scripts that delete by date string. Pin the timezone at column definition time and partition on UTC values. The official DateTime documentation is explicit about this: timezone is metadata on the column, not on the value, and it changes how partition expressions evaluate.

Over-Partitioning and the "Too Many Parts" Symptom

The most common partition-related production incident is the DB::Exception: Too many parts error during inserts. ClickHouse caps the number of active parts per partition at parts_to_throw_insert, which defaults to 300 in current versions (26.x as of May 2026). It also caps the total parts across the table at max_parts_in_total, which was raised from 300 to 100,000 in version 23.6. When background merges cannot keep up, inserts fail.

Over-partitioning is a leading cause. Every insert batch gets split into one new part per partition value it contains. Insert 10,000 rows that span 100 partitions and you create 100 parts in one go - 100 times more merge work than the same insert into a single partition. A few signs you have over-partitioned:

Inserts intermittently fail with Too many parts even though traffic is moderate.
system.parts shows tens of thousands of small active parts.
system.merges is constantly busy, but parts_count does not drop.
A single "small" insert touches dozens of partitions.

We have a dedicated walkthrough of the diagnosis and recovery steps in The 'Too Many Parts' Problem in ClickHouse. The short-term mitigations are batching inserts to span fewer partitions, raising parts_to_throw_insert carefully (600 or 1200 is common, but it masks the real problem), and using async inserts. The long-term fix is usually to repartition.

-- Quick health check for over-partitioning
  SELECT
      database,
      table,
      countDistinct(partition) AS partitions,
      count() AS active_parts,
      round(active_parts / partitions, 1) AS avg_parts_per_partition
  FROM system.parts
  WHERE active = 1
  GROUP BY database, table
  ORDER BY active_parts DESC
  LIMIT 20;

If partitions is in the thousands, or avg_parts_per_partition is consistently above 50, the partitioning scheme is fighting the merge engine.

Fixing a Wrongly-Partitioned Table Without Downtime

You cannot change PARTITION BY on an existing table - it is fixed at CREATE TABLE time. Migrating to a different partition key is a rebuild. The trick is doing it without dropping writes or stalling reads.

The pattern below uses INSERT ... SELECT into a new table and an atomic EXCHANGE TABLES swap. It works for both single-node and replicated setups.

Create the new table next to the old one.

CREATE TABLE events_new AS events
  ENGINE = MergeTree
  PARTITION BY toYYYYMM(event_time)
  ORDER BY (user_id, event_time)
  TTL toDateTime(event_time) + INTERVAL 90 DAY;

Match ORDER BY and column types exactly. Change only PARTITION BY (and TTL if needed).

Backfill in chunks. Doing the entire copy in one statement creates a giant merge backlog and risks Too many parts on the new table. Backfill one source partition at a time, oldest first:
```
INSERT INTO events_new
  SELECT * FROM events
  WHERE toYYYYMM(event_time) = 202601
  SETTINGS max_insert_threads = 4, max_partitions_per_insert_block = 100;
  
```
Loop over the partitions you find in system.parts for the source table. Run OPTIMIZE TABLE events_new PARTITION '202601' FINAL after each chunk if you need to consolidate parts before continuing.
Catch up the tail. Live writes are still going to events while you backfill. Once the backfill is within a few minutes of now(), briefly pause the writer (or fail it over to a buffer table), copy the last delta, then run the swap.
Atomic swap. ClickHouse 21.7+ supports atomic table exchange:
```
EXCHANGE TABLES events AND events_new;
  
```
Reads and writes immediately route to the new table. The old table is now named events_new and you can drop it once you are confident.
Replicated tables. On a replicated cluster, create events_new as ReplicatedMergeTree with a fresh ZooKeeper path, run the copy on one replica, and let replication propagate. The EXCHANGE TABLES is per-node; use ON CLUSTER if your DDL is cluster-aware.

A faster alternative when source and target share the same ORDER BY and only differ in partitioning is ALTER TABLE ... MOVE PARTITION TO TABLE, but it requires the partition keys to match, so it does not help here. Stick with INSERT SELECT for repartitioning.

Operational Patterns You Will Use Often

Once partitioning is in place for the right reasons, a small set of ALTER TABLE operations covers most real maintenance. All of these touch metadata cheaply and skip the row-level rewrite cost of mutations:

Retention: ALTER TABLE events DROP PARTITION '202501' removes a month in one metadata operation. ClickHouse marks the partition inactive immediately and clears the data within ~10 minutes. This is orders of magnitude faster than ALTER TABLE ... DELETE WHERE, which rewrites parts.
Tiered storage: ALTER TABLE events MOVE PARTITION '202501' TO DISK 's3_cold' migrates older data to cheaper storage. Combine with storage policies for automatic moves on TTL.
Atomic rebuild: INSERT into a staging table, then ALTER TABLE prod REPLACE PARTITION '202501' FROM staging swaps a partition atomically. Useful for late-arriving data corrections.
Snapshots: ALTER TABLE events FREEZE PARTITION '202501' creates a hardlink-based snapshot under shadow/, ready to ship to backup storage. Cheap and atomic.
Detach and reattach: DETACH PARTITION followed by file-level inspection and ATTACH PARTITION is the recovery path when a partition is corrupted or you need to move data between hosts.

These are the operations that justify partitioning. If you do not run any of them on a regular cadence, the partitioning is paying maintenance cost for nothing.

Frequently Asked Questions

Does partitioning make ClickHouse queries faster?

Mostly no. In ClickHouse the sparse primary index built from ORDER BY already prunes ranges at the granule level, so partitioning is only a coarse filter on top of that. Partitioning is a data lifecycle tool for retention, tiered storage, atomic swaps and backups, not a query optimizer.

When should I partition a ClickHouse table?

Partition when you delete or archive data on a time boundary, move cold data to a slower disk or object storage, or regularly rebuild and backfill a contiguous slice of data. If none of those apply, leave the table unpartitioned.

What is the best partition key for time-series data in ClickHouse?

Monthly partitioning with toYYYYMM(event_time) is the default for time-series tables. Daily partitions are reasonable only with a clear daily retention or backfill story, and high-cardinality keys like user_id or event_type are almost always wrong. Aim for dozens of partitions, tolerate hundreds, never thousands.

What causes the "Too many parts" error in ClickHouse?

Inserts fail with Too many parts when active parts per partition exceed parts_to_throw_insert, which defaults to 300. Over-partitioning is a leading cause because every insert batch is split into one new part per partition value it touches; short-term fixes are batching inserts and async inserts, and the long-term fix is usually repartitioning.

Can I change PARTITION BY on an existing ClickHouse table?

No, the partition key is fixed at CREATE TABLE time. To repartition without downtime, create a new table with the desired PARTITION BY, backfill it with INSERT ... SELECT one source partition at a time, copy the final delta, and swap with EXCHANGE TABLES (ClickHouse 21.7+).

How does daylight saving time affect ClickHouse partitions?

DateTime columns without an explicit timezone use the server timezone, so functions like toYYYYMM and toDate can assign rows near midnight to the wrong partition during DST transitions. Store timestamps as DateTime64(3, 'UTC') and apply local-timezone bucketing with toTimezone at query time.

Key Takeaways

Partitioning in ClickHouse is a data lifecycle tool, not a query optimizer. Use ORDER BY for query speed.
Default to monthly partitioning for time-series tables with retention. Skip partitioning for tables without a retention or archival story.
Keep partition cardinality in the dozens. Hundreds is risky. Thousands will hurt you.
Watch for Too many parts (default parts_to_throw_insert = 300); it is usually a partitioning or batching problem, not a setting problem.
Store timestamps as UTC and apply local-timezone bucketing at query time to dodge DST partition assignment bugs.
Repartition with INSERT ... SELECT into a new table and EXCHANGE TABLES for an atomic, zero-downtime swap.
Lean on DROP PARTITION, MOVE PARTITION, REPLACE PARTITION, and FREEZE PARTITION - these are the operations that justify partitioning in the first place.

When in doubt, start without partitioning. Adding it later is a migration; removing it once your team has built scripts around DROP PARTITION is a much harder conversation.

If you're unsure whether partitioning belongs in your schema, our ClickHouse consulting team can review it with you - ideally before the data volume makes changes expensive.