Choosing a ClickHouse primary key and ordering key for MergeTree tables
The ORDER BY key controls how ClickHouse sorts data on disk within each part, and it decides which queries can skip large blocks of data without reading them. It is hard to change later, because the sort order is fixed when data is written, so a poor choice often means building a new table and copying the data. For a self-managed team with large event or log tables, slow queries or faster-than-expected storage growth are the usual signs that the key deserves a review. In a MergeTree table, the ordering key is the ORDER BY clause, and it also defines the sparse primary index by default. The optional PRIMARY KEY clause sets only the index, which must be a prefix of the ordering key. Teams rarely need the two to differ, so start by treating them as one decision.
Review the key when dashboards or investigations filter on columns that the table does not sort by, when a new event type adds a high-volume filter, or when disk usage grows much faster than row counts. Start with the query log: list the columns that appear most often in WHERE clauses and check how many rows each query reads. Then confirm the candidate key with EXPLAIN indexes = 1 on representative queries, so you can see how many granules the index actually skips. Compare that with the current table definition in system.tables, which shows the sorting key in use. Do not assume that a column with a high-cardinality or a time value belongs first in the key without testing its effect on your own queries.
Before changing anything in production, build the candidate key on a copy of a representative slice of the data. Create a new table with the proposed ORDER BY, load the same rows with INSERT INTO ... SELECT, and run your slowest queries against both tables while comparing rows read and runtime. Use a pre-production environment with realistic data volumes, because compression and skipping behavior change with the data distribution. Record the on-disk size of each table from system.parts. Once the results are clear, plan the cutover as a separate change with a rollback path. Confirm the ClickHouse version and changelog for your target release through the ClickHouse production FAQ before you start.
Which key pattern fits which query workload
Event and log tables usually serve searches that name a tenant or service and a time window, then scan forward for matching rows. The key should put the columns that nearly every query filters on first, so ClickHouse can skip whole granules that fall outside the filter. In most multi-tenant logging designs that means the tenant identifier leads, followed by a coarse time column, with finer-grained fields such as severity or service name after time. Time-first keys are common when one tenant dominates and queries rarely name it, but they give up tenant isolation in the sort order.
- Tenant before time: Filtering on one tenant with a time range then reads a contiguous block of sorted data instead of touching every tenant's rows.
- Time as the second column: Range scans within a tenant become cheap, and recent-data queries stay localized.
- Low-cardinality fields next: Columns such as severity or environment narrow results after the first two without making the key too wide to compress well.
- Trade-off on free-text search: Keys do not help substring or token search in message bodies, so teams often add a skip index or a separate search layer for that.
- Trade-off on unfiltered queries: Dashboards that scan all tenants for a short window may not benefit, which is a reason to check whether they are common in your logs.
Check the WHERE clauses in system.query_log for your top 20 queries by read volume and confirm that the tenant and time columns appear in most of them before you commit to this shape.
Metrics tables are typically queried for one series or a small set of series over a time range, such as the latency of a service over the last hour. The natural key groups all rows for a series together and orders them by time, so a single-series query reads one contiguous run of data. Because the series identifier usually has far higher cardinality than severity or region, its position matters: placing it first keeps the series contiguous, while placing it later can scatter a series across the sort order and reduce skipping.
- Series identifier first: Groups each series into a contiguous run, which suits queries that name a metric and a host or device.
- Timestamp second: Within each series, rows sort by time, so range predicates skip efficiently.
- Metric name before labels: If queries always name the metric first, the metric name can lead, with the series identifier after it.
- Trade-off on cross-series aggregates: Fleet-wide queries that aggregate across all series by minute gain little from a series-first key and may benefit from a rollup or materialized view.
- Trade-off on series churn: Short-lived series, such as containers, spread rows across many small groups, which affects merge and compression behavior.
Confirm the proportion of queries that filter on a single series against those that aggregate across many, using your own query logs, before choosing between a series-first and a time-first key.
User-level analytics filters on a high-cardinality identifier such as a user, account, or device ID, often alongside a date range. The identifier is selective enough to make it a strong first filter, but it is also very wide in cardinality. Placing it first can make the key effective for per-user lookups while keeping each user's events together. The trade-off is that the sort order then favors one access pattern, and queries that do not name the identifier can read far more data than expected.
- Identifier first for per-user lookups: A query for one user's history reads a contiguous range of the sorted data.
- Date after the identifier: Each user's events sort by time, which supports recent-activity and range queries.
- Avoid very wide keys: Long or high-entropy composite keys increase index size and reduce the benefit of compression, so keep the key to the columns the queries need.
- Trade-off on population queries: Aggregations over all users by day may need a separate table ordered by date, or a materialized view.
- Trade-off on sort order versus projections: When both access patterns matter, a second ordering through a projection or a second table may be a better fit than one compromise key.
Review which filters appear in the queries that account for most of your read volume, and test both the identifier-first and date-first candidates against those queries on a copy of the table.
Filter columns first, then cardinality, then compression
These rules set the order of columns in an ORDER BY key. They pull in different directions, so each one trades a gain on one query pattern for a cost somewhere else. Test every rule against the queries your dashboards and investigations actually run before you commit to a key.
Put filter columns first
Columns used in equality or range filters in most queries belong at the front of the key, because ClickHouse uses the leading columns to skip granules it does not need to read. A column that appears only in GROUP BY or in rare ad hoc searches gains little from that position. Check which filters appear in the top queries by read volume before ordering the key, and confirm the leading column is one that most of those queries constrain.
Order lower cardinality columns earlier
Columns with fewer distinct values placed earlier in the key produce long sorted runs, which compress better and let range filters skip more data. Placing a high-cardinality column such as a request ID first spreads similar values apart and weakens both compression and skipping. The trade-off is that a low-cardinality column first can make a query that filters only on a later column read more data, so weigh this rule against the filter rule above rather than applying it alone.
Keep the key short
Every column in the ORDER BY key is stored in the primary index and affects how sorted data is laid out, and each added column makes inserts and merges do more sorting work. A short key of a few columns that cover the main access paths is easier to reason about than a long one that tries to serve every query. The trade-off is that a long key can avoid the need for projections or extra tables, so compare key length against the cost of a second table when a query pattern is common.
Avoid per-row volatile columns
Columns that change on every row, such as a unique trace ID, a random session token or a nanosecond timestamp with no query purpose, add bulk to the key without helping any filter. They make the sparse primary index large relative to the data and reduce compression of neighboring columns. Before adding such a column, ask which query filters on it and how often. If the answer is none, keep it in the table but out of the key.
Bucket time with care
Time is usually the most common filter in event and log tables, so it often leads the key, but a raw timestamp at full precision is very high cardinality and may not compress well as a sorting column. A coarser bucket such as a date or an hour can help, but it may leave rows from one bucket unordered within it. Decide whether the key needs the exact timestamp for ordering, and confirm the bucket width against the time ranges your queries usually request.
Key design options and when each fits
This table compares four common ordering key shapes for a MergeTree table, with the query pattern each suits, its main benefit and risk, and what to test on a copy. Read each row against your most frequent filters and your largest tables, then confirm the choice with representative queries before migrating production data.
| Key shape | Best fit query pattern | Main benefit | Main risk | What to verify on a copy |
|---|---|---|---|---|
| Tenant, then time | Queries that almost always filter to one tenant or customer and then to a time range | Data for each tenant is stored together, so per-tenant range scans read fewer granules | A few very large tenants can dominate parts and slow queries that span all tenants over time | Rows read for the top tenant filters, skew in row counts per tenant, and cross-tenant dashboards |
| Time, then tenant | Dashboards and retention jobs that scan recent time windows across all tenants | Time-bounded scans and TTL-style cleanup stay efficient, and recent data is grouped | Filtering on one tenant alone may read many more granules than a tenant-first key | EXPLAIN index usage for single-tenant queries, and read volume for the most common time window |
| Single timestamp | Simple time-range analysis with few or no equality filters on other columns | Easy to reason about and to maintain, with a clear time-based layout | Little pruning for filters on other columns, so high-cardinality lookups may scan widely | Read volume for each main filter column, and whether a secondary skip index is needed instead |
| Key with a hashed or bucketed column | High-cardinality identifiers such as device or session IDs, where equality lookups matter most | Spreads rows for very hot values and can narrow equality filters on the bucketed column | Range queries on the original value lose pruning, and bucket counts are hard to change later | Query plans for equality and range filters, insert distribution across parts, and whether the bucket count stays stable |
Testing a key change on a copy before migrating production
This sequence lets your team measure a candidate ORDER BY against real queries and real data on a copy of the table, so the production migration is planned from evidence rather than estimates. Each step ends with a check that decides whether to continue.
Capture representative queries from query log
Pull the most frequent and most expensive SELECT statements from system.query_log over a period that includes normal business load and known peaks. Group them by the table they read and by the filter columns they use in WHERE clauses. Your DBA or platform engineer runs this against the current production table, and the output is a short list of the top queries with their filters, read counts and execution times. You know the step is done when the list covers the dashboards and reports your teams depend on, and someone from the application or analytics side has confirmed nothing important is missing.
Build a copy table with the candidate key
Create an empty table with the same columns, types, engine and settings as production, changing only the ORDER BY clause to the candidate key. Keep partitioning and TTL aligned with production so the comparison isolates the sorting key. The engineer doing this writes the DDL in version control so it can be reviewed and reused. The step is complete when SHOW CREATE TABLE on the copy matches production except for the ORDER BY expression.
Load a representative sample into both tables
Insert the same data set into the copy and into a reference table built with the current key, using a sample that reflects the time range, cardinality and skew of production. Insert both from the same source query so the row sets are identical. A reviewer should confirm the sample includes busy and quiet periods and the high-cardinality columns that matter. The step is done when row counts match between the two tables and all parts have merged to a stable state.
Compare EXPLAIN output and rows read
Run each captured query against both tables and record the rows read, granules selected and whether the primary key condition is used, using EXPLAIN indexes = 1 and the read statistics in system.query_log. Engineers compare the candidate key against the current key query by query. The output is a table showing which queries read fewer rows, which are unchanged and which regress. The step is complete when every top query has been measured and each regression has an explanation or a decision to accept it.
Check compressed size with system tables
Query system.columns and system.parts for both tables to compare compressed and uncompressed bytes per column and in total. Sorting order affects compression, so the difference can be meaningful for storage and for I/O. Note that size alone does not decide the key, because a smaller table can still read more rows for your critical queries. The step is done when you have per-column sizes for both tables and a documented view of how the trade-off affects storage cost.
Validate query results match
Run the captured queries against both tables and compare result sets, including aggregates, counts and sums over the same time ranges. Differences usually point to a sampling or ingestion mismatch, not to the key, so resolve those before drawing conclusions. The validation owner signs off on the comparison. The step is complete when every top query returns identical results on both tables, or each difference is traced to a known cause.
Confirm the procedure and plan the migration
The ClickHouse production FAQ recommends a pre-production environment with representative data and queries, and it advises reading the changelog before upgrades. Before you change production, confirm with your ClickHouse version's documentation that the steps you used behave the same way on your server, and plan how to backfill or rebuild data under the new key with replicas kept available. Write a migration plan that includes a restore test using the backup documentation, a rollback path and the queries you will re-run afterward. The step is done when the plan has been reviewed by the people who own production and the restore test has passed on a spare cluster.
What a key change costs when data is already in production
The ORDER BY of an existing MergeTree table cannot be changed in place to rearrange the data already stored, so a key change in production is usually a rebuild: create a new table with the new key, copy the data across, validate the result, then switch writers and readers to the new table. Confirm what ALTER can do to the sorting key on your exact ClickHouse version before you plan around it, since the supported operations and their limits have changed between releases. Treat that check as the first task in the plan, and record the answer alongside the version you tested on.
The effort is driven by several factors. Data volume sets the copy time and the disk headroom you need for both tables at once. Ingestion continuity decides whether you pause writers, dual-write during the cutover, or accept a gap that must be backfilled from the source. Replica count multiplies the work, because the new table is replicated on every node and each replica must catch up. Dependent materialized views and projections must be recreated or repointed, and their own insert cost comes back into the budget. Downstream queries, dashboards and users that reference the old table name need to move, and anything that depends on the old key's sort order for performance needs retesting.
Plan rollback before the copy starts. Keep the original table, its data and its views intact until the new table has passed validation under real traffic, and write down which writers and readers switch first and how to switch them back. Confirm with your own environment how long the old table must remain queryable and who signs off on the cutover. A rollback path that has never been exercised on a copy is an assumption, so rehearse the switch and the reversal in the test environment described in the testing section before production data moves.
Operational and security considerations for a key migration
Changing an ordering key means building a new table and moving data, which creates a second copy of sensitive data with its own permissions, retention rules and failure modes. The cards below cover the controls to set before, during and after cutover, so the migration does not weaken the access model or leave you without a recoverable source.
Access rights on the copy
The rebuilt table and any temporary copy inherit nothing you have not granted, so check who can read and write them before any data arrives. Use named roles and row policies from the access control model rather than granting the migration user broad rights, and remove those grants once cutover is verified. Confirm that the default user, which starts with all permissions, is restricted in the target environment.
Retention and TTL during copy
A copy made with INSERT SELECT does not automatically carry over the table's TTL clauses, storage policy or retention intent, so the new table can keep data longer or shorter than the source. Compare the source definition with SHOW CREATE TABLE output and restate every TTL expression on the new table. Also decide whether historical rows older than the retention window should be copied at all.
Source backups before cutover
Take a full BACKUP of the source table before the switch, and store it outside the cluster that will be changed. The backup documentation notes that replication does not protect against human error or corruption, so a bad copy that replicates to every replica is still a loss. Record the backup identifier and restore it on a spare cluster before you rely on it.
Replica consistency during the switch
Run the rename or swap while every replica of the new table is healthy, and check replication queues and ClickHouse Keeper status before and after. A replica that is behind or offline can serve old or missing rows after cutover. The replication guide says a recovered replica syncs missing data from a healthy one, but confirm that sync finished before declaring the switch complete.
Audit log of the change
Keep a written record of the DDL statements, the schema version, the row counts compared, the operator and the approval for the migration. Store it in your change management system, not only in server logs that may rotate. An auditor or incident responder will want to know which ordering key was active on a given date and why it changed.
Query behavior after cutover
Watch query latency, rows read and error rates for the dashboards and APIs that depend on the table for at least one full reporting cycle after cutover. Compare against baselines taken from the source table. Confirm that queries relying on the old ordering still filter on the new key columns, and keep the old table available for a defined rollback window.
Common questions before changing a ClickHouse ORDER BY key
Can we change the ORDER BY key on an existing ClickHouse table?
Not in place. The sorting key of a MergeTree table is fixed when the table is created, so changing it means creating a new table with the new key and moving the data into it. The usual path is to create the new table, copy rows with INSERT ... SELECT in batches (partition by partition is easiest to verify), point writers and readers at the new table during a planned window, and keep the old table until the new one has been checked. The cost depends on table size, how many materialized views and downstream queries reference the old table, and whether writes must continue during the copy. Ask any partner to list every dependency before quoting.
How do we know which columns to put first in the key?
Start from the queries, not the table. Columns that appear in WHERE filters on almost every query and that narrow the data a lot usually belong first, followed by columns used for grouping or time ranges. Low-cardinality columns generally come before high-cardinality ones so that neighboring rows compress and the sparse primary index stays effective. Pull the real filter patterns from system.query_log over a representative period, then compare candidate keys on a copy using rows read and query duration. Ask for a written list of the query shapes each candidate was tested against.
Does the primary key have to equal the ORDER BY key?
No, but it is a prefix of it by default. In MergeTree, the PRIMARY KEY is optional and, when omitted, equals the ORDER BY clause. You can declare a shorter PRIMARY KEY that is a prefix of ORDER BY, which keeps the in-memory index small while still sorting data on the wider key. Because the sparse index only helps filters on the leading columns of the primary key, a filter on a later column can still scan most of the table. Confirm the exact behavior for your ClickHouse version in the official documentation before relying on it.
How do we test a new key without touching production data?
Build a copy of the table with the candidate key on a separate cluster or a non-production node with representative data. Representative means a slice that covers the same time ranges, skew and cardinality as production, not a small sample of recent rows. Replay a captured set of real queries against both tables, and compare rows read, bytes read, memory and latency from system.query_log, plus the on-disk size from system.parts. Keep the test on the same ClickHouse version you plan to run in production, because the optimizer and compression behavior can differ between releases. Ask for the test queries and the comparison report as deliverables.
What effort and cost drivers apply to a key migration?
Most of the cost is analysis and verification, with copy time as a smaller part. Main drivers are table size and daily write volume, the number of tables and materialized views that read from or write to the table, how many query patterns must be measured, whether the table is replicated across shards, and how strict the cutover window is. Recurring costs are small unless you adopt new retention or rollup patterns at the same time. When requesting a quote, ask for a fixed scope with named tables, the test method, the cutover plan, rollback steps and the post-cutover monitoring period, all in writing before work begins.
What does our team need to provide for a key migration?
You need someone who owns the table's queries and schema, someone who can grant access to query logs and a non-production environment, and a named approver for the cutover window. Your team should also provide the list of writers, readers, dashboards and materialized views that touch the table, and recent backups or a verified backup path before any copy starts. Without the query inventory, the test cannot show whether the new key helps the workloads that matter. Confirm these responsibilities in the scope document so that dependencies do not surface during cutover.
Can our own engineers change the key without outside help?
Yes, if they have hands-on ClickHouse experience and the table is small enough that a copy and a short cutover carry low risk. The technique is standard, but the risk sits in what is easy to miss: materialized views that write to the old table, dictionaries or external jobs that reference it, and replicas that must stay consistent during the copy. Teams often use an outside partner for the test design, the dependency inventory and the rollback rehearsal, then run the cutover themselves. Decide which parts you will own before starting, and make sure someone has restored a backup in a test environment first.
How do key choices interact with partitions and skip indexes?
Partitioning and skip indexes depend on the key, so they should be designed together. Partitions are usually chosen for retention and data management, and they do not replace the ordering key for query pruning. Skip indexes help only after the ordering key has narrowed the data, so a poor key can leave them with little to skip. Changing the key often means revisiting partitions and indexes, and in some cases a materialized view or projection may be a better choice for a second access pattern. Include partitioning, TTL and index changes in the same test so you measure their combined effect.
How DeployOpen scopes and delivers ClickHouse key reviews
A key review or key migration starts with a written scope, and DeployOpen agrees that scope before any work begins. The first input is evidence from your own system: the CREATE TABLE statements for the tables under review, the current ORDER BY and PARTITION BY clauses, and a sample of query logs from system.query_log covering the dashboards, ad hoc queries and ingestion jobs that matter most. From these, the team identifies which filter columns dominate, how many distinct queries touch each table, and which tables hold enough data for a key change to matter. The scope also records what is out of bounds, such as application changes, replica topology changes or retention policy changes, so that the quote reflects the actual work. DeployOpen does not publish a price list. A quote follows from the written scope, and the cost drivers you should ask about are the number of tables, the number of distinct query patterns, the data volume to copy for testing, and whether you need a migration runbook or only a recommendation.
The test copy is built inside your infrastructure, in your cloud accounts, data centers or restricted network, so production data does not leave your control. Representative data matters more than volume alone: the copy should include the skewed tenants, time ranges and high-cardinality columns that your queries actually hit. The written deliverables typically include the proposed key for each table, the reasoning tied to measured rows read and compressed size, the before and after EXPLAIN output for the critical queries, and a migration plan that uses a new table and a controlled data copy rather than an in-place change, since ClickHouse does not let you alter the sorting key of an existing MergeTree table in that way. Before any migration step, confirm with your team the rollback path and the restore test described in the backup guidance, because a key change that cannot be reversed needs a verified backup first. The timeline is agreed with the deliverables and depends on the scope you approve, so ask for it in the written proposal rather than assuming it.
Once the key is live, query patterns and data volumes keep changing, so many clients add a retainer for ongoing tuning. A retainer can cover periodic review of system.query_log for new slow query patterns, checks on whether new tables should follow the same ordering conventions, and support during ClickHouse version upgrades, where the ClickHouse production FAQ recommends testing on a pre-production environment with representative data and queries. Ask for the retainer scope in writing, including response expectations and what happens when a query regression is found outside the agreed hours. Keep in mind that DeployOpen prepares evidence and recommendations for your decision; the performance results you see in your own environment are the measure that counts.
