@Rocky can I use UUID v7 as a partition key in StarRocks, and if so, what's the best partitioning strategy for it?

Yes, you can use UUID v7 as a partition key in StarRocks. Because UUID v7 is time-ordered (the first 48 bits represent a Unix timestamp in milliseconds), it is much more efficient for partitioning and sorting than random UUIDs (v4). Supported Strategies 1. Range Partitioning (Recommended): Since UUID v7 is monotonically increasing over time, you can use Range Partitioning. This allows you to group data into time-based ranges. * Data Type: It is highly recommended to store UUIDs as

LARGEINT

(128-bit integer) using the

uuid_v7_numeric()

function. This is more compact and faster for comparisons than the

VARCHAR

representation. * Partitioning by UUID directly: You can define range partitions based on the UUID values themselves. However, this can be difficult to manage manually because the ranges are hex or large integers. 2. Expression Partitioning (v3.3+): Starting from StarRocks v3.3.0, you can use specific functions in your partition key to extract a date from a column. * If you store the UUID as a

LARGEINT

, you can extract the timestamp by right-shifting 80 bits (to isolate the 48-bit timestamp) and then using

from_unixtime_ms()

. _ _Note: Ensure your specific version supports bitwise operators in the partitioning expression; otherwise, it is better to store the extracted

DATETIME

in a separate column.* Best Partitioning Strategy The "best" strategy depends on how you query the data: * If you query by time and UUID: The most robust strategy is to have an explicit

event_time

(DATETIME) column and partition by that (using Expression Partitioning with

date_trunc

). Then, use the UUID v7 as the first column in your Sort Key or Primary Key. This gives you the best of both worlds: partition pruning by date and high-performance lookups/range scans on the UUID. * If you only want to partition by the UUID: Use Range Partitioning. Since UUID v7 is time-ordered, new data will naturally land in the "latest" partition, preventing the fragmentation and "random write" issues associated with UUID v4. Implementation Tips * Storage: Use

LARGEINT

for

uuid_v7_numeric()

to save 50% storage space compared to

VARCHAR(36)

. * Version: If you are on v3.3 or higher, leverage automatic partition creation (Automatic Partitioning) so StarRocks can manage the ranges for you as new UUIDs arrive. * Pruning: For partition pruning to work, your queries must include filters on the partition key. If you partition by the UUID itself, your

WHERE

clauses should use the UUID. Example (Range Partitioning with LARGEINT)

sql
CREATE TABLE orders (
    order_id LARGEINT NOT NULL, -- Store UUID v7 here
    customer_id INT,
    amount DECIMAL(10, 2)
) 
DUPLICATE KEY(order_id)
PARTITION BY RANGE(order_id) (
    -- Manual ranges or use Automatic Partitioning in v3.3+
    START ("0") END ("20000000000000000000000000000000000000") EVERY (10000000000000000000000)
)
DISTRIBUTED BY HASH(order_id);

For most production use cases, partitioning by a dedicated

DATE/DATETIME

column while keeping the UUID v7 as a Sorting Key is the most manageable and performant approach. References • sql-reference/sql-functions/utility-functions/uuidv7.md • sql-reference/sql-functions/utility-functions/uuid_v7_numeric.md • sql-reference/sql-functions/variant-functions/get_variant.md • sql-reference/sql-functions/date-time-functions/date.md • sql-reference/sql-functions/date-time-functions/date_trunc.md

@Rocky I need to use id as a varchar. Is there a way to achieve partition pruning when querying by ID

To achieve partition pruning and high performance with a

VARCHAR

UUID v7 ID, you should distinguish between Partition Pruning (skipping files/directories) and Index Lookups (fast row-level access). 1. Can you prune partitions by a

VARCHAR

ID? Yes, but you should not partition by the ID column itself. Because every UUID is unique, partitioning by the ID would create one partition per row, which will crash the Frontend (FE). Instead, use Expression Partitioning (available in v3.1+) to extract a "coarse" time-based partition key from the

VARCHAR

ID. The Strategy (v3.4+): If your UUID v7 is stored as a

VARCHAR

, you can use a combination of

SUBSTR

and

str2date

(or a separate

DATETIME

column) to define the partition. However, StarRocks requires the partition expression to result in a

DATE

or

DATETIME

for range-based pruning to work efficiently. Since converting a Hex UUID string to a Date inside a

CREATE TABLE

expression is complex, the best practice is: 1. Store a separate

created_at

column: Partition by

date_trunc('day', created_at)

. 2. Include both in your query: If you query

WHERE id = '...' AND created_at = '2023-10-01'

, StarRocks will prune partitions using the date and then use the index to find the ID. 2. How to get "Pruning-like" performance without a Date filter If you cannot include a date filter in your queries, you can rely on the Short Key Index or a Primary Key Table: * Short Key Index (Duplicate/Aggregate Key Tables): StarRocks automatically creates a "Short Key Index" (prefix index) for the first few columns of your table. If your

VARCHAR

ID is the first column in your

DUPLICATE KEY(...)

or

PRIMARY KEY(...)

definition, StarRocks can locate the data extremely fast (comparable to partition pruning) even without a partition filter. _ _Note*: Since UUID v7 is time-ordered, it is highly efficient for this index because new IDs are appended to the end of the data files, keeping the index "hot" and sorted. * Primary Key Tables: If your table is a Primary Key table and

id

is the primary key, StarRocks uses an in-memory index to map the

id

directly to its location. This is the fastest way to query by a

VARCHAR

ID and does not require partition pruning to be fast. Recommended Schema If you must use

VARCHAR

and want the best performance:

sql
CREATE TABLE users (
    id VARCHAR(36) NOT NULL,
    created_at DATETIME NOT NULL,
    name VARCHAR(50)
) 
PRIMARY KEY(id) -- ID is indexed in memory for instant lookups
PARTITION BY date_trunc('day', created_at) -- Pruning for time-based/batch queries
DISTRIBUTED BY HASH(id); -- Ensures data is spread evenly

Summary of Pruning for

VARCHAR

ID: Query Type | Best Mechanism | Requirement Point Lookup (

WHERE id = '...'

) | Primary Key Index | Define

id

as the Primary Key. Range Scan (

WHERE id > '...'

) | Short Key Index | Put

id

as the 1st column in the sort key. Time-based Pruning | Expression Partitioning | Partition by a

DATE

column or expression. Pro Tip: If you are on StarRocks v3.3+, you can use

uuid_v7_numeric()

to store the ID as a

LARGEINT

. It is significantly faster than

VARCHAR

for both partitioning and indexing, and you can still format it as a string in your Application layer views or out of the database if needed. References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-functions/utility-functions/uuidv7.md • table_design/data_distribution/Data_distribution.md • faq/Sql_faq.md • best_practices/partitioning.md References