< Rocky> we have a duplicate key table with a partition by d StarRocks #questions-and-troubleshooting

<@U0A71G31CDV> we have a duplicate key table with ...

GregM

06/09/2026, 4:43 PM

@Rocky we have a duplicate key table with a partition by date_trunc(timestmap) and it seems to make several partitions for a date_trunc(timestamp) value, is this the designed behavior?

Rocky

06/09/2026, 4:43 PM

Based on the StarRocks documentation and typical table design, the behavior you are seeing depends on exactly how the

PARTITION BY

clause is defined and what you are observing in the system. 1. Single Column Partitioning (Designed Behavior) If your table is partitioned only by a single

date_trunc

expression, such as:

Copy code

sql
PARTITION BY date_trunc('day', your_timestamp_col)

The designed behavior is to have one partition per truncated value (e.g., one partition per day). Each partition represents a unique range, such as

[2023-10-01 00:00:00, 2023-10-02 00:00:00)

. 2. When "Multiple Partitions" are expected There are three common scenarios where you might see "several partitions" for what seems like the same time value: * Composite Partition Keys: If you have multiple columns in your partition key, such as

PARTITION BY tenant_id, date_trunc('day', timestamp)

, StarRocks will create a separate partition for every unique combination of

tenant_id

and

day

. This will result in many partitions for the same date. * Finer Granularity than Expected: Ensure you are using

'day'

as the first argument in

date_trunc

. If you used

'hour'

, StarRocks will naturally create 24 partitions for a single day. * Tablets vs. Partitions: In the output of

SHOW PARTITIONS

, each row represents one partition. However, each partition is further divided into tablets (defined by the

DISTRIBUTED BY

clause). If you are looking at the number of files or tablets and see many entries for one date, that is normal—it reflects the bucketing (distribution) of that partition across the cluster. 3. Edge Cases & Known Issues * NULL Values: If your timestamp column contains

NULL

values,

date_trunc

will return

NULL

, and all those rows will be placed into a single automatic partition (often named

p_null

or similar). * Random Distribution (Duplicate Key Tables): There is a known edge case in some versions where using

DISTRIBUTED BY RANDOM

alongside automatic expression partitioning can lead to metadata inconsistencies if the same partition is created multiple times via different load jobs (though this is typically handled by FE locks). * Shared-Data Clusters: In StarRocks' shared-data (cloud-native) architecture, version 3.1.2 had a reported issue where automatic partition creation during heavy concurrent loads could occasionally result in unexpected partition behavior, which was addressed in later patches. Recommended Checks 1. Check Table Definition: Run

SHOW CREATE TABLE <table_name>;

. Verify if there is more than one column in the

PARTITION BY

clause. 2. Verify Partition List: Run

SHOW PARTITIONS FROM <table_name>;

. * If you see different

PartitionName

values like

p20231001000000

, check if the names indicate hourly instead of daily intervals. * If you see the same date but different

PartitionKey

ranges, you may have overlapping manual partitions. 3. Check for NULLs: Ensure the data being loaded doesn't have

NULL

or "zero-dates" (like

0000-00-00

) which might be routed to a catch-all partition. If you see multiple partitions with the exact same name or exact same range for a single column key, this would be an anomaly. You can verify your StarRocks version using `SELECT VERSION();`—if you are on a version earlier than 3.1, upgrading to a stable 3.2+ or 3.3+ release is recommended for the most stable "Expression Partitioning" experience. References • best_practices/partitioning.md • sql-reference/sql-functions/date-time-functions/date_trunc.md • table_design/data_distribution/expression_partitioning.md

GregM

06/09/2026, 5:37 PM

@Rocky to be more specific, it's the same PartitionName with many

PartitionIds

Rocky

06/09/2026, 5:37 PM

Yes, this is the designed behavior for Duplicate Key tables in StarRocks (starting from version 3.1) when using Random Bucketing. Why you see multiple
PartitionIds
for one
PartitionName
When you create a Duplicate Key table and do not specify a distribution key (or explicitly use

DISTRIBUTED BY RANDOM

), StarRocks uses Random Bucketing. To allow these tables to scale and handle data growth dynamically, StarRocks implements a hierarchy of Partition > Subpartition > Bucket: 1. Dynamic Scaling: Instead of a fixed number of buckets, StarRocks can increase the number of buckets for a partition on-demand as data volume grows. 2. Subpartitions: To add these buckets, StarRocks creates a new subpartition within the same logical partition. 3. Result in `SHOW PARTITIONS`: Because each subpartition is managed as an independent unit of distribution, the

