@Rocky / Team, We have a fundamental question regarding the use of the Primary Key model with Broker Load in StarRocks 3.5.1. We have a table where the actual business primary key is based on

(col1, col2)

. However, when using the Primary Key model with partitioning, the partition key (

col3

) also needs to be included as part of the Primary Key, even though

col3

is not part of the actual unique/business key. Our understanding is that Broker Load performs UPSERTs based on the Primary Key columns. In our use case, we need the UPSERT behavior to be based only on

(col1, col2)

, without considering the partition key (

col3

) as part of the uniqueness. For example, if an existing record has:

(col1, col2, col3=value1)

and a new record arrives with:

(col1, col2, col3=value2)

we still want the new record to update/replace the existing record because

(col1, col2)

is the actual business key. Is there another option or table design that supports this use case with the Primary Key model and Broker Load? Or is this use case not supported with the PK Broker Load model when the partition key can change? Any guidance on the recommended approach would be greatly appreciated. Thanks!

Your understanding is correct. In StarRocks (including version 3.5.1), for Primary Key and Unique Key models, the partitioning columns and bucketing columns must be part of the Primary/Unique key. Because uniqueness is enforced on the entire Primary Key, StarRocks treats

(col1, col2, value1)

and

(col1, col2, value2)

as two distinct records. When you perform a Broker Load with a new

col3

value, StarRocks will insert it into the new partition rather than updating the record in the old partition. Why this requirement exists StarRocks' Primary Key index is stored locally within each tablet. To find which tablet a row belongs to (during an UPSERT/DELETE), StarRocks uses the partition and bucketing columns. If the partition key were not part of the Primary Key, StarRocks would have to search every partition in the cluster to find if the record exists, which would make ingestion extremely slow. Recommended Approaches If your business key

(col1, col2)

is unique but the partition key

col3

can change, here are the standard design patterns: 1. Avoid Partitioning on Mutable Columns If the table size is manageable (e.g., under a few hundred GBs or a few billion rows), consider removing the partitioning on

col3

and only using Bucketing (Hash Distribution) on

(col1, col2)

. * Design:

PRIMARY KEY (col1, col2) DISTRIBUTED BY HASH(col1, col2)

(no

PARTITION BY

). * Result:

(col1, col2)

will correctly enforce uniqueness, and updates will work as expected regardless of

col3

values. 2. Use a Stable Partition Key If you need partitioning for performance or data retention (TTL), use a column that is part of the business key and never changes (e.g.,

created_at

or a fixed

tenant_id

). If

col3

is a "status" or "category" that can change, it is generally a poor candidate for a partition key in a PK table. 3. Explicit DELETE + INSERT (Broker Load) If you must partition by a mutable

col3

, you must handle the "move" manually by sending two operations in your Broker Load: 1. DELETE the old record:

(col1, col2, old_value_of_col3, __op=1)

2. UPSERT the new record:

(col1, col2, new_value_of_col3, __op=0)

* This requires your upstream system to know the previous value of the partition key. 4. Use Flink CDC If your data source is a database (like MySQL/PostgreSQL), using the StarRocks Flink Connector is the easiest solution. Flink CDC automatically tracks "Update Before" and "Update After" events. When a partition key changes in the source, Flink sends a DELETE for the old primary key and an UPSERT for the new one to StarRocks automatically. Summary There is currently no table property in StarRocks 3.5 to decouple the partition key from the Primary Key. You must either: * Make the partition key a column that is fixed for the life of the record. * Remove partitioning if the data volume allows. * Manually manage the move across partitions via DELETE + INSERT. References • table_design/table_types/unique_key_table.md • table_design/table_types/primary_key_table.md • table_design/table_types/table_capabilities.md

@Rocky This is how we have a table creation so will create only weekly partitions based on timestamp column and also, timestamp is stored with full date time values PRIMARY KEY (col1, timestamp, col2) PARTITION BY date_trunc('week', timestamp) DISTRIBUTED BY HASH(col1) col1 VARCHAR(255) NOT NULL, timestamp DATETIME NOT NULL, col2 VARCHAR(255) NOT NULL, data looks like this - 2026-01-12T200534.000Z 2026-01-12T152945.000Z 2026-01-14T142246.000Z

The behavior you are seeing is expected because of how StarRocks manages its Primary Key index. In StarRocks 3.5.1, all partitioning and bucketing columns must be part of the Primary Key. Because the Primary Key index is stored locally within each tablet, StarRocks must know exactly which partition and bucket a row belongs to in order to find and update it. If you change the partition key (

timestamp

), StarRocks considers it a different row in a different location, resulting in a new insertion rather than an update. In your current DDL, including the raw

timestamp

in the PK means that even two records with the same

(col1, col2)

but a 1-second difference in

timestamp

are treated as unique rows. Here are the recommended design patterns to solve this: 1. Remove Partitioning (Recommended for smaller datasets) If your data volume is manageable (e.g., less than 500GB or a few billion rows), the simplest approach is to remove the

PARTITION BY

clause. * Design:

PRIMARY KEY (col1, col2) DISTRIBUTED BY HASH(col1)

* Result:

(col1, col2)

becomes the true business unique key. Any Broker Load with the same

