@Rocky What is the practical limit to the number of partitions a table can have?

The practical limit for partitions in a StarRocks table is generally considered to be 100,000, though there are specific hard limits and performance considerations to keep in mind. 1. Hard Configuration Limits * Per Table Limit: By default, a table can have up to 100,000 partitions. This is controlled by the Frontend (FE) configuration parameter

max_partition_number_per_table

(introduced in v3.3.2). * Creation Batch Limit: When creating partitions in a single batch (e.g., during a large data load or via

ALTER TABLE

), the default limit is 4,096. This is controlled by

max_partitions_in_one_batch

and

auto_partition_max_creation_number_per_load

. 2. Practical Performance Limits While you can technically scale to 100,000 partitions, exceeding this range or even approaching it can lead to several performance issues: * FE Memory Pressure: Partitions are catalog objects. Each partition adds to the metadata overhead in the FE's memory. Having hundreds of thousands of partitions can significantly increase FE memory consumption and slow down metadata operations (like schema changes or cluster restarts). * Tablet Explosion: Each partition contains one or more buckets (tablets). If you have 10,000 partitions and each has 20 buckets with 3 replicas, you end up with 600,000 tablets. A high tablet count per Backend (BE) can lead to: * High CPU/IO for Compaction: The BE must manage versioning and compaction for every tablet. * Slower Queries: The query planner and scheduler must process more metadata, and the BE has more file handles to manage. * Rule of Thumb: Most production use cases stay within the 100 to 10,000 partition range. If you find yourself needing more than 100,000, consider increasing the partition granularity (e.g., moving from hourly to daily) or using a different distribution strategy. Best Practices * Bucket Size: Aim for each bucket (tablet) to be between 100 MB and 1 GB of compressed data. * Total Tablets: Try to keep the total number of tablets per BE under 200,000. * Pruning: Partitioning is most effective when your queries frequently filter on the partition column, allowing the FE to "prune" (skip) irrelevant data. Over-partitioning (e.g., partitioning by a high-cardinality ID) often hurts performance more than it helps. References • best_practices/partitioning.md • faq/operation_maintenance_faq.md • best_practices/query_tuning/schema_tuning.md • table_design/data_distribution/expression_partitioning.md