@Rocky If my duplicate key data is mostly in-order timestamp key, is there still much benefit in using the timestamp field as sort key, or will hi-low column metadata make tha less necessary?

Yes, there is still significant benefit in using the timestamp as a sort key, even if your data arrives "mostly in-order." While Zone Maps (the hi-low metadata) provide the baseline for data skipping, their effectiveness is directly tied to how strictly the data is physically sorted. Here is why an explicit sort key remains necessary: 1. Tightening Zone Map Ranges Zone Map metadata exists for every 64KB page and every segment (~512MB). * Without a sort key: Even if 99% of your data is in order, a single late-arriving record with an old timestamp in a segment will expand that segment's "Min-Max" range to cover the entire history. This forces the engine to scan the segment instead of skipping it. * With a sort key: StarRocks sorts the data in the MemTable before flushing to disk and maintains this order during background compaction. This ensures that each page and segment has the tightest possible Min-Max range, which is the single most important factor for massive I/O elimination. 2. Prefix Index (Short-Key Index) StarRocks automatically builds a Prefix Index for columns defined in the sort key. This is a sparse index (storing the first 36 bytes of the sort key every 1024 rows) that allows the engine to perform binary searches to land on the exact data page needed. * If the timestamp is not in the sort key, you lose this "millisecond point look-up" capability for time-based queries. 3. Better Compression and Encoding Sorted data allows the storage engine to use more efficient compression algorithms: * Run-Length Encoding (RLE): If timestamps repeat or are very close, sorting allows RLE to compress the column significantly. * Frame-of-Reference (FOR): Sorted integers or timestamps result in smaller deltas between rows, which reduces the physical storage footprint. * Docs note that sorted telemetry data often achieves ~1.8x better compression and 25% lower CPU usage during scans compared to unsorted data. 4. Streaming Aggregation If you frequently run queries like

SELECT count(*) ... GROUP BY timestamp_hour

, having the timestamp as the sort key allows StarRocks to use a Streaming Aggregation execution plan. This avoids the overhead of building a large hash table in memory because the engine knows the data is arriving in order. Summary If your data is already mostly in-order, you are in a great position because the background compaction process will have very little work to do to keep it perfectly sorted. By declaring it as the sort key, you "lock in" these benefits (Prefix Index, tight Zone Maps, and optimized compression) that "mostly" ordered data cannot guarantee on its own. _Reference: StarRocks documentation on Duplicate Key Tables and Table Clustering Best Practices._ References • table_design/table_types/duplicate_key_table.md • table_design/table_types/unique_key_table.md • table_design/table_types/table_types.md • best_practices/table_clustering.md

@Rocky is it recommended to include even high cardinality dimensions in the sort key? Also how many dimensions are reasonable to include?

Yes, it is highly recommended to include high-cardinality dimensions in the sort key, as they provide the best data pruning (the ability to skip large chunks of data during a scan). However, there are specific trade-offs regarding their position and the number of columns you should include. 1. High Cardinality: The Pruning vs. Compression Trade-off High-cardinality columns (like

user_id

or

device_id

) are the most effective at narrowing down the search space. * For Pruning: Place high-cardinality columns early in the sort key if they are frequently used in equality filters (

WHERE user_id = 123

). This allows the Prefix Index to quickly jump to the exact data pages. * For Compression: If query performance is similar, placing a low-cardinality column (like

region

or

event_type

) before a high-cardinality one can significantly improve the compression ratio. This is because sorting by low-cardinality values creates long "runs" of identical data that the storage engine can compress very efficiently. * The 36-Byte Limit: StarRocks builds a Prefix Index using the first 36 bytes of the sort key. If your first column is a long

VARCHAR

(high cardinality), it might consume the entire 36-byte quota, preventing subsequent columns from being indexed. 2. How many dimensions are reasonable? The general recommendation is to keep the sort key to 3 to 5 columns. * The "Diminishing Returns" Rule: Adding more than 5 columns rarely improves query performance but increases the CPU and memory overhead during data ingestion and background compaction. * Order Matters: StarRocks treats the sort key as a composite index. It works best when your query filters match the prefix of your sort key. _ _Example:* If your sort key is

(A, B, C)

, a query filtering on

A

and

B

