@Rocky Does StarRocks use ZoneMap indexes by default for primary key tables? Does it only apply to low cardinality values? Because if a value is high cardinality and distribution is random, then woudln't zone maps be useless since most blocks of data will be scanned anyways? Does it work best with ORDER BY keys?

Yes, StarRocks uses ZoneMap indexes by default for all table types, including Primary Key tables. They are built-in, automatically created, and require no manual configuration. Here is a breakdown of how they work and their relationship with cardinality and sorting: 1. Are they only for low cardinality? No. ZoneMap indexes (which store

Min

,

Max

,

HasNull

, and

HasNotNull

for each data page and segment) are used for columns of any cardinality. In fact, they are highly effective for high-cardinality columns like

timestamps

,

IDs

, or

prices

, provided the data is not randomly distributed. 2. High Cardinality + Random Distribution = "Useless" You are correct. If a high-cardinality column has a random distribution, the ZoneMap index becomes ineffective. * Why: If every 64KB data page contains a wide range of values (e.g., one page has both a very small ID and a very large ID because they were inserted randomly), the

Min

and

Max

for that page will likely overlap with almost any query range. * Result: StarRocks will be unable to skip the page, and a full scan of that column will occur. This is a common performance bottleneck for columns that are not part of the sort key. 3. Do they work best with ORDER BY keys? Yes. ZoneMap indexes are most powerful when applied to the Sort Key (defined by the

ORDER BY

clause in Primary Key tables). * Clustering: Sorting ensures that values are physically stored in order. This results in "tight" Min/Max ranges for each data page (e.g., Page 1 contains IDs 1–100, Page 2 contains 101–200). * Pruning: Because the ranges are narrow and non-overlapping, StarRocks can instantly skip (prune) the vast majority of pages that don't match your

WHERE

clause. Key Takeaways for Primary Key Tables: * Default Behavior: In StarRocks 3.x, if you don't specify an

ORDER BY

clause, the Primary Key columns are used as the Sort Key by default. ZoneMaps will be very effective for these columns. * Decoupling: If your most frequent queries filter on a column that is not the Primary Key (e.g.,

event_time

), you should use the

ORDER BY

clause to set that column as the Sort Key. This maximizes ZoneMap pruning for those queries. * Alternative for Random Data: If you must query a high-cardinality, randomly distributed column that cannot be sorted, consider using a Bloom Filter index instead, as it is designed to handle point lookups in non-sorted data. References • knowledge/usage/best-practice.md • table_design/indexes/Prefix_index_sort_key.md • best_practices/table_clustering.md

@Rocky

PRIMARY KEY (received, auction_id, imp_key)
	PARTITION BY date_trunc('hour', received)
	DISTRIBUTED BY HASH (auction_id) BUCKETS 128
	ORDER BY (received, auction_id)

Is this order key useless then? Order keys are a subset of primary key. Given StarRocks recommends only 3 order keys and max 4, does that mean I can remove auction id and received from order key and use other keys like campaign_id and advertiser_id and network_id to make zone maps work more effectively?

The current order key is not "useless," but it is highly specialized for queries that filter strictly by

received

(at a sub-hour level) and

auction_id

. Because your table is a Primary Key Model (decoupled since v3.0), you have significant flexibility. Here is why you should consider adjusting your

ORDER BY

clause: 1. Partitioning vs. Sorting Since you already have

PARTITION BY date_trunc('hour', received)

, StarRocks already performs hourly pruning at the metadata level. * Including

received

as the first column in your

ORDER BY

adds fine-grained sorting (down to the second/millisecond) within each hour. * Verdict: If your queries mostly filter by hour or day, you don't need

received

at the start of your Sort Key. Removing it frees up the "leading" slots for columns that actually need help with pruning. 2. High Cardinality "Blocks" the Sort Key As you noted,

auction_id

is likely high cardinality. * When a high-cardinality column is placed early in the

ORDER BY

, it sorts the rows so granularly that the columns after it become effectively random. * Result: In a 64KB data page, if

auction_id

has sorted your rows so much that each page only contains a few unique IDs, any third column (like

imp_key

) will likely have a wide `Min`/`Max` range in every page, rendering its ZoneMap index useless. 3. Using

campaign_id

,

advertiser_id

, etc. Yes, you should move these into the Sort Key. This is a best practice for StarRocks: * Primary Key: Handles uniqueness for upserts and deletes via a separate internal index (Persistent Index). It does not need to be the Sort Key. * Sort Key (

ORDER BY

): Determines physical storage order to maximize ZoneMap and Prefix Index (Short Key Index) performance for SELECT queries. * Strategy: Put your most frequent filter columns (usually low-to-medium cardinality) at the beginning of the