SHOW PARTITIONS

command returns one row for each subpartition. * All these rows will share the same PartitionName
(e.g., the name generated by your

date_trunc

expression). * Each row will have a unique PartitionId
representing that specific subpartition. How to verify You can confirm this by checking your table's distribution method:

Copy code

sql
SHOW CREATE TABLE <your_table_name>;

Look for the

DISTRIBUTED BY ...

clause. If it says

DISTRIBUTED BY RANDOM

(or if the clause is missing, which defaults to Random for Duplicate Key tables in recent versions), then the multiple IDs are expected. Is this a problem? No, it is generally beneficial for performance: * It prevents tablets from becoming too large as more data is loaded into a single date. * It allows StarRocks to manage the data distribution across the cluster more flexibly. How to get one row per partition If your use case requires a 1:1 mapping between a date and a partition entry (e.g., for certain administrative scripts), you must use Hash Bucketing instead:

Copy code

sql
-- Use HASH instead of the default RANDOM
DISTRIBUTED BY HASH(one_of_your_columns) BUCKETS <number>;

Note that with Hash Bucketing, the number of buckets is fixed for each partition and cannot be adjusted dynamically via the subpartition mechanism, so

SHOW PARTITIONS

will only display one row per name. References • table_design/data_distribution/Data_distribution.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • table_design/data_distribution/expression_partitioning.md References • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • table_design/data_distribution/Data_distribution.md

GregM

06/11/2026, 8:54 PM

@Rocky could it be over bucketed? Would that matter?

Copy code

Buckets          |10
DataSize         |122.3MB
StorageSize      |122.3MB
RowCount         |251368
EnableDataCache  |true
AsyncWrite       |false
AvgCS            |4.20
P50CS            |4.00
MaxCS            |5.00
DataVersion      |20

Rocky

06/11/2026, 8:54 PM

Based on the metrics you provided, here is the breakdown of whether your table is over-bucketed and if it matters. 1. Is it over-bucketed? Technically, yes. StarRocks generally recommends that the size of data in each bucket (tablet) should be between 100 MB and 1 GB (compressed). * Your Data: 122.3 MB total / 10 buckets = ~12.2 MB per bucket. * This is about 1/10th of the recommended minimum size. 2. Does it matter? At this specific scale, probably not. While your buckets are "small" by StarRocks standards, you only have 10 buckets for this partition. Over-bucketing becomes a serious performance and stability issue when you have: * Thousands of partitions. * Total tablet counts in the hundreds of thousands or millions across the cluster. * Extremely high ingestion frequency on these small buckets. In your case, having 10 buckets for 122MB is unlikely to cause a noticeable performance hit unless you have thousands of similar partitions, which would bloat the metadata on your FE (FrontEnd) and BE (BackEnd). 3. Understanding your Metrics * CS Metrics (AvgCS 4.20 / MaxCS 5.00): These are Compaction Scores. They represent the number of data versions (rowsets) that need to be merged. A score of 4–5 is very healthy. Usually, you only need to worry if this number climbs above 20–50, which indicates the system is struggling to keep up with background data merging. * 10 Buckets: If this is a Duplicate Key table with Random Bucketing, StarRocks likely chose "10" automatically based on your cluster size (number of BE nodes). Why you see multiple
PartitionIds
As mentioned before, for Duplicate Key tables, StarRocks uses Subpartitions to allow the bucket count to scale. * Even if your data is small now (122MB), the system prepares the structure to handle growth. _ If you load 100GB into this same date tomorrow, StarRocks might automatically create a _new*

PartitionId

(subpartition) for that same

PartitionName

with a higher bucket count to handle the load. Recommendation If this is a small dimension table or a low-volume log table, you can leave it as is. The overhead of 10 small tablets is negligible. If you want to optimize and ensure you have exactly 1 row per partition name with larger buckets: 1. Switch to Hash Bucketing: Define a specific column for distribution:

DISTRIBUTED BY HASH(user_id) BUCKETS 1

. 2. Manually Set Buckets: For a partition that only expects ~100MB of data,

BUCKETS 1

BUCKETS 3

would be more than enough. Summary: You are under the recommended size per bucket, but because your total bucket count is so low (10), it is not a "problem" yet. Your cluster will handle this easily. References • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md References • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • sql-reference/information_schema/partitions_meta.md • knowledge/admin_and_operation/shared_data_compaction.md

GregM

06/11/2026, 9:07 PM

@Rocky seeing tons of this in cn warning logs

Copy code

