Databend Cluster Key Best Practices: Choosing Columns, Key Order, and Granularity

This is Part 2 of the Databend Cluster Key series, written for data engineers who are familiar with SQL predicates and basic OLAP query analysis.

• Part 1 — How pruning works: From traditional partitions to micro-partitions, and how Snowflake and Databend reduce data scanning.

• Part 2 — Best practices (this post): How to decide whether a table needs clustering, then choose columns, key order, and granularity from a real workload.

Part 1 explains why Cluster Keys can reduce data scanning. This post addresses the harder operational question: given a large production table, which expressions belong in

CLUSTER BY

The Baseline: A Two-Day Query Scans 62.9% of All Blocks

The experiment uses

test.hits

• 100 million event rows;

• 105 columns;

• approximately 10.6 GiB after compression;

• 17 distinct dates in July 2013;

• no explicit Cluster Key in the baseline layout;

• 9,044 Blocks across 10 Segments.

For this experiment, the baseline explicitly sets

ROW_PER_BLOCK = 10000

Start with a two-day range query:

SELECT count(*)
FROM hits
WHERE eventdate >= '2013-07-02'
  AND eventdate <  '2013-07-04';

The predicate matches approximately 13.54 million rows, but

EXPLAIN ANALYZE

segments:  <range pruning: 10 to 10>
blocks:    <range pruning: 9044 to 5693>
read rows: 63,526,217

These numbers tell us four things:

1. Segment Range Pruning eliminates no Segments.

2. Block Range Pruning reduces 9,044 Blocks to 5,693.

3. The query still scans 62.9% of all Blocks.

4. Databend reads roughly 4.7 times as many rows as the query actually matches.

The SQL predicate is not the problem. The physical layout is. Many Blocks have

EventDate

Now consider a query closer to a real analytics workload: finding the most popular URLs for a specific counter over the same period.

SELECT
    url,
    count(*) AS page_views
FROM hits
WHERE counterid = 7525
  AND eventdate >= '2013-07-02'
  AND eventdate <  '2013-07-04'
  AND dontcounthits = 0
  AND isrefresh = 0
  AND url <> ''
GROUP BY url
ORDER BY page_views DESC
LIMIT 10;

The baseline pruning path is:

segments:  <range pruning: 10 to 10>
blocks:    <range pruning: 9044 to 5667,
            bloom pruning: 5667 to 243>
read rows: 2,862,793
read size: 30.80 MiB

Two complementary mechanisms are at work:

• Range Pruning uses Block Min/Max statistics to eliminate value ranges that cannot match.

• Bloom Pruning further eliminates Blocks that cannot contain values used in equality predicates.

A Cluster Key primarily improves Range Pruning. By reorganizing rows, it narrows the Min/Max interval of each Block, allowing more Blocks to be rejected before they are read.

High scan ratios indicate room for improvement, but they do not prove that clustering will pay for itself. The first decision is whether this table is worth clustering at all.

Step 1: Decide Whether the Table Is Worth Clustering

A large row count alone does not justify a Cluster Key. Initial data reorganization and ongoing maintenance consume compute, I/O, and temporary storage. The cumulative query-side savings must outweigh those costs.

A table is more likely to benefit when most of the following are true:

• It is large enough to contain many Blocks that could potentially be pruned.

• Frequent queries read only a small subset of the table.

• Existing Range and Bloom Pruning still leave a large scan footprint.

• The most expensive queries share one or two stable filter dimensions.

• Those queries run often enough for per-query savings to accumulate.

• Ingestion order and update patterns will not make clustering prohibitively expensive to maintain.

The incremental benefit is usually limited when:

• The table has only a small number of Blocks.

• Frequent queries need most of the data anyway.

• Data already arrives in the order used by the main filters.

• Expensive queries have no stable, shared access path.

• Queries that could benefit run infrequently.

• Backfills, out-of-order ingestion, or frequent DML continually disrupt the layout.