ORDER BY

. Suggested Optimization: If you frequently query by campaign or advertiser, change your table definition to:

sql
PRIMARY KEY (received, auction_id, imp_key)
PARTITION BY date_trunc('hour', received)
DISTRIBUTED BY HASH (auction_id) BUCKETS 128
ORDER BY (campaign_id, advertiser_id, network_id)

What happens to

auction_id

? If you still need fast point lookups for

auction_id

but it's no longer in the Sort Key, you should add a Bloom Filter index to that column. This allows StarRocks to quickly tell if an

auction_id

exists in a data block without needing the data to be physically sorted by that ID. Key Takeaways: * Sort Key Limit: Aim for 3–5 columns. The Prefix Index only covers the first 36 bytes of the Sort Key, so the order of these columns matters significantly. * Cardinality Rule: Place columns from lowest to highest cardinality in the

ORDER BY

clause to keep ZoneMap ranges "tight" for as many columns as possible. * Decoupling: Do not feel obligated to include Primary Key columns in your

ORDER BY

. They serve different purposes. References • table_design/table_types/primary_key_table.md • knowledge/usage/best-practice.md • table_design/indexes/Prefix_index_sort_key.md • best_practices/table_clustering.md References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md

@Rocky How about for array value lookups? Is there any way to optimize it?

For array value lookups (like

array_contains

), StarRocks does not currently support direct indexing (such as Bloom Filter or Bitmap) on the

ARRAY

data type itself. However, there are several powerful strategies to optimize these queries: 1. The Recommended Approach: Generated Columns The most effective way to optimize array lookups is to use Generated Columns. Since StarRocks can't index the array directly, you "pull out" the data you query most often into a separate, indexable column. * How it works: You define a column that automatically extracts a value or a boolean from the array. * Example: If you frequently query

WHERE array_contains(tags, 'premium')

, you can create a generated column:

sql
    ALTER TABLE users ADD COLUMN is_premium BOOLEAN
    AS (array_contains(tags, 'premium'));

* Why it's fast: You can then add a Bitmap index or a Bloom Filter to the

is_premium

column. StarRocks will query the index instead of scanning every array in every row. 2. Inverted Indexes (v4.0+ for Primary Key Tables) StarRocks introduced Full-text Inverted Indexes (starting in v3.3 for Duplicate tables and v4.0 for Primary Key tables). * Current Limitation: As of now, these indexes are primarily designed for

STRING

,

VARCHAR

, and

CHAR

types. They are not yet directly supported for the

ARRAY

data type. * Workaround: If your array contains strings, you could store a "flattened" string version of the array (e.g.,

"tag1 tag2 tag3"

) in a separate

STRING

column and use a

MATCH

query with an inverted index. 3. Query Optimization:

unnest

vs.

array_contains

For certain patterns, users have found that using a

JOIN

with

unnest

or a subquery can sometimes be faster than

array_contains

on very large datasets, though this is highly dependent on your data distribution and the specific query. 4. ZoneMap and Arrays ZoneMap indexes do exist for Array columns, but they only store the Min/Max of the array's elements and the nullity of the array. * When they help: If you are looking for a value (e.g.,

5

) and the

Max

of an array in a data page is

4

, StarRocks can skip that entire page. * When they fail: If your arrays have a wide range of values (e.g., every array contains both small and large numbers), the

Min

and

Max

will cover almost everything, making the ZoneMap useless. Summary of Best Practices: Scenario | Strategy Searching for a specific tag/value | Use a Generated Column + Bitmap/Bloom Filter index. Searching for multiple values | Use a Generated Column that calculates a "fingerprint" or use multiple generated booleans. High-frequency lookups | Move the array elements into a separate "mapping" table (normalize) and use standard Primary Key joins. References • table_design/indexes/inverted_index.md • sql-reference/sql-functions/array-functions/array_contains.md • table_design/generated_columns.md (For accelerating semi-structured data) References • table_design/indexes/indexes.md • sql-reference/sql-functions/array-functions/array_contains_seq.md • sql-reference/data-types/semi_structured/Array.md • sql-reference/sql-functions/array-functions/array_contains_all.md • knowledge/usage/best-practice.md

@Rocky Do non integer values (like string) use zone maps too? My auction id is primarily based off timestamp, so the ordering should be natural already

