ClickHouse partitioning and TTL rules manage data lifecycle at different layers. PARTITION BY groups MergeTree data into coarse operational units; TTL rules automate retention and other lifecycle actions. Neither should be used as a substitute for a well-designed ORDER BY key.
For production analytics, the useful question is how to make retention, deletion, and maintenance predictable without creating thousands of tiny partitions or forcing queries to scan poorly ordered data.
This guide owns ClickHouse partitioning and TTL decisions. For row ordering and query pruning, use ClickHouse Query Performance: ORDER BY, Keys & Memory Trade-Offs. For broader architecture, use ClickHouse Architecture for Production Analytics.
Partitioning, ordering, and TTL have different jobs
| Mechanism | Primary job | Common misuse |
|---|---|---|
ORDER BY | Physical row ordering and data skipping | Treating it as presentation sorting |
| Primary key | Sparse index used to skip ranges | Copying OLTP primary-key patterns |
PARTITION BY | Coarse lifecycle/maintenance boundary | Creating a partition for every tenant or day without need |
| TTL | Automated retention/lifecycle action | Using it without capacity and deletion monitoring |
The most important rule is: partition for operations; order for queries.
A query can still perform poorly inside a perfectly chosen partition if the sorting key does not match the dominant filters.
Partition only when a coarse boundary has operational value
A good partition key helps with operations such as:
- dropping old periods efficiently;
- detaching or moving a defined data slice;
- managing retention by month or another coarse period;
- isolating data with a real lifecycle boundary.
For time-series analytics, monthly partitions are often easier to operate than daily partitions when the workload does not require daily administrative boundaries.
For example:
PARTITION BY toYYYYMM(event_time) ORDER BY (account_id, event_time)
Here the partition groups data by month for lifecycle work, while ordering by account and time helps queries that filter on account and time ranges.
The partition key should come from a lifecycle requirement, not from a desire to speed every query.
Too many partitions create metadata and merge overhead
High-cardinality partition keys can create too many small independent data sets.
Problematic examples include partitioning directly by:
- customer ID when there are thousands of customers;
- request ID;
- user ID;
- a high-cardinality event property;
- overly granular time buckets for modest datasets.
Each partition can contain its own parts and merge work. Excessive partitions make part counts, metadata, inserts, and maintenance harder to operate.
A better design often keeps tenants together in a coarse time partition and puts tenant/account fields early in ORDER BY.
That preserves query pruning without creating a separate partition for every tenant.
TTL makes retention explicit in table design
ClickHouse TTL expressions can automate lifecycle actions such as deleting data after a specified age.
A simple retention rule can look conceptually like:
TTL event_time + INTERVAL 90 DAY DELETE
This expresses a business rule: data becomes eligible for deletion after 90 days.
Use TTL when:
- retention is predictable;
- data age is encoded in a reliable timestamp;
- expiration should happen continuously rather than through manual partition drops;
- the team can monitor storage and background merge activity.
Do not choose 90 days, 30 days, or any other duration from convention. Tie it to product, recovery, and data-governance requirements.
TTL deletion is asynchronous, not an exact wall-clock delete
TTL work is applied through background merges. Rows do not necessarily disappear at the exact second their TTL expires.
This matters operationally:
row reaches TTL → becomes eligible → background merge processes eligible data → storage is reclaimed
If storage is tight, do not assume expired data will free space immediately.
Monitor:
- disk growth;
- merge backlog;
- part count;
- oldest retained data;
- TTL completion behavior;
- available capacity during traffic peaks.
TTL is a policy. Merge capacity determines how quickly that policy is physically realized.
Partition drops are faster for large coarse deletions
When all data in an old partition can be removed together, dropping a partition can be operationally simpler than row-level TTL deletion.
This makes a coarse time partition valuable for datasets with clean retention boundaries.
Example:
monthly partitions Jan 2026 Feb 2026 Mar 2026 ...
If the business retention rule says everything before February can be deleted, removing a whole January partition avoids rewriting unrelated newer rows.
TTL and partition drops can coexist: TTL handles continuous lifecycle, while partitions provide an efficient administrative unit for large boundaries and maintenance.
Partition pruning helps only when filters use the partition expression
Queries can skip whole partitions when predicates align with the partition key, but partition pruning should be treated as a secondary optimization.
A monthly time partition helps queries constrained to specific months. It does not make an account-only query efficient if every month still needs to be read and the ORDER BY key does not help.
Design priority should usually be:
dominant query filters → ORDER BY → lifecycle / maintenance need → PARTITION BY → TTL
That order prevents partitioning from compensating for the wrong sorting key.
TTL can also support movement and tiering in some designs
ClickHouse TTL features can be used for lifecycle actions beyond deletion, depending on storage policy and deployment architecture.
The strategic question is whether older data needs:
- deletion;
- movement to a different volume;
- a lower-cost storage tier;
- aggregation before raw detail expires.
Each additional lifecycle stage increases operational complexity. Use tiering only when the retained data volume and access pattern justify it.
For small teams, a simple, observable retention rule is often safer than a multi-stage policy that few people can explain during an incident.
Retention must preserve recovery and business needs
Before shortening TTL, identify whether old data is needed for:
- customer-facing history;
- model/trend analysis;
- incident investigation;
- regulatory or contractual retention;
- rebuilding materialized views;
- correcting historical aggregates;
- replaying ingestion after pipeline bugs.
A short TTL can lower storage cost and still create a much larger recovery problem later.
If raw data must be retained longer than the ClickHouse operational dataset, consider exporting or archiving it before expiry.
TTL and materialized views need coordinated lifecycle rules
Derived tables may need a different retention period from raw source data.
For example:
raw events → 30 days hourly aggregate → 12 months monthly aggregate → multiple years
That can be efficient when detailed events are useful only for recent troubleshooting while long-term reporting needs coarser summaries.
But verify derived data before raw data expires. Once the only detailed source is deleted, rebuilding or correcting aggregates becomes harder.
Use ClickHouse Materialized Views for the pre-aggregation design.
Managed ClickHouse reduces lifecycle operations but not retention ownership
Raff Managed ClickHouse reduces host-level database operations and provides managed storage, monitoring, backups, scaling workflows, and optional HA.
The data team still decides:
- partition key;
ORDER BYkey;- TTL duration;
- archival requirements;
- which derived tables outlive raw data;
- when a lifecycle change is safe.
At Raff, the operational rule is: do not partition more finely than the lifecycle needs, and do not expire source data before you can prove the business no longer needs it for recovery or rebuilds.
Partitioning and TTL checklist
-
ORDER BYis designed from query filters first. - Partitioning has a real lifecycle/maintenance purpose.
- Partition cardinality stays coarse.
- Tenant/user IDs are not used as high-cardinality partitions without a hard requirement.
- TTL duration is tied to business retention.
- TTL behavior and merge backlog are monitored.
- Storage headroom does not rely on instant TTL deletion.
- Large historical deletes can use partition boundaries where appropriate.
- Derived-table retention is coordinated with raw-data TTL.
- Archive/recovery needs are confirmed before shortening retention.
Conclusion
ClickHouse partitioning and TTL are lifecycle tools, not replacements for physical query design.
Keep partitions coarse and operationally meaningful, use ORDER BY for the dominant query path, and make retention explicit with TTL only after recovery and business-history needs are understood. This keeps MergeTree data easier to query, merge, delete, and operate as it grows.