W20260611 21:05:42.555562 140169065768640 lake_service.cpp:381] Fail to publish version: Internal error: prepare_primary_index: load primary index failed: Not found: load_segments failed tablet:2693652 rowset:10 segid:0: starlet err Object <mystorageaccount>
dataops-k8-blob/starrocks/c06faf1f-a109-4cf6-a569-f20a4df260be/db49953/2618536/2693646/data/0000000000212ec5_c86dd37f-89c2-4826-af9f-b0c9338c7f2b.dat does not exist
be/src/storage/lake/rowset.cpp:481 load_segments(&segments, false)
be/src/storage/lake/txn_log_applier.cpp:476 value_or_err_L476
be/src/storage/lake/txn_log_applier.cpp:509 prepare_primary_index()
be/src/storage/lake/txn_log_applier.cpp:310 check_and_recover([&]() { return apply_write_log(log.op_write(), log.txn_id()); }). tablet_info={tablet_id: 2693652} txn_ids=txn_id: 2215208
commit_time: 1781208002
combined_txn_log: true
txn_type: TXN_NORMAL
force_publish: false
gtid: 426500324625219584
,txn_id: 2215419
commit_time: 1781209203
combined_txn_log: true
txn_type: TXN_NORMAL
force_publish: false
gtid: 426502843652898816
version=23

Rocky

06/11/2026, 9:08 PM

There is a significant discrepancy between your table description and this error log that points to the root of the problem: 1. Primary Key vs. Duplicate Key The error

prepare_primary_index

and the mention of

load primary index

are specific to Primary Key tables in StarRocks Shared-Data (Lakehouse) mode. * Duplicate Key tables do not use a Primary Index. * Primary Key tables maintain a persistent index in object storage to handle upserts/deletes. Action: Please run

SHOW CREATE TABLE <table_name>;

to verify the

ENGINE

and

KEYS

type. If the log is coming from this table, it is almost certainly a Primary Key table, not a Duplicate Key table. If it is a Duplicate Key table, these logs likely belong to a different table in your cluster. 2. Why the "Object does not exist" error occurs This error means the metadata (Transaction Log) expects a data file (

.dat

) to exist in your Azure Blob storage, but the file is missing. This usually happens for one of three reasons: * Aggressive Vacuuming: StarRocks automatically cleans up old data files. If the

lake_autovacuum_grace_period_minutes

(default is 1 day) is set too low, or if a transaction takes a very long time to publish, the vacuum process might delete a file that is still "technically" needed by a pending version. * Manual Deletion: Data files in the

starrocks/

prefix on Azure Blob should never be moved or deleted manually. * Failed Write / Storage Latency: If the object storage reported a successful write to StarRocks but the file wasn't actually committed (rare for Azure Blob), the publish phase will fail when it tries to read the file back to build the index. 3. How to fix it Since this is a Shared-Data (Lakehouse) environment, the system is struggling to "publish" a new version because it cannot find the data it just tried to load. Immediate Steps: 1. Identify the Table: Use the IDs in the log to find the exact table:

Copy code

sql
    -- Replace with the TableId from your log (the second long number in the path)
    -- In your path: .../db49953/2618536/... -> Table ID is 2618536
    SELECT TABLE_NAME, DATABASE_NAME
    FROM information_schema.tables
    WHERE TABLE_ID = 2618536;

2. Check Tablet Health:

Copy code

sql
    ADMIN SHOW TABLET 2693652;

3. Increase Vacuum Grace Period: If you are doing very frequent, large loads, increase the time StarRocks waits before deleting old files. In `fe.conf`:

Copy code

properties
    lake_autovacuum_grace_period_minutes = 1440  -- (24 hours, increase if needed)

4. Recovering the Partition: Since a

.dat

file is physically missing, the specific partition mentioned in the log is likely corrupted. * If you have the source data, the safest fix is to TRUNCATE the affected partition and reload the data. * If this is a Primary Key table, you can try to trigger a manual repair or check if there are any specific errors in the

sys_log

of the FE leader. Summary: The "many `PartitionIds`" you saw earlier is normal for Random Bucketing, but this "Object does not exist" log is a data integrity error. Verify if the table is actually a Primary Key table, as the error specifically involves the Primary Index logic. References • faq/shared_data_faq.md • administration/management/monitoring/metrics.md • faq/loading/Loading_faq.md • administration/Meta_recovery.md • data_source/datalake_faq.md

GregM

06/17/2026, 8:37 PM

@Rocky is this a reasonable thing to change? lake_autovacuum_max_previous_versions
?

