@Rocky I created 2 tables with similar fields but different configs and the other one with less config and no partitioning is faster. the query is just select count(*) for both Base table setup PRIMARY KEY(

id

) DISTRIBUTED BY HASH(

id

) PROPERTIES ( "replication_num" = "1" ); optimized_table PRIMARY KEY(

id

,

utc_created

) PARTITION BY date_trunc('MONTH', utc_created) DISTRIBUTED BY HASH(

id

) BUCKETS 16 ORDER BY (

member_id

,

location_id

,

utc_created

) PROPERTIES ( "replication_num" = "1", "bloom_filter_columns" = "location_id" );

Metric base_table optimized_table
TabletCount	16	1,872
MorselsCount	16	1,872
Rows	13.6M	9.0M (fewer!)
BytesRead	12.99 MB	8.57 MB
Total time	288 ms	13.17 s (~46× slower)
ScanTime	69.7 ms	12.67 s
CreateSegmentIter	19.95 ms	4.37 s
SegmentInit	11.5 ms	1.49 s
RowsetsReadCount	77	10,927
InstanceAllocatedMemory	79.8 MB	6.2 GB

The performance regression you are seeing in the "optimized" table is a classic case of over-partitioning and data fragmentation (small file problem). Despite having fewer rows, the

optimized_table

is forcing StarRocks to do significantly more "administrative" work (metadata handling and file I/O) to process those rows. Here is the breakdown of why this is happening and how to fix it. 1. The "Small Tablet" Problem * Base Table: 16 tablets for 13.6M rows ($\approx$ 850k rows per tablet). This is a healthy size for this volume. * Optimized Table: 1,872 tablets for 9M rows ($\approx$ 4,800 rows per tablet). In StarRocks, each tablet is a physical directory with its own metadata and index structures. The recommended tablet size is 1 GB to 10 GB of data. By having only 4,800 rows per tablet, the overhead of just "opening" the tablets and their segments is dwarfed by the actual data processing. * Evidence: Your

CreateSegmentIter

(4.37s) and

SegmentInit

(1.49s) show that StarRocks is spending nearly 6 seconds just initializing the readers before it even starts counting rows. 2. High Fragmentation (

RowsetsReadCount

) This is the most critical metric in your profile: * Base Table: 77 rowsets. * Optimized Table: 10,927 rowsets. A "rowset" is created every time you perform an ingestion (e.g., a

STREAM LOAD

or

INSERT

). 10,927 rowsets for 9M rows means your average rowset has only ~820 rows. In a Primary Key table, every query must check these rowsets to determine which rows are the "latest" version or if they have been deleted. Scanning 10,000+ rowsets requires massive memory and CPU to manage the primary key indexes and delete vectors. * Evidence: This explains why your memory usage jumped from 79 MB to 6.2 GB. The BE has to load the metadata/indexes for all 10,000+ fragments into memory to perform the scan. 3. Primary Key Index Size Your PK changed from

(id)

to

(id, utc_created)

. * In the Primary Key model, the entire PK index is typically kept in memory (unless

persistent_index

is enabled). * By adding

utc_created

to the PK, you have increased the memory footprint of the index and the cost of the lookup during the scan. If

id

is already unique, adding more columns to the PK only slows down the table. ────────── How to Fix This 1. Reduce Tablet Count (Buckets) For 9M rows, you do not need 117 partitions with 16 buckets each. * Recommendation: If you must partition by month, reduce the bucket count to 1 or 2. * Better yet: If you don't actually need to drop old data by month (TTL), consider removing the

PARTITION BY

and just using

DISTRIBUTED BY HASH(id) BUCKETS 16

. 2. Fix Ingestion (Reduce Rowsets) The 10,927 rowsets suggest you are doing many very frequent, tiny inserts. * Batch your writes: Try to load data in larger chunks (e.g., every 1-5 minutes or at least 100MB+ per load). * Manual Compaction: You can trigger a manual compaction to merge those 10,000 rowsets into a few large ones:

sql
    -- Check compaction status
    SHOW TABLET FROM optimized_table;

(If the "VersionCount" is high, compaction is lagging). 3. Optimize the Primary Key * If

id

is unique, keep the PK as

PRIMARY KEY(id)

. * The

ORDER BY

clause already handles data sorting for range queries on

utc_created

. There is no need to put

utc_created

in the PK unless you need to support updates where the same

id

might have different

utc_created

values. Summary of Recommended Config

sql
CREATE TABLE optimized_table (
    id BIGINT,
    utc_created DATETIME,
    ...
    PRIMARY KEY(`id`) -- Keep PK lean
)
PARTITION BY date_trunc('MONTH', utc_created)
DISTRIBUTED BY HASH(`id`) BUCKETS 2 -- Reduced buckets for small data
ORDER BY (`member_id`, `location_id`, `utc_created`)
PROPERTIES (
    "replication_num" = "1",
    "enable_persistent_index" = "true" -- Helps with memory if rowsets grow
);

References

@Rocky I tried to add partition by utc created because we want to have fast deletes due to some constraints in 1 of our environment

It makes sense that you want fast deletes—in StarRocks,

ALTER TABLE ... DROP PARTITION

is a metadata-only operation that is nearly instantaneous and immediately reclaims disk space. However, your current configuration has created an extreme Small Tablet Problem. You have 1,872 tablets for 9 million rows, which averages to about 4,800 rows per tablet. StarRocks is designed to handle 100 million to 1 billion rows per tablet. Here is how to achieve the fast deletes you need without the 46x performance penalty. 1. The "Small Tablet" Tax When you run

SELECT COUNT(*)

, StarRocks must: 1. Open every tablet. 2. In a Primary Key table, it must load the Delete Vector for every single rowset to see which rows are "alive." 3. With 10,927 rowsets and 1,872 tablets, the CPU and Memory overhead of just "starting" the scan is massive compared to the work of counting the rows. This is why your memory usage jumped from 79MB to 6GB. 2. How to Balance Fast Deletes & Performance Option A: Coarser Partitioning (Recommended) If you only have 9M rows total, partitioning by Month is too granular. * Switch to Partition by Year: This reduces your partition count from ~117 down to a handful. * Reduce Buckets: For such a small dataset, use 1 bucket per partition. * Why this works: You can still drop an entire year instantly. If you need to delete a specific month within that year, you can use

DELETE FROM table WHERE month = x

, which is still very fast in the Primary Key model. Option B: Use a TTL (Time To Live) If your "deletes" are just for expiring old data, you don't need manual partitions at all.

sql
PROPERTIES (
    "partition_ttl_number" = "12" -- Automatically drops partitions older than 12
);

3. Immediate Fix for the 10,000+ Rowsets The 10,927 rowsets indicate that your ingestion process is sending very small batches of data. This is the "silent killer" of Primary Key table performance. * Batch your Inserts: Instead of inserting every few seconds, buffer your data and insert once every 1–5 minutes. * Check Compaction Score: Run this to see how many "versions" (rowsets) each partition has:

sql
    SELECT * FROM information_schema.partitions_meta
    WHERE TABLE_NAME = 'optimized_table'
    ORDER BY MAX_CS DESC;

If

MAX_CS

(Compaction Score) is high, the system is struggling to merge your small writes. 4. Optimized Schema for your Volume Based on 9M rows and the need for deletes, this is a much healthier setup:

sql
CREATE TABLE optimized_table (
    id BIGINT,
    utc_created DATETIME,
    ...
    PRIMARY KEY(`id`) -- Only include columns needed for uniqueness
)
PARTITION BY date_trunc('YEAR', utc_created) -- Coarser for small data
DISTRIBUTED BY HASH(`id`) BUCKETS 1          -- 1 bucket is plenty for 9M rows
ORDER BY (`member_id`, `location_id`, `utc_created`)
PROPERTIES (
    "replication_num" = "1",
    "enable_persistent_index" = "true"       -- Offloads PK index from RAM to disk
);