One subtle but important point is that a table without an explicit Cluster Key is not necessarily unordered. If data arrives in time order and most queries filter by time, existing Block ranges may already be compact. Explicit clustering may then deliver little additional pruning while still imposing maintenance work.

Evaluate the economics over a representative period rather than looking at one faster query:

query frequency × scan reduction per query
                   >
initial reorganization + ongoing clustering cost

In the

hits

Step 2: Choose Candidate Columns from the Workload

Wide tables contain many business-critical columns, but business importance does not imply pruning value. Candidate expressions should come from query history, not from browsing the schema and guessing.

A practical sequence is:

1. Identify parameterized query families with high execution frequency and cumulative scan volume.

2. Extract the predicates those queries consistently share.

3. Evaluate the selectivity and access pattern of each predicate.

4. Create a small number of candidate Cluster Key expressions.

5. Validate them with controlled experiments.

For

hits

EventDate

EventTime

CounterID

RegionID

UserID

WatchID

URLHash

IsRefresh

DontCountHits

The main queries in this workload filter on

CounterID

CounterID

EventDate

EventTime

Low cardinality does not automatically make a good key

In this dataset,

IsRefresh = 0

The relevant question is not whether a column is popular, important, or low-cardinality. It is:

When a common value or range is queried, can this expression help Databend eliminate most Blocks?

Frequency, business relevance, and cardinality are useful screening signals. None of them replaces selectivity analysis and scan validation.

Step 3: Match Time Granularity to the Query Window

Time is one of the most common Cluster Key candidates and one of the easiest to choose by intuition alone. A raw timestamp, hour, day, week, and month create very different physical organizations. Finer is not always better, and coarser is not automatically cheaper.

Start by estimating the cardinality of candidate expressions:

SELECT
    min(eventdate) AS min_date,
    max(eventdate) AS max_date,
    approx_count_distinct(eventdate) AS approx_days,
    approx_count_distinct(to_start_of_hour(eventtime)) AS approx_hours,
    approx_count_distinct(counterid) AS approx_counters,
    approx_count_distinct(userid) AS approx_users
FROM test.hits;

The dataset has the following distribution:

Date range: 2013-07-02 to 2013-07-31
Distinct dates: 17
Distinct hours: approximately 406
Distinct CounterIDs: approximately 6,477
Distinct UserIDs: approximately 17.97 million

Because the table covers only one month, this expression is almost constant in the experiment:

CLUSTER BY (to_start_of_month(eventtime))

It provides almost no additional partitioning. More plausible candidates are:

CLUSTER BY (eventtime)
CLUSTER BY (to_start_of_hour(eventtime))
CLUSTER BY (eventdate)

Each favors a different access pattern:

• Raw

  EventTime
  

  preserves the finest time order and has the highest cardinality.

• to_start_of_hour(eventtime)
  

  better matches minute-to-hour analysis.

• EventDate
  

  is a natural fit for one-day to multi-day range queries.

The important queries in this experiment span one to several days, so the candidate tests use

EventDate

At minimum, time granularity should account for:

typical query window
+ number of Blocks covered by each time bucket
+ whether data naturally arrives in time order
+ frequency and size of historical backfills

Expressions must preserve the ordering you want to optimize

Functions such as

to_start_of_hour

to_date

to_start_of_month

The same principle applies to strings. If the meaningful order is in the prefix,

substr(column, 1, N)

Step 4: Test Composite Key Order Under Controlled Conditions

Once both

EventDate

CounterID

Databend organizes a multi-column Cluster Key lexicographically. These definitions contain the same columns but optimize different access paths:

-- Build global date ranges first, then subdivide by CounterID.
CLUSTER BY (eventdate, counterid)

-- Build global CounterID ranges first, then subdivide by date.
CLUSTER BY (counterid, eventdate)

Experimental setup

The same 100 million rows were organized into three layouts:

(eventdate, counterid)

(counterid, eventdate)

All tables use

ROW_PER_BLOCK = 10000

v1.2.932-nightly

The purpose is to compare changes in pruning and scan volume caused by physical layout. Wall-clock time is too sensitive to hardware, concurrency, cache state, and background tasks to serve as the primary selection criterion here.