col1, col2

will correctly update the record, regardless of what the

timestamp

value is. * Trade-off: You lose the ability to drop old data by partition (TTL). You would need to use

DELETE

statements to purge old data. 2. Use a Truncated Partition Key in the PK If you must keep partitioning, you can use a generated column or a truncated date column as part of the PK instead of the raw

timestamp

. * Design:

sql
    CREATE TABLE your_table (
      col1 VARCHAR(255) NOT NULL,
      col2 VARCHAR(255) NOT NULL,
      timestamp DATETIME NOT NULL,
      week_id DATE AS date_trunc('week', timestamp) -- Generated column
    )
    PRIMARY KEY (col1, col2, week_id)
    PARTITION BY (week_id)
    DISTRIBUTED BY HASH(col1);

* Result: Records with the same

(col1, col2)

arriving within the same week will now correctly update each other because their

week_id

remains the same. * Limitation: If a record's

timestamp

changes so much that it moves to a different week, StarRocks will still create a duplicate record in the new partition. 3. Handle Updates via Flink CDC If you are loading data from a source database (like MySQL or PostgreSQL), the StarRocks Flink Connector handles this use case automatically. * When a primary key's partition column changes in the source, Flink detects the "update" and sends a

DELETE

for the old record and an

UPSERT

for the new one to StarRocks. * Broker Load does not have this "look-back" capability; it only performs blind UPSERTs based on the provided keys. 4. Manual DELETE + INSERT in Broker Load If you must use Broker Load and the partition key changes across weeks, you must include two operations in your load file: 1. A row with

__op = 1

(Delete) containing the old

(col1, col2, old_timestamp)

. 2. A row with

__op = 0

(Upsert) containing the new

(col1, col2, new_timestamp)

. _ _Note: This requires your upstream pipeline to know the previous value of the timestamp.* Summary For your specific requirement where

(col1, col2)

is the only true business key and

timestamp

is mutable, Approach 1 (removing partitioning) is the most robust solution within StarRocks. If you must partition, Approach 2 reduces the duplication to only cases where records cross the weekly boundary. References • table_design/table_types/primary_key_table.md

@Rocky is there a way to manage partition outside the primary key table if i use PK without timestamp columns bcz customer queries based on this timestamp hence reduce the scan/prune the data instead scan all data across the table which is too expensive so any recommendataion to apply this logic?

In StarRocks 3.x, you can achieve the query performance of "partition pruning" without including the mutable

timestamp

in your Primary Key by using Sort Keys (ORDER BY) instead of physical PARTITION BY. The Solution: Decoupled Sort Key Starting with StarRocks 3.0, the Sort Key is decoupled from the Primary Key. This is the recommended way to handle your use case: 1. Remove the

PARTITION BY

clause: This removes the requirement that

timestamp

must be part of the Primary Key. 2. Define a Sort Key: Use the

ORDER BY

clause to sort the data physically by

timestamp

. 3. True Business PK: Set your

PRIMARY KEY

to only

(col1, col2)

. Example DDL

sql
CREATE TABLE your_table (
    col1      VARCHAR(255) NOT NULL,
    col2      VARCHAR(255) NOT NULL,
    timestamp DATETIME     NOT NULL,
    -- other columns ...
)
PRIMARY KEY (col1, col2)                -- True business key for UPSERTs
DISTRIBUTED BY HASH(col1)               -- Consistent hashing
ORDER BY (timestamp, col1)              -- Physical sorting for query performance
PROPERTIES (
    "enable_persistent_index" = "true"
);

Why this works for performance Even without physical partitioning, StarRocks provides Data Skipping (pruning) via internal storage indexes: * Zone-Map Index: For every block of data (64KB page and ~512MB Segment), StarRocks stores the

min

and

max

values of the Sort Key columns. * Query Pruning: When you run

SELECT ... WHERE timestamp > '2024-01-01'

, the storage engine checks the min/max metadata. If a block's range is outside your filter, it is skipped entirely without being read from disk. * Efficiency: On large tables, a well-chosen Sort Key often provides performance nearly identical to physical partitioning for range queries, but without the strict "must be in PK" constraint. When should you still use Partitioning? You should only use physical

PARTITION BY

if you have one of the following requirements: 1. Data Lifecycle (TTL): You need to drop old data instantly using

ALTER TABLE DROP PARTITION

. (With a non-partitioned table, you must use

DELETE

, which is a heavier background process). 2. Tiered Storage: You want to move older data (e.g., older than 1 month) to cheaper cold storage (S3/HDD) while keeping new data on SSD. Summary of Recommendation If your primary goal is query performance (reducing scans) and your

timestamp

is mutable: * Use the Sort Key (

ORDER BY

) and remove physical partitioning. * This allows

(col1, col2)

to remain your unique Primary Key. * Updates to

timestamp

will now correctly overwrite existing records based on

(col1, col2)

, and your queries will remain fast due to segment-level data skipping. ────────── References: • Primary Key Table - Usage • Sort Key and Prefix Index • Table Clustering & Data Skipping References • knowledge/usage/best-practice.md • best_practices/table_clustering.md • best_practices/partitioning.md • table_design/table_types/primary_key_table.md