@Rocky I have a table with the following columns:

(col1, col2, col3, timestamp)

The requirement is that

(col1, col2)

is the unique business key, and we need to use the Primary Key table model with Broker Load to take advantage of the UPSERT/DELETE capabilities supported by Broker Load. At the same time, we are considering adding

timestamp

as part of the partitioning strategy, i.e., partitioning by

(col1, col2, timestamp)

or using

timestamp

as the partition key, so that queries can take advantage of partition pruning when filtering datasets by time using. (It's mandatory that partition key should be a part of primary key to use) PRIMARY KEY (col1, col2, timestamp) PARTITION BY date_trunc('day', timestamp). The challenge is that if we include

timestamp

in the Primary Key, then

(col1, col2, timestamp)

is no longer guaranteed to be unique. This would impact the UPSERT/DELETE semantics because Broker Load relies on the Primary Key to identify the specific record that needs to be updated or deleted. So, we effectively have two requirements: 1.

(col1, col2)

must remain the unique key to support Primary Key table semantics and Broker Load UPSERT/DELETE operations. 2.

timestamp

needs to be used for partitioning to improve query performance through partition pruning. Given these requirements, what would be the recommended StarRocks table/model design? Would it be better to continue with the Primary Key model + Broker Load, keep

(col1, col2)

as the Primary Key, and use

timestamp

separately as the partition key? Or are there other StarRocks table models or design patterns that would allow us to retain the UPSERT/DELETE capabilities while also getting the benefits of timestamp-based partitioning? Any recommendations or guidance on the best approach would be greatly appreciated. Thanks

In StarRocks, the Primary Key table model has a strict constraint: any column used for partitioning or bucketing must be part of the Primary Key. This creates the conflict you identified: if you add

timestamp

to the Primary Key to allow partitioning, you lose the uniqueness enforcement for

(col1, col2)

. If you don't add it, you cannot partition by it. Given your requirements for UPSERT/DELETE capabilities via Broker Load and query performance via time-filtering, here are the recommended design patterns: Recommendation 1: Use Sort Keys Instead of Partitioning (Best for Performance) Since StarRocks v3.0, the Sort Key is decoupled from the Primary Key. You can maintain the uniqueness of your business key while still getting high-performance time-based pruning. * Design: Keep your Primary Key limited to the business key, but set the

timestamp

as the first column in the

ORDER BY

clause. * Example:

sql
    CREATE TABLE my_table (
        col1 INT NOT NULL,
        col2 INT NOT NULL,
        col3 STRING,
        timestamp DATETIME NOT NULL
    )
    PRIMARY KEY (col1, col2)
    DISTRIBUTED BY HASH (col1, col2)
    ORDER BY (timestamp, col1, col2); -- Sort key for pruning

* Why this works: StarRocks will store data sorted by

timestamp

. It creates a prefix index and zone maps (min/max values) for the sort key. When you query with a

WHERE timestamp > ...

filter, StarRocks will skip entire data blocks that don't match, achieving performance very similar to partition pruning. * Trade-off: You cannot use partition-level management (like

DROP PARTITION

to instantly delete a day of data). Recommendation 2: Use a "Static" Time Column in the PK (Best for TTL/Lifecycle) If you absolutely need partitioning (e.g., for data retention/TTL), you must include a time column in the PK. To avoid breaking UPSERT logic, this column must be static for the life of the record. * Design: Use a column like

created_at

or

event_date

that never changes for a specific

(col1, col2)

pair. * Example:

sql
    CREATE TABLE my_table (
        col1 INT NOT NULL,
        col2 INT NOT NULL,
        created_date DATE NOT NULL,
        col3 STRING,
        updated_at DATETIME
    )
    PRIMARY KEY (col1, col2, created_date)
    PARTITION BY (created_date)
    DISTRIBUTED BY HASH (col1, col2);

* Why this works: Because

created_date

is constant, the Primary Key

(col1, col2, created_date)

remains a stable unique identifier for that record. Broker Load will correctly find and overwrite the existing row even if other columns (like

col3

or

updated_at

) change. * Constraint: You cannot use this if the

timestamp

you want to partition by is a "Last Updated" time that changes with every upsert. Recommendation 3: Partition by a Subset of the Business Key If one of your business keys has a logical grouping (e.g.,

tenant_id

,

region_id

, or a

category_id

), you can partition by that column instead of time. * Design:

PRIMARY KEY (col1, col2)

,

PARTITION BY (col1)

. * Result: This allows you to scale the table and manage data by your business dimensions while keeping the

(col1, col2)

uniqueness intact. Summary * If your priority is query performance and the

timestamp

changes frequently: Use Recommendation 1 (Sort Keys). It is the most flexible approach in StarRocks 3.x. * If your priority is Data Lifecycle (dropping old partitions) and you have a stable date: Use Recommendation 2 (Static Date in PK). Note on Broker Load: In both recommended cases, Broker Load's UPSERT and DELETE (via the

__op

column) will work as expected because the Primary Key remains the stable anchor for each record. References • release_notes/release-3.3.md • table_design/table_types/primary_key_table.md