After loading and reorganizing the data, the three layouts contain 9,044, 9,261, and 9,258 Blocks, respectively. Reorganization changes Block and Segment boundaries, so the comparison includes pruning at each level, final Block ratios, and actual read volume rather than relying on absolute Block counts alone.

Query 1: Date filter only

(eventdate, counterid)

(counterid, eventdate)

The date-first layout retains fewer Blocks for a date-only query, as expected from the leading column.

Query 2: Counter filter only

SELECT count(*)
FROM hits
WHERE counterid = 7525;

(eventdate, counterid)

(counterid, eventdate)

The counter-first layout is stronger during Range Pruning. Once Bloom Pruning runs, however, the two candidates retain 75 and 84 Blocks, narrowing the final difference substantially.

Query 3: Counter and date filters together

(eventdate, counterid)

(counterid, eventdate)

When both predicates are present, the two candidate layouts retain 38 and 44 Blocks and produce similar scan volumes.

Why this workload chooses date first

The three queries reveal the leading-column effect:

• Date first provides a clear advantage for date-only queries.

• Counter first has stronger Range Pruning for counter-only queries.

• Bloom Pruning reduces the final difference between the two layouts for equality filters.

• Both layouts perform similarly when queries provide both predicates.

Given the relative importance of these query families, the selected layout is:

CLUSTER BY (eventdate, counterid)

The reason is not that time should always come first. Date first provides a better compromise for this workload: it improves date-only scans more, retains the benefit of Bloom Pruning for counter-only queries, and scans slightly less in the combined query.

A reasonable starting hypothesis is to place a commonly used range column before a high-cardinality business key, provided both have demonstrated pruning value. If precise high-cardinality lookups dominate the workload, test the reverse order instead of applying that pattern mechanically.

Step 5: Handle High-Cardinality IDs and Long Strings Carefully

High cardinality is not an automatic rejection criterion. Maintenance cost depends on how values are generated, how data arrives, and whether queries usually constrain the leading key columns.

Monotonic and random IDs behave differently

• Monotonically increasing or time-ordered IDs tend to append near the end of an existing range and are easier to keep locally ordered.

• Random UUIDs, trace IDs, and hashes continually land in historical ranges. This increases overlap between old and new Blocks and creates more Recluster work.

Do not place a random, high-cardinality column at the front of a Cluster Key only because equality filters are common. Test it when full-history point lookups are a core, frequent workload and Range Pruning adds enough value beyond Bloom Pruning to justify the maintenance cost.

Later columns depend on leading predicates

The

hits

UserID

CLUSTER BY (counterid, eventdate, userid)

Within

CounterID = 62

EventDate = '2013-07-15'

CounterID + EventDate

CounterID + EventDate + UserID

UserID

When a query supplies the first two key columns, Databend first narrows the scan to a continuous CounterID and date range. Adding

UserID

With

UserID

This is the key lesson:

A composite Cluster Key is not a set of independent indexes. The value of a later high-cardinality column depends heavily on whether queries also constrain its leading columns.

Distinguish string Cluster Key statistics from ordinary column statistics

Long strings such as

URL

Referer

CLUSTER BY (substr(column, 1, N))

VARCHAR STATS_TRUNCATE_LEN N

Approximate prefix cardinalities for

hits.URL

substr(url, 1, 8)

substr(url, 1, 16)

substr(url, 1, 32)

url

Using

CLUSTER BY (url)

If the workload's filtering semantics align with a prefix, test an explicit expression:

CLUSTER BY (substr(url, 1, N))

The first 32 characters produce an approximate cardinality of 2,115,680 in this dataset, but

N = 32

Changing

STATS_TRUNCATE_LEN

substr

CLUSTER BY

Step 6: Validate Layout, Scanning, and Maintenance Cost

Cluster Key validation has at least two technical layers and one economic layer. Query latency alone is noisy, while

average_depth

Inspect physical organization with

clustering_information

