I Finally Understood Why Our ClickHouse Cluster Was Melting (And Why Partitioning Isn't What I Thought It Was)

Six months ago, I inherited a ClickHouse deployment that was supposed to handle analytical queries for an e-commerce platform. It was slow. Not "slightly slower than expected" slow—I'm talking about queries that should finish in seconds taking 10+ minutes. The first thing everyone blamed was indexing. We didn't have enough indexes. So we added more. It didn't help.

Turns out, we had a partitioning disaster on our hands. Our events table was partitioned by user_id, which meant we had millions of tiny partitions. ClickHouse wasn't struggling because it couldn't find data—it was drowning in metadata overhead and merge operations that never completed. This is the kind of mistake that makes you realize you fundamentally misunderstood how the database works.

Reading about petabyte-scale partitioning strategies forced me to confront something uncomfortable: I'd been treating partitions like traditional database indexes, and I was completely wrong. Let me share what I've learned, because I suspect I'm not alone in this.

Partitions Are Not Indexes (This Was My Biggest Misconception)

Here's the thing that changed how I think about ClickHouse: partitions are a data organization mechanism, not a query acceleration mechanism. This distinction matters enormously.

When I partition a table, I'm telling ClickHouse how to physically organize data on disk. It's a storage architecture decision, not a query optimization decision. The actual query acceleration comes from the sorting key—the ORDER BY clause. I got this backwards for years.

In my broken deployment, I thought partitioning by user_id would make user-specific queries faster. What actually happened: ClickHouse created millions of partitions, each one becoming a separate file on disk. During merge operations (which ClickHouse does automatically), the system had to coordinate across all these tiny pieces. It was like organizing a library by putting every book from every author in a separate shelf—technically organized, practically unusable.

Why Time-Based Partitioning Actually Makes Sense at Scale

After fixing our cluster, I became a believer in time-based partitioning. And it's not arbitrary. There are real reasons this pattern dominates production systems.

Time-based partitioning aligns with how analytical workloads actually work. Most queries filter by time ranges: "Give me events from last week" or "Analyze user behavior in January." When your partition key matches your query patterns, ClickHouse can skip entire partitions—this is partition pruning, and it's one of the few times partitioning directly improves performance.

The operational benefits are equally important. With monthly partitions, I can drop old data with a single metadata operation instead of issuing massive DELETE statements. At petabyte scale, this is the difference between a 10-second operation and a multi-hour nightmare.

Here's what we settled on:

CREATE TABLE events (
    event_time DateTime,
    user_id UInt64,
    country LowCardinality(String),
    event_type String,
    properties String
) ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id)
SETTINGS index_granularity = 8192;

Monthly partitions work for our 500GB daily ingest rate. Daily partitions would create too much metadata fragmentation; yearly would make retention management a nightmare. The granularity matters, and it depends on your specific volume and retention needs.

The Multi-Dimensional Case (Where I Got Creative)

For our multi-tenant deployment, we experimented with composite partition keys:

PARTITION BY (tenant_id, toYYYYMM(event_time))

This let each tenant's data live in separate partitions while preserving time-based pruning. The trade-off: we now have roughly 12 times more partitions, but each tenant can be managed independently. It's a worthwhile trade-off when you're dealing with SLAs across different customers.

However—and I learned this the hard way—avoid high-cardinality columns in partition keys. Using user_id or device_id creates an explosion of partitions. Your merge backlog balloons. Your monitoring systems start screaming. It's worse than useless; it actively degrades performance.

My Take: Monitoring Matters More Than Design

Here's what nobody emphasizes enough: you need to actively monitor partition health. I now check system.parts regularly to track active partition counts, average part sizes, and merge backlogs.

When I see the part count climbing or merges falling behind, that's a signal something is wrong with my partitioning strategy. Too many partitions for my data volume? Wrong granularity? I catch these issues early now, instead of discovering them when the database is on fire.

The design decision itself is important, but ongoing observability is what prevents disaster at scale.

What I'm Wondering

One thing I haven't fully resolved: what's the right approach for systems where you genuinely need multi-dimensional querying patterns that don't fit neatly into time-based partitions? I'm curious how others handle this without creating partition explosions.

Source: This post was inspired by "Day 71 - Advanced Partitioning Strategies for Petabyte-Scale Tables" by Dev.to. Read the original article