Rocky

06/17/2026, 8:38 PM

Yes, changing

lake_autovacuum_max_previous_versions

is a very reasonable and often recommended troubleshooting step when you see "Object does not exist" errors in StarRocks Shared-Data (Lakehouse) mode. What it does In Shared-Data mode, StarRocks creates a new "version" of the table metadata and data for every transaction (load, compaction, delete). The AutoVacuum process is responsible for deleting old versions to save storage costs. * lake_autovacuum_max_previous_versions
: This is a count-based limit. It tells StarRocks: "Keep at least $N$ previous versions of metadata/data before deleting them." * lake_autovacuum_grace_period_minutes
: This is the time-based limit (default is often 30 minutes). Why changing it helps The "Object does not exist" error during a

publish version

task usually means: 1. A transaction finished writing data. 2. Before that data could be "published" (made visible to users), the AutoVacuum process ran. 3. Because the system was under high load or had a high version churn, AutoVacuum mistakenly identified those files as "old" or "orphaned" and deleted them from your Azure Blob storage. By increasing

lake_autovacuum_max_previous_versions

(e.g., setting it to

), you create a larger "safety buffer." This ensures that even if there is a delay in publishing a version, the underlying data files won't be deleted immediately. Recommended Settings If you are seeing these errors frequently, try applying these settings in your

fe.conf

(or via

ADMIN SET FRONTEND CONFIG

if they are mutable):

Copy code

sql
-- Increase the version buffer (default is often 0 or very low)
ADMIN SET FRONTEND CONFIG ("lake_autovacuum_max_previous_versions" = "20");

-- Increase the time buffer to at least 1 hour (60 minutes) 
-- to account for slow storage response or long transactions
ADMIN SET FRONTEND CONFIG ("lake_autovacuum_grace_period_minutes" = "60");

The Trade-off * Pro: Prevents "Object does not exist" errors and potential partition corruption. * Con: Storage Usage. By keeping more versions, you will store more data in your Azure Blob storage for a longer period. However, since your data size is relatively small (hundreds of MBs), this cost impact is likely negligible. Summary If you are running StarRocks in a Shared-Data (Lakehouse) environment with high-frequency loads, the default vacuum settings can sometimes be too aggressive. Increasing this parameter is the standard way to fix the "disappearing data" problem you are seeing in your logs. References • administration/management/FE_configuration.md • faq/shared_data_faq.md • administration/management/monitoring/metrics.md • administration/Meta_recovery.md • data_source/datalake_faq.md • knowledge/admin_and_operation/shared_data_compaction.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • sql-reference/information_schema/partitions_meta.md • faq/loading/Loading_faq.md • table_design/data_distribution/Data_distribution.md • knowledge/usage/best-practice.md • best_practices/bucketing.md

Rocky

06/17/2026, 8:38 PM

Rocky

06/17/2026, 8:38 PM

• integrations/loading_tools/Flink_connector.md • integrations/loading_tools/Spark_connector.md • integrations/loading_tools/Kafka_connector.md • integrations/bi/BI_intro.md • integrations/bi/Metabase.md • integrations/bi/Superset.md • integrations/bi/Tableau.md • integrations/bi/PowerBI.md • integrations/ide/IDE_intro.md • integrations/ide/DBeaver.md • integrations/ide/DataGrip.md • integrations/ecosystem/Ecosystem_intro.md • integrations/ecosystem/dbt.md • integrations/ecosystem/Airflow.md • integrations/ecosystem/Grafana.md • integrations/ecosystem/Prometheus.md • deployment/Deployment_intro.md • deployment/Deployment_recommendation.md • deployment/Helm_chart_deployment.md • deployment/Operator_deployment.md • deployment/On-premises_deployment.md • deployment/Docker_deployment.md • quick_start/Quick_start_intro.md • quick_start/Docker.md • quick_start/Binary.md • quick_start/Kubernetes.md • quick_start/Shared-data.md • get_started/Get_started_intro.md • get_started/StarRocks_architecture.md • get_started/Concepts.md • get_started/Key_features.md • get_started/Technical_whitepaper.md • release_notes/Release_notes_intro.md • release_notes/release-3.3.md • release_notes/release-3.2.md • release_notes/release-3.1.md • release_notes/release-3.0.md • release_notes/release-2.5.md • release_notes/release-2.4.md • release_notes/release-2.3.md • release_notes/release-2.2.md

Rocky

06/17/2026, 8:38 PM

2 Views

Open in Slack

Previous Next