Yes, non-integer values like strings (VARCHAR/CHAR) also use ZoneMap indexes. In StarRocks, ZoneMaps are not limited to numbers; they work for any data type that has a clear "less than" or "greater than" relationship (lexicographical order for strings). Here is how it works for your specific scenario: 1. How String ZoneMaps Work StarRocks stores the

Min

and

Max

values for each data page. For strings, it uses alphabetical (lexicographical) order. * Key Strings: If a string column is part of your

ORDER BY

or

PRIMARY KEY

(which defaults to the sort key), StarRocks creates a full ZoneMap index for it. * Non-Key Strings: For string columns not in the sort key, StarRocks can still create ZoneMaps, but it often truncates the `Min`/`Max` values to a certain prefix length (controlled by the BE config

string_prefix_zonemap_prefix_len

) to save storage space while still allowing for effective pruning. 2. Is your "Natural Order" effective? Yes. If your

auction_id

is based on a timestamp (e.g.,

20231027-xxxx

), its alphabetical order will perfectly match its chronological order. * Because you are likely inserting data as it happens, the values are physically stored in a "naturally sorted" way. * Even if

auction_id

is not the first column in your

ORDER BY

, as long as the data is not randomly scattered across the table, the

Min

and

Max

for each data page will be very "tight" (narrow ranges). * Result: When you run

WHERE auction_id = '...'

, StarRocks will compare your ID against the `Min`/`Max` of each page. Because of the natural clustering, it will skip almost all pages that don't contain that specific timestamp range. 3. Key Configuration (BE) There is a specific Backend (BE) setting that governs this behavior: *

enable_string_prefix_zonemap

: Defaults to

true

. This confirms that StarRocks is actively using ZoneMaps for your string columns. When does it fail for strings? The only time ZoneMaps fail for strings is when the strings are UUIDs or Random Hashes. In those cases: * A single 64KB data page might contain a string starting with

A

and another starting with

Z

. * The

Min

for that page becomes

A

and the

Max

becomes

Z

. * Since almost every query falls between

A

and

Z

, the index can't prune anything. Summary: Since your

auction_id

is timestamp-based and naturally ordered, the ZoneMap index will be highly effective, even if you don't explicitly put it first in the

ORDER BY

clause. References • administration/management/BE_configuration.md • table_design/indexes/indexes.md • knowledge/usage/best-practice.md • table_design/indexes/Prefix_index_sort_key.md References • knowledge/usage/best-practice.md • table_design/indexes/indexes.md • administration/management/BE_configuration.md • best_practices/query_tuning/schema_tuning.md • table_design/StarRocks_table_design.md • table_design/indexes/Bitmap_index.md

@Rocky Does it apply to datetime types as well? In that case, using received as order type is useless since it's naturally in order physically

Yes, DATETIME types absolutely use ZoneMap indexes (storing Min/Max values) just like integers and strings. Your observation is very sharp: if your data is naturally inserted in chronological order, the physical storage is already "clustered" by time. This makes the ZoneMap index for

received

highly effective even if you don't include it in the

ORDER BY

clause. However, there is a technical distinction between "Natural Clustering" (ZoneMaps) and "Explicit Sorting" (Prefix Index). Here is how to decide if you can remove

received

from the order key: 1. ZoneMap vs. Prefix Index * ZoneMap (Natural): Every column has this. If data is naturally ordered, StarRocks will see that Page 1 has

received

from 1000–1005 and Page 2 has 1005–1010. It can skip Page 1 if you query for 10:07. * Prefix Index (ORDER BY): This is a specialized, in-memory index built only on the first ~36 bytes of your

ORDER BY

columns. It allows StarRocks to binary-search for the exact data block location without even looking at the ZoneMap metadata. It is the "fastest" path. 2. The "Uselessness" of

received

in your specific case In your current schema,

received

might indeed be redundant in the

ORDER BY

for two reasons: 1. Partition Pruning: Since you have

PARTITION BY date_trunc('hour', received)

, any query with a time filter already skips entire chunks of data at the metadata level before the Sort Key is even touched. 2. Natural Clustering: As you noted, the physical order already keeps the ZoneMap ranges tight. 3. Recommendation: Should you remove it? If you have a limited "budget" of 3–4 Sort Key columns, you should likely move

received

and

auction_id