Key Takeaway: If a partition has less than 100MB of data, you should probably merge it with another or reduce the bucket count to 1. In StarRocks, fewer, larger files are always faster than many small ones scattered faster than many, tiny files. References • best_practices/partitioning.md • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md References • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md

@Rocky can you explain how bucketing works in a shared-data mode with 1 compute node or 3 compute nodes cluster

In a StarRocks shared-data (compute-storage separation) cluster, bucketing works differently than in a shared-nothing cluster because the physical data is decoupled from the compute nodes. Here is how it functions in 1-node and 3-node scenarios: 1. What "Bucketing" Represents in Shared-Data In shared-data mode, a Bucket (Tablet) is a logical unit of data and parallelism. * Data Location: All data resides in remote object storage (S3, HDFS, etc.). * Replication: The

replication_num

is effectively ignored for storage (since the object store handles durability), but the number of buckets still dictates how the FE (Frontend) divides the work. * Unit of Scheduling: The FE assigns these tablets to available Compute Nodes (CNs) during a query. ────────── 2. Scenario: 1 Compute Node Cluster If you have a table with 16 buckets and 1 CN: * Distribution: The FE assigns all 16 tablets to that single CN. * Parallelism: The CN will process the tablets using its local CPU cores. If the CN has 16 or more cores, it can potentially scan all 16 tablets in parallel. * Cache: The CN will download data from S3 and store it in its local

storage_root_path

. Since there is only one node, the cache hit rate will be high for subsequent queries because every tablet is "homed" on that one node. 3. Scenario: 3 Compute Node Cluster If you have a table with 16 buckets and 3 CNs: * Distribution: The FE will distribute the 16 tablets across the 3 nodes (e.g., CN1 gets 6, CN2 gets 5, CN3 gets 5). * Parallelism: The scan work is now shared. Instead of one node doing all the work, three nodes participate. This significantly reduces the time for CPU-intensive tasks (like decompression, filtering, and aggregation). * Cache Affinity: To maintain performance, StarRocks uses consistent hashing to assign tablets to CNs. This means Tablet A will always prefer to be processed by CN1. This ensures that the local data cache built on CN1 stays useful. ────────── 4. What happens during Scaling (1 CN → 3 CNs)? When you add 2 more nodes to your 1-node cluster: 1. Redistribution: The FE detects the new nodes and re-calculates the tablet-to-CN mapping. 2. Cache "Cold" Start: About 2/3 of your tablets (in this case, ~10–11 tablets) will be reassigned from the original CN to the new ones. 3. Performance Dip: The first time you query after scaling, the new CNs will have a cache miss. They must pull the data from S3/HDFS. 4. Recovery: Once the new CNs have pulled the data and filled their local disks, the query speed will increase beyond what the single node could achieve. ────────── Key Recommendations for Shared-Data Bucketing * Over-bucket for Elasticity: In shared-data mode, you should set the bucket count based on your future maximum cluster size, not your current size. If you think you might scale to 10 nodes, and each node has 16 cores, having only 16 buckets is too few (you'd only use 1 core per node). * Rule of Thumb: Aim for at least 1-2 tablets per CPU core in your expected cluster. For 3 nodes with 16 cores each (48 cores total), you should have at least 48–96 buckets. * Avoid "Small Tablet" Problem: Even in shared-data mode, do not make tablets too small. Ideally, each tablet should represent 100MB to 1GB of data on disk. If your total data is only 1GB, 16 buckets is plenty; if it's 1TB, you should have hundreds of buckets. Summary Table Metric | 1 CN Cluster | 3 CN Cluster Tablet Assignment | 100% on one node | Distributed (~33% per node) Max Parallelism | Limited by 1 node's CPU/RAM | 3x the CPU/RAM capacity Cache Behavior | Very stable | High affinity (mapped via hashing) Scaling Cost | N/A | Metadata update + temporary cache miss References • introduction/Architecture.md • deployment/shared_data/s3.md