SELECT
    cluster_key,
    info:total_block_count,
    info:constant_block_count,
    info:average_overlaps,
    info:average_depth,
    info:p95_depth,
    info:p99_depth
FROM clustering_information(
    'database_name',
    'table_name'
);

These metrics help answer:

• How many Blocks exist?

• How many have reached a Constant state?

• How many Block ranges overlap on average?

• Across how many overlapping ranges does the same key value appear?

• Does high Depth come from disordered data or from hot values distributed across multiple Constant Blocks?

The experiment produces the following results:

(eventdate, counterid)

(eventdate, counterid)

(counterid, eventdate)

(counterid, eventdate, userid)

Three cautions matter when interpreting these values.

High Depth can reveal an improvable, disordered layout. In the baseline evaluated against

(eventdate, counterid)

average_depth

average_depth

Remaining Depth can come from hot values that cannot be split further. In both two-column layouts, approximately 76.5%–77.0% of Blocks are Constant: the complete Cluster Key has the same Min and Max within each Block. If one hot key genuinely fills several size-limited Blocks, those identical point ranges still count toward overlap and Depth. They are already a terminal Recluster state; rewriting them again cannot remove the overlap caused by the actual data distribution.

Depth is not directly comparable across different key definitions. The three-column layout has an

average_depth

UserID

UserID

clustering_information

Measure real scans with

EXPLAIN ANALYZE

Read the pruning pipeline in this order:

Segments before and after Segment Range Pruning
        ↓
Blocks before and after Block Range Pruning
        ↓
Blocks before and after Bloom Pruning, when applicable
        ↓
Read Rows and Read Size

This sequence shows which layer produced the improvement and prevents Bloom Pruning gains from being incorrectly attributed to the Cluster Key.

Defining or changing a Cluster Key does not automatically reorder every historical Block. Before evaluating historical data, reorganize it according to the maintenance strategy used by your deployment:

ALTER TABLE hits
CLUSTER BY (counterid, eventdate);

When running explicit Recluster operations, prefer limiting the operation to the affected range:

ALTER TABLE hits RECLUSTER
WHERE eventdate >= '2013-07-14'
  AND eventdate <  '2013-07-16';

RECLUSTER FINAL

Put maintenance cost on the same balance sheet

Reorganization consumes compute, I/O, and temporary storage. Out-of-order ingestion, historical backfills, and frequent

UPDATE

DELETE

MERGE

Evaluate a complete business period and ask:

• How much cumulative

  Read Rows
  

  and

  Read Size
  

  did the important queries avoid?

• Did query frequency amplify those savings enough to matter?

• How much compute did automatic or explicit clustering consume?

• Do new writes continually degrade the layout?

• Can maintenance target only the affected time range?

The goal is not to rewrite the entire table on a fixed schedule. Recluster when layout degradation begins to affect important queries, and restore sufficient pruning at a controlled cost.

Derive the Cluster Key from the Workload

There is no universal best column order for a Cluster Key. What transfers across workloads is the decision process:

Find the query families with the highest cumulative scan cost
        ↓
Extract selective predicates they consistently share
        ↓
Match time granularity to typical query windows
        ↓
Build candidate layouts from leading predicates and ingestion order
        ↓
Run controlled tests with identical physical settings
        ↓
Use clustering_information to inspect data organization
        ↓
Use EXPLAIN ANALYZE to measure actual scans
        ↓
Confirm that long-term query savings exceed maintenance cost

Five principles summarize the method:

1. A frequently filtered column does not necessarily have pruning value.

2. Neither low nor high cardinality is a sufficient selection criterion on its own.

3. The leading columns of a composite key define its primary physical ranges.

4. Lower Depth does not necessarily mean lower scan cost.

5. Adding more Cluster Key columns does not necessarily improve the layout.

The objective is not to make

average_depth

The experiment ran on Databend

v1.2.932-nightly

. This article also references official documentation available in August 2026. Clustering behavior, string statistics, and

EXPLAIN ANALYZE

output may change across versions; validate against the documentation and the behavior of the version you deploy.