@Rocky I have Primary Key dimension tables that are append-only. Max scale is around 100–150 million rows at the largest, with an average of roughly a few million new rows per month. My partitions are monthly and that is fixed — I cannot change the granularity. Is it worth partitioning these dimension tables (monthly), or should I skip partitioning and just rely on hash distribution?

For a Primary Key table of this scale (100–150 million rows) that is append-only, you should likely skip partitioning and rely on hash distribution alone. While partitioning is powerful, it introduces overhead that may not be justified for your specific scenario. Here is the breakdown of why you should probably skip it, along with the conditions under which you might reconsider. 1. The "Small Partition" Problem StarRocks generally recommends keeping partitions around 100 GB or at least several tens of millions of rows. * Your Scale: With 150 million rows total and 3–5 million rows added per month, monthly partitioning creates very "thin" partitions. * The Risk: If you have multiple BE (Backend) nodes and a high bucket count, each monthly partition will be split into many tiny tablets (e.g., <100MB each). This creates excessive metadata for the FE (Frontend) and makes compaction inefficient for the BEs. 2. Primary Key Table Constraints In StarRocks, the partition column must be part of the Primary Key. * If your dimension table is keyed by a simple

ID

(e.g.,

user_id

), and you want to partition by

created_at

, you would be forced to change your Primary Key to

(ID, created_at)

. * This changes the uniqueness constraint: you could technically have the same

ID

in two different months. If your business logic expects a truly unique

ID

across the whole table, partitioning by a time column complicates this. 3. Primary Key Index Memory One of the main reasons to partition a PK table is to limit the memory/IO needed for the Primary Key Index during ingestion. * Partitioned: StarRocks only loads the index for the "hot" partition being written to. * Unpartitioned: It must check the index for the entire 150 million rows to ensure uniqueness. * Verdict: At 150 million rows, even a single-partition index is relatively small. If you enable the Persistent Index (default in recent versions), StarRocks handles this scale easily on disk with minimal memory impact. 4. When SHOULD you partition? You should only use monthly partitioning in this case if: * Data Lifecycle (TTL): You need to drop old data frequently (e.g., "delete everything older than 3 years"). Dropping a partition is a metadata operation and much faster than a

DELETE

query. * Storage Tiering: You want to move older months to cheaper storage (S3/HDD) while keeping recent months on SSD. Summary Recommendation * Recommendation: Use a single partition (do not use

PARTITION BY

) and use Hash Distribution on your primary key. * Bucket Count: For 150 million rows, aim for a total tablet count that keeps each tablet around 1GB–10GB. For example, if you have 3–5 BE nodes,

DISTRIBUTED BY HASH(pk) BUCKETS 10-20

is usually sufficient. * Optimization: Ensure

enable_persistent_index

is set to

true

(it is by default since v2.3) to keep memory usage low as you grow toward 150M+ rows. If you eventually reach billions of rows or find that queries filtering by month are too slow, you can use

ALTER TABLE

(in newer versions) or a swap-table migration to introduce partitioning later, but at 150M rows, simplicity is better. References • table_design/table_types/primary_key_table.md • best_practices/query_tuning/schema_tuning.md