further down or remove them in favor of columns that are not naturally ordered but are frequently filtered. Comparison of Strategies: Feature | Current Strategy (

received

first) | Optimized Strategy (

campaign_id

first) Time Filtering | Extremely fast (Prefix Index + Partitioning). | Very fast (ZoneMap + Partitioning). Campaign Filtering | Slow (Must scan all pages in the time range). | Extremely fast (Prefix Index). Storage Efficiency | High (Good compression on time). | High (Better compression on campaign groups). Final Verdict: If your most common queries include filters like

WHERE campaign_id = 123

, you will see a massive performance boost by changing your Sort Key to:

ORDER BY (campaign_id, advertiser_id, network_id)

Why this works: 1. Time Pruning still happens via

PARTITION BY

. 2. Fine-grained Time Pruning still happens via ZoneMaps (because of the natural order). 3. Campaign/Advertiser Pruning now happens via the Prefix Index, which was previously impossible because they were "blocked" by the high-cardinality

auction_id

. One caveat: If you frequently run queries that sort by

received

(e.g.,

ORDER BY received DESC LIMIT 10

), keeping

received

in the Sort Key prevents a heavy in-memory sort at query time. If you don't do that, move it out! References • knowledge/usage/best-practice.md • table_design/indexes/Prefix_index_sort_key.md • table_design/indexes/indexes.md • administration/management/BE_configuration.md • best_practices/query_tuning/schema_tuning.md References • knowledge/usage/best-practice.md • table_design/indexes/indexes.md

@Rocky Is it possible to update order keys of an existing table without recreating it?

Yes, you can update the order keys (sort keys) of an existing table without recreating it. This feature is supported via the

ALTER TABLE

statement. Support by Version and Table Type * Primary Key Tables: Supported since v3.0. * Duplicate, Aggregate, and Unique Tables: Supported since v3.3. Syntax To change the sort key, use the following command:

sql
ALTER TABLE <table_name> ORDER BY (column1, column2, ...);

How it Works 1. Asynchronous Process: The change is an asynchronous schema change. StarRocks doesn't immediately rewrite all your existing data. 2. New Data: Any new data ingested after the command will be physically sorted according to the new order keys immediately. 3. Existing Data: Old data is gradually re-sorted in the background during the compaction process. 4. Prefix Index: StarRocks will automatically rebuild the Prefix Index based on the new order. Monitoring Progress Since it is an asynchronous task, you can monitor the progress of the re-sorting using:

sql
SHOW ALTER TABLE COLUMN FROM <db_name>;

Important Considerations * Performance Impact: While the operation is online, the background compaction to re-sort large amounts of existing data can consume IO and CPU. It is best to perform this during lower-traffic periods. * Aggregate/Unique Key Tables: For these table types, the new

ORDER BY

clause must still include all columns that make up the key (Aggregate Key or Unique Key), but you can change their relative order. * Primary Key Tables: You have full flexibility; the sort key is decoupled from the Primary Key, so you can choose any combination of columns (excluding

FLOAT

,

DOUBLE

,

JSON

, and complex types). In your specific case: If you want to move

received

and

auction_id

further down or remove them to prioritize other columns like

campaign_id

for the prefix index, you can simply run:

sql
ALTER TABLE auctions ORDER BY (campaign_id, advertiser_id, received);

This will allow your queries filtering by

campaign_id

to use the high-performance Prefix Index, while

received

will still benefit from the "Natural Order" ZoneMaps we discussed earlier. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/table_types/primary_key_table.md • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/indexes/Prefix_index_sort_key.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/table_types/primary_key_table.md • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/indexes/Prefix_index_sort_key.md

@Rocky Is there option to only change order key for new records so no background cpu intensive process kicks off?

In StarRocks, there is no explicit option to change the order key for "new records only" while permanently exempting old records from being re-sorted. The sort key is a table-level property that dictates how data is physically organized to support the Prefix Index. Because the Prefix Index is built based on this sort key, StarRocks must eventually ensure that all data in the table conforms to the same ordering to maintain query consistency and performance. However, the way StarRocks handles this depends on the table type and version: 1. Primary Key Tables (v3.0+) For Primary Key tables, changing the sort key via

ALTER TABLE ... ORDER BY

is designed to be as low-impact as possible: * Immediate for New Data: Any data ingested after the command is issued will immediately be sorted and indexed according to the new key. * Gradual for Old Data: StarRocks does not typically trigger a massive, one-time "stop-the-world" rewrite of all historical data. Instead, it relies on background compaction to gradually re-sort existing data into the new order over time. * CPU Impact: While this background process does use CPU/IO, it is governed by the same resource limits as standard compaction. It won't "spike" your CPU in the same way a synchronous table recreation would. 2. Duplicate, Aggregate, and Unique Tables (v3.3+) In these models, changing the sort key is also an asynchronous operation. While it technically creates a "Schema Change" job, StarRocks optimizes this by allowing the table to remain online. How to Minimize CPU Impact If you are worried about the background process consuming too many resources, you can control it using these configurations: 1. Throttling Compaction (Shared-Data/Cloud Native): If you are on a shared-data (cloud-native) cluster, you can disable or limit the number of concurrent compaction tasks for a specific table to "defer" the re-sorting:

sql
    -- Get your table ID first, then:
    ADMIN SET FRONTEND CONFIG ("lake_compaction_disable_tables" = "your_table_id");

2. Scheduling during Low Traffic: Even though the process is backgrounded, you should run the

ALTER TABLE

command during off-peak hours. The heaviest CPU usage occurs when the system merges large "Base" versions of your data. 3. Monitor the Job: You can track exactly how much data has been converted using:

sql
    SHOW ALTER TABLE COLUMN;

Summary: You don't need a "new records only" flag because StarRocks naturally prioritizes new data and handles old data lazily in the background. The "natural order" of your timestamp-based

auction_id

will actually make the re-sorting process very efficient for the system, as the data is already mostly in the correct physical proximity. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • administration/management/compaction.md • table_design/table_types/primary_key_table.md • knowledge/usage/best-practice.md • best_practices/query_tuning/schema_tuning.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • best_practices/query_tuning/schema_tuning.md • administration/management/compaction.md • table_design/table_types/primary_key_table.md

@Rocky If I change order key to jsut advertiser id and campaign id, wouldnt this new physical sorting order mean even though received column is in a natural non-decreasing insertion pattern, it will be re-arranged in advertiser_id, campaign_id order

You are exactly right. Your intuition is 100% correct: Changing the sort key to

advertiser_id, campaign_id

will physically rearrange the data, which will "scatter" the

received

column and degrade its ZoneMap efficiency. Here is the technical breakdown of what happens to that "natural order": 1. Physical "Scattering" When you use

ORDER BY (advertiser_id, campaign_id)

, StarRocks groups all rows for the same advertiser together. * Before: Page 1 might contain data from 10:00 to 10:05. The ZoneMap

min: 10:00, max: 10:05

is very tight. * After: Page 1 will contain data for

advertiser_id = 1

. If that advertiser has been active for 3 years, Page 1 now has a ZoneMap

min: 2021-01-01, max: 2024-01-01

. Because the

min/max

range for

received

on that page is now huge, a query for

WHERE received > '2024-01-01'

can no longer skip that page. StarRocks will have to open the page and scan the rows to see if any belong to 2024. 2. Why Partitioning Saves You If you have

PARTITION BY date_trunc('day', received)

, your "time-based pruning" is not entirely lost. * Partition Level: StarRocks still knows which files (partitions) belong to which day. It will only look at the files for the days in your query. * Page Level: Within those files, however, the data is shuffled. You will end up scanning nearly 100% of the data inside the relevant partitions, rather than skipping 90% of the pages within them. 3. The Performance Trade-off You are essentially choosing between two "Indexing Philosophies": Query Type | Current (

received

first) | New (

advertiser_id

first) Time Range (e.g., last 1 hour) | Extreme Pruning. Scans almost nothing. | Partition Pruning only. Scans the whole hour. Specific Advertiser | Scan heavy. Scans all time-relevant data. | Extreme Pruning. Jumps directly to rows. Recommendation: The "Compromise" Sort Key If you want the best of both worlds, you should keep a "coarse" time element in your sort key before the high-cardinality IDs. Instead of

ORDER BY (advertiser_id, campaign_id)

, consider:

sql
ALTER TABLE auctions ORDER BY (date_trunc('hour', received), advertiser_id, campaign_id);

Why this is often the "Sweet Spot": 1. Clustered Time: It keeps rows from the same hour physically together, so ZoneMaps remain tight and effective for time-based queries. 2. Advertiser Speed: Within that hour, all data for

advertiser_id = 1

is grouped together, giving you the Prefix Index speed you want for your filters. 3. Natural Order Preservation: Because you are only sorting by hour, the "natural order" of your data isn't completely destroyed; it's just organized into hourly buckets. Summary If you remove

received

entirely from the sort key, the physical "natural order" is lost during the background compaction process. If time-filtering performance is critical to you, do not remove it entirely; move it to a "coarse" position (like hourly) or keep it as the first column. References • faq/Sql_faq.md • best_practices/table_clustering.md • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md