will be very fast. A query filtering only on

C

will not benefit from the Prefix Index (though it will still benefit from Zone Maps/Min-Max stats). Recommended Best Practice (The "Heuristic") Follow this order when selecting your 3–5 columns: 1. Equality Filters: Columns most frequently used with

=

or

IN

(e.g.,

tenant_id

,

user_id

). 2. High-Selectivity Dimensions: The high-cardinality fields that narrow the data down most aggressively. 3. Range Filters: Usually the Timestamp. Even if data is "mostly in order," placing it here ensures the tightest possible min-max ranges for time-slice queries. Pro-tip: If you have high-cardinality dimensions that you don't include in the sort key, consider adding a Bloom Filter index on them to maintain fast point-lookup performance without the overhead of sorting. References • best_practices/table_clustering.md • table_design/indexes/Prefix_index_sort_key.md • best_practices/query_tuning/schema_tuning.md • faq/operation_maintenance_faq.md References • best_practices/table_clustering.md • table_design/indexes/Prefix_index_sort_key.md • faq/operation_maintenance_faq.md • best_practices/query_tuning/schema_tuning.md

@Rocky I guess I want to sort in order of increasing cardinality, so I may have campaign_id,device_id,timestamp. This is unintuitive to me since timestamp comes in mostly sorted

Your intuition is actually correct regarding compression, but it conflicts with the primary goal of the sort key, which is query pruning (speed). In StarRocks, the order of columns in the

ORDER BY

clause is a trade-off between how fast you can find data and how small you can store it. 1. The Cardinality Trade-off * Low Cardinality First (

campaign_id

→

device_id

): This is better for compression. By sorting on a column with few values (like

campaign_id

), you create long continuous "runs" of identical data. The engine can then use Run-Length Encoding (RLE) to store that column in almost zero space. * High Cardinality First (

device_id

→

campaign_id

): This is better for pruning. If your most frequent query is

WHERE device_id = 'XYZ'

, putting the high-cardinality column first allows the Prefix Index to skip directly to the exact page for that device. 2. Why Timestamp usually goes last Even though data arrives "mostly sorted" by time, we usually put the timestamp after the high-cardinality dimensions (e.g.,

ORDER BY (device_id, timestamp)

). Here is why: * The "Bucketing" Effect: By sorting by

device_id

first, you physically cluster all records for a specific device together. Within that device's cluster, the data is then sorted by

timestamp

. * Time-Series Efficiency: Most queries are "What did Device A do in the last hour?" _ If

timestamp

is first, the engine finds the "last hour" for _all* devices and then has to filter for Device A. * If

device_id

is first, the engine jumps to Device A's data and then performs a very narrow range scan for the "last hour." This is significantly faster. * The 36-Byte Prefix Limit: StarRocks only indexes the first 36 bytes of your sort key. If you put a high-cardinality

VARCHAR

(like

device_id

) second, and the first column is already long, the

device_id

might not even make it into the Prefix Index. 3. Addressing the "Mostly Sorted" Intuition The fact that your data is already "mostly sorted" by timestamp is a benefit, not a reason to leave it out. * It means the background compaction process has very little work to do to maintain the physical order. * However, "mostly sorted" is not "perfectly sorted." Without the timestamp in the sort key, late-arriving data will "smear" the Min-Max metadata of your segments, making the hi-low metadata (Zone Maps) less effective at skipping data. Recommendation for your case If your queries usually include both

campaign_id

and `device_id`: * Recommended Order:

ORDER BY (campaign_id, device_id, timestamp)

* Why:

campaign_id

is likely an INT or short VARCHAR, leaving plenty of room in the 36-byte Prefix Index for

device_id

. You get the compression benefit of the low-cardinality prefix and the pruning power of the high-cardinality dimension. Pro-Tip: If you find you need to query by

device_id

without a

campaign_id

, but

campaign_id

is first in your sort key, you should add a Bloom Filter index to the

device_id

column to maintain performance. References • best_practices/table_clustering.md • table_design/indexes/Prefix_index_sort_key.md • faq/operation_maintenance_faq.md ) References • table_design/indexes/Prefix_index_sort_key.md • faq/operation_maintenance_faq.md • knowledge/usage/best-practice.md