How to choose a ClickHouse partitioning key for your data
A partitioning key tells ClickHouse how to split a MergeTree table into separate groups of data parts, typically by time, so that whole groups can be dropped, moved or managed as units. It matters once your event, log or telemetry tables grow large enough that retention, deletion and tiering become routine operations. The most important point to settle first is that partitioning is primarily a data management tool, not a query speed lever. For filtering and reading less data, the main lever is the ORDER BY key, which determines how rows are sorted on disk. The partitioning key and the ordering key are separate decisions, and the sibling topics on choosing a primary and ordering key and on TTL retention cover the query side and the expiry side in more depth.
Your team's starting point is the retention rule. Write down how long each table must keep data, which operations you expect to run on it (dropping expired periods, restoring a single period from backup, moving old periods to cheaper storage) and how often they happen. A coarse time grain such as a month often matches retention and bulk deletion well, while a fine grain such as a day can suit high-volume tables with short retention. Keep the number of partitions modest, because each partition adds parts and metadata that the server must manage, and inserts that scatter across many partitions create more small parts for background merges to handle. Confirm the partition count and part behavior on a copy of your real data before you commit, and check the current guidance in the ClickHouse production FAQ for the version you run.
Align the partitioning key with your TTL expressions and with your backup plan. If retention deletes data by partition, the TTL and the partition grain should agree so that expiry drops whole partitions instead of rewriting parts row by row. Test a partition drop and a restore of one partition on a spare cluster, because the way you partition determines the granularity of your backups and recovery. Ask your platform owner to confirm which ClickHouse version you will run and how the partition expression behaves on it, since changes to partitioning and TTL behavior should be reviewed against the release notes before any upgrade.
Which partition key fits which data pattern
Each partition key trades retention precision against the number of parts ClickHouse must manage. The right choice depends on how long data must be kept, whether tenants leave the system, and how bulk loads arrive. Read each card against your own retention rules and data volumes before settling on a key.
No partitioning
A table with no PARTITION BY keeps all data in one logical partition. It avoids partition management entirely and can suit small reference or dimension tables. The main risk is that retention and deletion must run through row-level TTL or mutations, which rewrite parts instead of dropping them. Check whether your table is small enough that this cost stays acceptable and whether any future retention rule would require whole-period drops.
By day
Daily partitions fit event and log data with retention measured in days, because a whole day can be removed with a partition drop. The risk is partition count: a long retention window multiplied by several tables and tenants can create many active parts that merges must work through. Ask your team to estimate partitions per table for the full retention window and confirm the result with a test on representative data.
By week or month
Weekly or monthly partitions reduce the number of partitions for long retention windows, which helps when data is kept for years. The trade-off is coarser deletion: removing a month of data drops only when the whole period has expired, so retention can run longer than policy requires. Confirm with your compliance owner whether that overhang is acceptable, and align the period with how TTL and legal hold rules are written.
By tenant or source column
Partitioning by a tenant, customer or source identifier lets you drop one customer's data in a single operation when they leave, or reload one source without touching others. The main risk is unbounded cardinality: thousands of tenants create thousands of partitions with uneven sizes, and small tenants produce many tiny parts. Confirm the number of distinct values in production and whether any tenant will be large enough to dominate merges.
By combined key
A combined key, such as a date paired with a source or region, supports both time-based retention and per-source reloads. It multiplies partition count, so it only works when the component values are bounded and the combined count is tested. Compare the resulting number of partitions against your retention window and source list before committing, because changing the partition key later requires rebuilding the table.
Partitioning options and when each fits
This table compares common partition granularities (none, daily, monthly and tenant-based) across retention alignment, partition count growth, merge behavior and the operational tasks each one enables. Read each row as a decision: the right choice depends on how data expires, how many distinct values the partition column has, and how often you need to drop or move whole ranges.
| Option | Retention and data management | Partition count and merge behavior | Choose it when |
|---|---|---|---|
| No partitioning | TTL deletes rows individually, and dropping a whole range means a mutation or delete rather than a metadata-only operation | Few parts per table, but merges run across all data at once, so large tables carry more merge work per part | The table is small, retention is uniform, and you never need to drop a time range in one step |
| Daily partitions | Fine-grained drops and detaches by day, and TTL can remove whole partitions as they expire | Partition count grows by one per day of data, so the number of active parts and merges rises with retention length | Retention is short or per-day exports and corrections are frequent, and you can accept many partitions |
| Monthly partitions | Drops and detaches by month match common retention periods and reduce the number of objects to manage | Partition count stays modest for multi-year retention, and merges stay within each month's data | Retention is measured in months or years and you want few partitions with simple expiry |
| Tenant-based partitions | Dropping one tenant's data is a single operation, but cross-tenant time-based retention needs a separate TTL design | Partition count grows with the number of tenants, and a high tenant count multiplies small parts | Tenants are few, long-lived and removed as units, such as offboarding a customer |
| Composite (time plus tenant) | Supports both per-tenant drops and time-based expiry, with the most objects to track | Partition count is the product of tenants and time buckets, so it grows fastest and needs the most monitoring | You have a hard requirement for both per-tenant deletion and time-based retention, and have confirmed the partition count is acceptable |
The cost of too many partitions in ClickHouse
A partition key that is too fine or too high in cardinality multiplies the number of *parts* ClickHouse must manage, and that is where most partitioning mistakes become expensive. Each insert writes a new part for every partition its rows touch, so a key such as a per-user or per-minute value spreads one batch across many small parts. Background merges then have to combine these parts within each partition, and they can only merge parts that share a partition. The result is more parts to track, more work per query and a merge queue that can fall behind the insert rate. Partitions should be coarse enough that a typical insert batch lands in one or a few of them.
The symptoms usually appear first in ingestion. Operators see inserts slow down or start failing with "too many parts" errors, disk usage that looks higher than the data volume justifies, and queries that scan many small parts. To check this on a live cluster, query system.parts filtered to the active parts of the table and count them per partition and per table, then compare the count with the number of partitions. Check system.merges for long-running or piling-up merges, and system.replication_queue on replicated tables to see whether fetches and merges are backing up. Confirm the current guidance on part count and partition count limits for your ClickHouse version in the official documentation, and record a baseline before you change the key.
Test any partition change on a copy of the table with representative insert volume, not on an empty table, because part behavior depends on how data arrives. Measure insert latency, the number of active parts over a full day of ingestion, and merge lag. If the test shows the table needs a coarser key, plan the change as a table migration, since the partition expression cannot be altered in place for an existing table. Your team should own the decision, but a partner can run the copy test, capture the system table evidence and document the threshold you will monitor afterward.
How to test a partitioning key before production
This sequence turns a partitioning idea into a tested decision by checking it against retention rules, real query and ingestion patterns, and representative data in a copy of the cluster before any production table changes.
Write down retention and deletion requirements
The data owner and compliance lead list how long each dataset must be kept, what triggers deletion, and whether any deletion must be provable to an auditor. Those rules define the partition granularity that is worth testing, since partitions are usually dropped or expired as whole units. Done when every dataset has a written retention period and a deletion method agreed by the owner, and the team can say which deletions must happen by partition and which can happen by row.
Map query and ingestion patterns
The engineers who run dashboards, investigations and batch loads record the typical filters, time ranges and insert rates for each table, using the query log and the sources that write to ClickHouse. The goal is to see whether queries usually scan a narrow time window and how late data arrives. Done when the team has a short table of common filters, the largest time ranges queried, and the typical lateness of inserted rows.
Define candidate partition keys
Based on the first two steps, the architect writes two or three candidate
PARTITION BYexpressions, such as a monthly or daily date expression, and records the expected number of active partitions for each. Keeping the candidates coarse and few makes the later tests easier to compare. Done when each candidate has a written expression, a stated reason tied to retention or query pattern, and an estimate of partitions per retention window that the team can check in the next step.Load representative data into a copy
The team creates a test cluster or test database that matches the production table definitions, then loads a representative sample of production data that covers the full retention window and realistic late arrivals. Each candidate key gets its own table. The ClickHouse production guidance recommends a pre-production environment with representative data and queries, and this step is where that recommendation is applied to the partition decision. Done when each candidate table holds data across the full window and row counts match the source for the same period.
Check part counts and merge behavior
Using the
system.partstable, the team counts active parts per partition and per table, watches how parts merge after a bulk insert, and records the number of partitions created by a full retention window. Too many small partitions produce many parts and more merge work, so the candidate with the fewest partitions that still supports retention is usually the one to examine first. Done when each candidate has recorded part counts, merge timings and partition counts, and the team has ruled out any key that leaves many small parts unmerged under the expected insert rate.Rehearse a partition drop and a TTL run
On each candidate table, the team drops one old partition with
ALTER TABLE ... DROP PARTITIONand then runs a TTL rule that matches the retention requirement, confirming which rows and parts are removed and how long the operation takes. The check is whether the deletion matches the written requirement exactly, with no rows outside the window removed and no rows inside it lost. Done when the drop and TTL results match the expected row counts, and the team has confirmed any TTL edge cases such as late rows that arrive after expiry.Record the decision and its limits
The architect writes a short decision record that names the chosen partition key, the rejected candidates, the test results, and the conditions that would trigger a review, such as a change in retention or a shift in insert volume. The record is reviewed by the data owner and the platform team before production work begins. Done when the record is signed off, stored with the schema definitions, and linked from the production change request so the reasoning survives staff changes.
Security and operational considerations for partition design
Partition operations such as DROP PARTITION and DETACH PARTITION remove or move whole chunks of data in one statement. The controls below decide whether a partitioning scheme is safe to run in production, and each one should be designed before the first partition is created.
Restrict who can drop and detach
Partition drops and detaches are destructive and need no row-level check, so they belong in a narrow role. Use ClickHouse access control with users, roles and grants so that only a maintenance role can run ALTER TABLE ... DROP PARTITION or DETACH PARTITION. Restrict the default user, which starts with all permissions, before any application connects. Test the grants with a non-admin account on a copy of the schema to confirm that ingestion users cannot drop data by accident.
Back up before destructive partition work
Replication alone does not protect against human error. A dropped partition is removed on every replica of a ReplicatedMergeTree table, so a replica failover will not bring it back. Take a BACKUP of the affected partition or table to storage outside the cluster before any drop, and confirm the backup completed. Partition-level backup support makes this a targeted step rather than a full copy.
Prove backups restore
A backup that has never been restored will probably not restore cleanly when it is needed. Schedule restore tests of partition backups onto a spare cluster, and record the time and row counts each test produces. Include a restore of a single partition in the test, because that is the path a rollback after a bad drop would take. Ask the team who owns these tests and how often they run.
Audit partition maintenance activity
Partition maintenance should leave a trail that answers who dropped, detached or attached which partition, and when. Review query logs for DDL statements and keep the user and client address on each record. Alert on DROP PARTITION issued outside an approved maintenance window. Confirm that audit records are kept outside the cluster so an operator cannot erase them along with the data they changed.
Plan for Keeper during partition changes
Replicated tables coordinate partition operations through ClickHouse Keeper, so a Keeper outage can block DROP and ATTACH on replicated tables. The replication guide recommends at least three Keeper nodes on dedicated hosts in production, with about 4 GB of RAM each. Check Keeper health before a planned partition job, and avoid running large partition moves while a Keeper node is down or recovering.
Questions teams ask about ClickHouse partitioning keys
Can our own team choose a ClickHouse partitioning key without outside help?
Yes, if one engineer understands how MergeTree parts, merges and TTL interact and has time to test the choice on representative data. Most teams start from one question: how will old data be dropped or moved? Partitioning by month or by day usually follows retention rules, while the ordering key drives query pruning. The choice is harder when several tables share a cluster, when retention differs by dataset, or when data arrives late or out of order. In those cases, an outside review can compare options against your query logs and retention policy before anything is built, and your team keeps the decision record.
What does changing a partition key later involve?
Partitioning is set when a MergeTree table is created, so changing it normally means building a new table with the new key and moving data into it. The work includes creating the target table, copying or replaying data, checking row counts and query results, switching applications or views to the new table, and retiring the old one. Ingestion may need a pause or a dual-write period. The effort grows with the number of rows, the number of dependent materialized views and dashboards, and how much history must be kept. This is why testing the key on a copy before production data arrives is worth the time.
What drives the cost of a partitioning review?
The cost depends on the work the review must cover, not on a standard package. The main drivers are data volume and arrival pattern, the number of tables and materialized views that depend on them, the retention rules that partitions must support, and how much testing is needed on a copy of real data. A single table with clear retention needs a narrow review. A cluster with several datasets, tiered storage and legal retention rules needs more analysis and more test runs. Ask for a written scope that lists tables, environments, test cases and deliverables, and confirm which parts are one-time and which are repeated when data grows.
How long does a scoped partitioning review take?
Duration depends on how quickly the inputs are available and how many test cycles the scope includes. A review that starts from a clean, documented schema and a non-production cluster with representative data can be completed faster than one that must first collect query logs, reconstruct retention rules and set up a test environment. Shared production clusters, change approval windows and the number of teams who must sign off also extend the timeline. Your partner should state the timeline in the written scope, with the dependencies that could move it, such as access to a staging environment or data owners' availability.
What does our team need to provide for the review?
The review works best with three inputs. First, a representative sample of the data, including the time column, its arrival pattern and any late-arriving records, with personal or regulated fields masked or removed. Second, query logs or a list of the most common and most expensive queries, so filters on time and other columns can be measured. Third, your retention policy, with the period each dataset must be kept and any rules for deletion or legal hold. A named owner for each dataset should confirm the policy, and access to a non-production ClickHouse environment is needed for test runs.
Does partitioning alone make ClickHouse queries faster?
No. Partitioning mainly helps ClickHouse skip whole partitions when a query filters on the partition key, and it makes dropping or moving old data cheap. Query speed for most analytical workloads depends more on the ordering key, data types and skip indexes. Too many partitions can slow inserts and merges, and a partition key that does not match common filters adds little to query performance. Partitioning should be chosen for data management first, then checked with EXPLAIN and system.query_log on realistic queries. Treat any speedup you expect as something to measure, not assume.
How DeployOpen scopes and delivers ClickHouse schema work
DeployOpen works on partitioning keys as part of a wider schema review, starting from your retention rules, deletion needs and query patterns in your own infrastructure. We write the scope, build and test the table definitions on a copy of representative data, and hand over the decisions with the evidence behind them.
How we scope
- Engagements are fixed-scope, with the partition design, tables and deliverables agreed in writing first
- We list retention periods, delete and correction patterns, and the tables that will share a TTL policy
- Scope records which environments, data volumes and ClickHouse versions the design must cover
How we build
- We test candidate partition expressions on a pre-production copy with representative data and queries
- We count parts and partitions per table and compare insert and merge behavior across the candidates
- We confirm that TTL rules, partition drops and backup steps match the chosen granularity before sign-off
How we hand over
- You receive table definitions, the partitioning rationale and a record of the tests that were run
- Runbooks cover partition drops, TTL checks and what to do when part counts grow unexpectedly
- Your team walks through the design with us so the choice can be revisited when data volumes change
How we support
- Ongoing support, upgrade checks and schema reviews are available as a retainer
- Retainer work can re-test partitioning when a ClickHouse upgrade or new retention rule changes the trade-offs
- Ask any provider to confirm how they handle your test data, access and change records before work starts
