http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> why would a task run have a status ...
# questions-and-troubleshooting
b
@Rocky why would a task run have a status of skipped?
r
In StarRocks, a task run (especially for Materialized View refreshes) is marked or considered skipped primarily when the system determines that the work it was supposed to do is no longer necessary or is redundant. While the 
information_schema.task_runs
table officially lists states like 
PENDING
, 
RUNNING
, 
FAILED
, 
SUCCESS
, and 
MERGED
, you may encounter the "skipped" behavior or status for the following reasons: 1. No Data Changes (Redundant Refresh) The most common reason for a "skipped" task in Materialized Views (MVs) is that the base tables have not changed. StarRocks tracks the data version of base tables. When a refresh task is triggered (either automatically or by a schedule), the scheduler compares the current version of the base tables with the version used during the last successful refresh. * Behavior: If the versions match, the system skips the execution of the refresh logic to save resources. * Result: The task may complete almost instantly, and in some contexts or logs, it is described as "skipped." 2. Partition-Level Optimization For partitioned Materialized Views, StarRocks only refreshes the specific partitions that have changed in the base tables. * Behavior: If an MV has 100 partitions but only 2 partitions in the base table have new data, the task will only process those 2. The remaining 98 partitions are effectively skipped. 3. Task Merging (
MERGED
Status) From StarRocks v3.1.12 onwards, the system includes a 
MERGED
state. * Reason: If a new refresh task is triggered while an identical task is already waiting in the 
PENDING
queue, StarRocks will merge them into one. * Effect: The older, redundant task run is "skipped" in favor of the newer one, though its status will specifically show as 
MERGED
. 4. Metadata or Version Inconsistency (Bugs/Edge Cases) In some rare scenarios (often related to backup/restore or specific bugs in older versions), a task might be skipped because the system incorrectly perceives the data as "up-to-date." * Example: If a 
RESTORE
operation fails to clear the MV's version map, subsequent incremental refreshes might be skipped because the system thinks the MV is already in sync with the base table. 5. Task Signature Collisions In very recent versions (like v4.x), internal tasks might be skipped if there is a collision in task signatures (e.g., during concurrent 
ALTER
jobs). This is usually treated as a bug and resolved in point releases. How to check for more details: If you see a task that didn't behave as expected, check the 
EXTRA_MESSAGE
field in the 
information_schema.task_runs
table. It often contains diagnostic info:
Copy code
sql
SELECT * FROM information_schema.task_runs 
WHERE task_name = 'your_task_name' 
ORDER BY create_time DESC LIMIT 1\G
Look for fields like 
forceRefresh
, 
basePartitionsToRefreshMap
, or 
planBuilderMessage
in the JSON output to see if the optimizer decided no partitions needed refreshing. References • sql-reference/information_schema/tasks.md • faq/operation_maintenance_faq.md • administration/management/FE_configuration.md • using_starrocks/async_mv/materialized_view_task_run_details.md
b
@Rocky how do i get a count of all partitions
r
To get a count of partitions in StarRocks, you can use the 
information_schema.partitions_meta
table or the 
SHOW PARTITIONS
command. 1. Using SQL (Recommended) Querying the 
information_schema.partitions_meta
view is the easiest way to get an exact count using standard SQL. Count for a specific table:
Copy code
sql
SELECT COUNT(*) 
FROM information_schema.partitions_meta 
WHERE db_name = 'your_database_name' 
  AND table_name = 'your_table_name';
Count for all tables in a specific database:
Copy code
sql
SELECT table_name, COUNT(*) as partition_count
FROM information_schema.partitions_meta 
WHERE db_name = 'your_database_name'
GROUP BY table_name;
Total partitions across the entire cluster:
Copy code
sql
SELECT COUNT(*) FROM information_schema.partitions_meta;
────────── 2. Using the SHOW command If you are working in a CLI or want to see the partition details along with the count, use `SHOW PARTITIONS`:
Copy code
sql
SHOW PARTITIONS FROM your_table_name;
Note: This command displays the list of partitions. Most SQL clients will show the total number of rows returned at the bottom of the result set, which corresponds to the partition count. 3. Check against Table Limits If you are trying to find the partition count because you are hitting a limit, you can check your current partition configurations:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE '%max_partitions%';
* `max_partitions_per_db`: Default is often 10,000. * `max_partitions_in_one_batch`: Default is 4,096. Summary of Metadata Tables Table Name | Use Case 
information_schema.partitions_meta
| Best for counting. Contains partition names, IDs, row counts, and data sizes. 
information_schema.partitions
| Note: This is a placeholder in StarRocks for MySQL compatibility and may not contain full data; always use 
partitions_meta
instead. References • sql-reference/information_schema/partitions_meta.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md References • sql-reference/information_schema/partitions.md • sql-reference/information_schema/partitions_meta.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md
b
@Rocky what sort of performance difference should i expect for the same query against an iceberg table through starrocks and a starrocks native table?
r
When comparing the performance of the same query against an Iceberg table (via an external catalog) versus a StarRocks native table, you should generally expect the native table to be faster, though the gap has narrowed significantly in recent versions (v3.3+ and v4.0). Here is the breakdown of the performance differences and the reasons behind them: 1. Performance Gap Summary * Native StarRocks Tables: Designed for sub-second, high-concurrency analytics. They offer the best performance because StarRocks has full control over the storage format, indexing, and metadata. * Iceberg via StarRocks: Offers "Data Warehouse speed at Data Lake cost." In many benchmarks (like TPC-H), querying Iceberg through StarRocks can reach 80-90% of the speed of native tables, provided that Data Caching is enabled. Without caching, the gap is wider due to remote storage latency. 2. Key Architectural Differences Feature | StarRocks Native Table | Iceberg External Table Data Locality | Data is stored on local BE disks (Shared Nothing) or optimized S3 storage (Shared Data). | Data is stored on remote object storage (S3, HDFS, Azure). Metadata Parsing | Instant; managed internally by FE. | Requires parsing Iceberg manifest files (high overhead for tables with millions of files). Indexing | Supports Sorting Keys, Bloom Filters, Bitmap Indexes, and Primary Key indexes. | Relies on Iceberg's partition pruning and Min/Max statistics in manifest files. Predicate Pushdown | Extremely deep; filters are applied at the lowest storage layer. | Leverages Iceberg statistics to skip files, but generally less granular than native indexing. Late Materialization | Highly optimized for native formats. | Supported for Parquet/ORC but with slightly more overhead. 3. Factors That Narrow the Gap StarRocks uses several "Lakehouse" technologies to make Iceberg queries perform nearly as fast as native ones: * Data Cache (Block-level): This is the most critical feature. It caches hot data from Iceberg (S3/HDFS) onto the local NVMe SSDs of the BE nodes. Once data is cached, the "IO gap" between Iceberg and native tables virtually disappears. * Metadata Caching: StarRocks caches Iceberg's manifest files in memory and on disk. This eliminates the time spent communicating with the Metastore (Glue/Hive) and parsing Avro metadata for every query. * Distributed Metadata Planning: For massive Iceberg tables, StarRocks can distribute the task of parsing metadata across all BE nodes, preventing the FE from becoming a bottleneck. * CBO (Cost-Based Optimizer): StarRocks' optimizer is aware of Iceberg statistics (including Puffin files). It can perform intelligent Join Reordering and Predicate Pushdown just as it does for native tables. 4. When to Use Which? * Use Native Tables if: * You need sub-second latency for user-facing dashboards. * You have high concurrency (hundreds of queries per second). * You need Primary Key support for real-time upserts/deletes. * Use Iceberg Catalogs if: * You want to avoid the ETL overhead of moving data into StarRocks. * You have petabyte-scale data where storage cost is a major concern. * You need Openness (allowing Spark, Trino, or Flink to access the same data). Recommendation for Iceberg Performance If you choose to use Iceberg, ensure you: 1. Enable Data Cache: Set 
enable_scan_datacache = true
. 2. Collect Statistics: Use 
ANALYZE TABLE
on your Iceberg catalog so the optimizer can create better execution plans. 3. Optimize File Sizes: Ensure your Iceberg files aren't too small (target 128MB–512MB) to avoid the "small file problem" which impacts scan speed. References • data_source/catalog/iceberg/iceberg_catalog.md
b
@Rocky does it seem reasonable that the same query would be 3 times slower on iceberg?
r
Yes, a 3x performance difference between a StarRocks native table and an Iceberg table can be considered "reasonable" if certain optimizations are not in place. While StarRocks is one of the fastest engines for querying Iceberg, native tables still have inherent architectural advantages. Here are the specific reasons why you might see a 3x gap and how to narrow it: 1. Data Cache (The "Cold Data" Penalty) Native StarRocks tables store data on local disks (or optimized shared storage). Iceberg stores data on remote object storage (S3/HDFS/Azure). * The Issue: If the StarRocks Data Cache is disabled or "cold" (not yet populated), every query must fetch data over the network from S3. This network latency alone can easily make a query 3–5x slower. * How to fix: Ensure 
enable_scan_datacache = true
is set. After running the query once or twice, the speed should improve significantly as data is moved to the local NVMe cache. 2. Missing Statistics (CBO Optimization) The Cost-Based Optimizer (CBO) relies on table statistics (row counts, min/max values, null counts) to choose the most efficient join order and distribution strategy. * The Issue: Native tables often have auto-collected statistics. Iceberg tables might not have had 
ANALYZE TABLE
run recently within StarRocks. Without these stats, the optimizer might choose a "Shuffle Join" when a "Broadcast Join" would have been 10x faster. * How to fix: Run 
ANALYZE TABLE iceberg_catalog.db.table;
to provide the optimizer with the metadata it needs. 3. Metadata Parsing Overhead Iceberg's metadata structure (Snapshots -> Manifest Lists -> Manifest Files) is much more complex than StarRocks’ internal metadata. * The Issue: For a native table, the FE knows exactly which data tablets to hit instantly. For Iceberg, StarRocks must parse Avro manifest files to find the relevant Parquet files. If your Iceberg table has many small files or thousands of snapshots, this "planning time" can add seconds to the query. * How to fix: Ensure Metadata Caching is enabled (it is by default in v3.3+). If you have a massive table, StarRocks v3.3+ supports "Distributed Planning," which uses the BE nodes to parse these manifests in parallel. 4. Lack of Native Indexes StarRocks native tables support advanced indexing that the Iceberg format (Parquet/ORC) simply does not have. * The Issue: Native tables use Bloom Filters, Bitmap Indexes, and Sort Keys to skip data at a very granular level. Iceberg only supports basic min/max pruning at the file level. If your query filters on a specific ID or high-cardinality string, the native table will skip 99% of the data, while Iceberg might have to scan every file. * Solution: If you require sub-second performance on these types of queries, consider using a Synchronous Materialized View in StarRocks to index the Iceberg data. 5. The "Small File" Problem If the process writing to Iceberg (e.g., Flink or Spark) is creating many small Parquet files (e.g., 10MB each), the I/O overhead will be massive. * The Issue: Native StarRocks tables automatically "compact" small segments in the background. Iceberg requires an external maintenance job (like Spark's 
rewrite_data_files
) to merge small files. * How to check: Run 
SELECT * FROM iceberg_catalog.db.table$files;
to see the average size of your data files. Summary Checklist If your Iceberg query is 3x slower, check these three things: 1. Is Data Cache enabled and warmed up? (
SET enable_scan_datacache = true;
) 2. Have you collected statistics? (
ANALYZE TABLE ...
) 3. Is the data layout optimized? (Are Iceberg files ~128MB+ and sorted by frequently filtered columns?) References • data_source/catalog/iceberg/iceberg_catalog.md • administration/management/datacache/data_cache.md
b
@Rocky can you list some ways that I can check whether our materialized views are causing FE memory issues?
r
If you suspect that Materialized Views (MVs) are causing memory pressure on your Frontends (FE), you can use the following diagnostic methods to confirm and narrow down the cause. 1. Check 
MemoryUsageTracker
Logs (v3.2.4+) StarRocks has an internal 
MemoryUsageTracker
that periodically logs the memory consumption of different metadata modules. * Action: Check your 
fe.log
for entries containing the keyword 
MemoryUsageTracker
. * What to look for: Look for modules related to Materialized Views. While module names may vary by version, you should look for: * `MaterializedView`: Memory used for storing MV metadata. * `MvPlanCache`: Memory used to cache the execution plans for query rewrite. * `MvRewrite`: Memory consumed during the transparent rewrite process. * Config: Ensure 
memory_tracker_enable = true
(default) in 
fe.conf
. 2. Analyze FE Memory Profiles (v3.3.6+) If the FE is experiencing sudden memory spikes, StarRocks automatically generates memory profiles (flame graphs). * Action: Go to your FE log directory: 
fe/log/proc_profile
. * Diagnostic: Open the 
.html
flame graphs in a browser. Look for wide bars (indicating high memory allocation) with stacks containing: * `org.apache.starrocks.planner.MaterializedViewRewriter`: Indicates the query rewrite engine is consuming memory (likely due to too many MVs or overly complex rewrite logic). * `org.apache.starrocks.scheduler.mv.MVRefreshJob`: Indicates MV refresh tasks are consuming FE memory during planning. 3. Monitor Query Rewrite Cache Transparent query rewrite uses an internal cache to speed up planning. If you have hundreds of MVs, this cache can grow large. * Check Configuration: Run 
ADMIN SHOW FRONTEND CONFIG LIKE '%mv_plan_cache%';
* `mv_plan_cache_max_size`: Default is 1000. If this is very high and you have complex MVs, it may consume significant heap. * Test: You can temporarily disable the cache using 
SET GLOBAL enable_mv_query_context_cache = false;
to see if FE memory stabilizes. 4. Monitor Active Refresh Tasks Refreshing an asynchronous MV is essentially an 
INSERT OVERWRITE
query. The FE must manage the planning and coordination of these tasks. * Action: Run 
SHOW PROC '/current_queries';
during a refresh. * Metric: Check the 
MemoryUsage
column. While this typically shows BE memory, high concurrency of refresh tasks can also bloat the FE's internal task scheduler metadata. * Action: Use 
SHOW MATERIALIZED VIEWS
to find MVs with very frequent refresh intervals (e.g., every 1 minute) which can keep the FE under constant pressure. 5. Check Audit Logs for "Heavy" Planning Sometimes the query rewrite logic takes a long time and uses a lot of memory to find the "best" MV match. * Action: Check 
fe.audit.log
. * Diagnostic: Look for queries where the 
PlanTime
is unusually high (e.g., > 500ms). If a query has a massive planning time, the FE might be struggling with the MV rewrite search space, which often correlates with high memory usage. 6. Use JVM Tools (Advanced) If the logs aren't conclusive, you can use standard JVM tools: * jmap: Run 
jmap -histo <fe_pid> | head -n 20
to see which objects are taking up the most memory. * Look for 
MaterializedView
, 
MvPlanCache
, or 
ScalarOperator
objects. * jstat: Use 
jstat -gcutil <fe_pid> 1000
to monitor if the FE is constantly performing Full GCs, which is a symptom of high heap occupancy. Summary Checklist Symptom | Likely Cause | Fix/Mitigation High memory during planning | Too many MVs for rewrite | Reduce 
mv_plan_cache_max_size
or disable rewrite for specific MVs. High memory during refresh | Too many concurrent refreshes | Increase refresh intervals or use 
mv_refresh_parallelism
. Permanent high metadata memory | Too many MV partitions/metadata | Drop unused MVs or implement partition TTL (
partition_ttl_number
). References
b
@Rocky what if i don't see any 
MemoryUsageTracker
entries in my logs. logs have been running for hours
r
If you don't see any 
MemoryUsageTracker
entries in your 
fe.log
after several hours, it is likely due to one of the following reasons: 1. Version Requirement The 
MemoryUsageTracker
was introduced in StarRocks v3.3.7. * If you are on a version earlier than v3.3.7, this specific logging mechanism does not exist. * What to do: Check your version using 
SELECT aurora_version();
or looking at the top of your 
fe.log
during startup. 2. Alternative: Check 
fe/log/proc_profile
(v3.3.6+) Even if you don't have the tracker logs, if you are on v3.3.6 or newer, StarRocks automatically generates memory "flame graphs" when it detects memory pressure. * Action: Check the directory 
fe/log/proc_profile/
. * Look for: Files ending in 
.tgz
. Decompress them and open the 
.html
files in a browser. * Diagnosis: If MVs are the culprit, you will see a large horizontal bar for 
MaterializedViewRewriter
or 
MvPlanCache
. ────────── Alternative Ways to Check FE Memory (Any Version) If the automatic logs are missing, you can use these manual methods to see if Materialized Views are bloating your memory: A. The Lightweight "Histogram" Check You can use the standard Java 
jmap
tool to see which objects are occupying the most memory without crashing the FE. 1. Find your FE PID: 
ps aux | grep StarRocksFE
2. Run: 
jmap -histo <pid> | head -n 50
3. Search for these classes: * `org.apache.starrocks.catalog.MaterializedView`: High count means you have many MVs or many partitions/metadata objects per MV. * `org.apache.starrocks.sql.optimizer.MvPlanCache`: This is the memory used for query rewrite. If this is in the top 10, your MVs are consuming significant memory for plan caching. * `org.apache.starrocks.catalog.Partition`: If this number is in the millions, your MVs likely have too many partitions. B. Check the "Plan Cache" Stats If you have many MVs, the FE caches their optimized plans to speed up query rewrites. * Action: Run 
ADMIN SHOW FRONTEND CONFIG LIKE '%mv_plan_cache%';
* If 
mv_plan_cache_max_size
is reached (default 1000), it might not be a "leak," but it indicates that a large chunk of your heap is dedicated to MV metadata. * Test: You can try clearing the cache (or reducing its size) and observing if memory drops:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("mv_plan_cache_max_size" = "100");
C. Monitor "Plan Time" in Audit Logs If the FE memory is high specifically during query peaks, it’s often the Query Rewrite process. * Action: Check 
fe.audit.log
for queries with high 
PlanTime
(e.g., > 1000ms). * Diagnosis: If queries that should be simple have massive plan times, the FE is likely doing "exhaustive searching" through your MVs to find a match, which creates thousands of short-lived objects in the JVM heap, causing "GC pressure." D. Manual Profile (For versions < v3.3.6) If you don't have the automatic profiles, you can manually trigger a 30-second memory allocation profile:
Copy code
bash
# From the fe/ directory
./bin/profiler.sh -e alloc -d 30 -f memory_profile.html <fe_pid>
Open the resulting 
memory_profile.html
to see exactly which MV-related functions are allocating the most memory. Summary Checklist 1. Check version: Is it 
< 3.3.7
? (Explains missing logs). 2. Check 
proc_profile
folder: Any 
.tgz
files there? 3. Run `jmap -histo`: Are 
MaterializedView
or 
MvPlanCache
objects in the top 20? 4. Audit Log: Is 
PlanTime
high for queries? References • administration/management/resource_management/Memory_management.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md
b
@Rocky when I do 
SHOW CREATE TABLE
I always see a replication_num of 1. is that because i'm using compute nodes?
r
Yes, you are exactly right. Seeing a 
replication_num
of 1 is the standard behavior when you are using Compute Nodes (CN) in a shared-data (cloud-native) architecture. Here is the breakdown of why this happens and why it is safe: 1. Shared-Data Architecture (Compute Nodes) In a shared-data cluster, StarRocks separates storage from compute. All data is stored in a single source of truth on remote object storage (like AWS S3, HDFS, or Azure Blob Storage). * Why 1 replica? Because the underlying storage system (e.g., S3) already handles data redundancy and high availability at the hardware level. Storing multiple replicas within StarRocks would be redundant and would triple your storage costs without providing extra benefit. * The Role of CNs: Compute nodes do not "own" the data long-term; they only cache it locally to speed up queries. If a CN fails, no data is lost because the data resides safely in the remote object store. 2. Shared-Nothing Architecture (Classic BE Nodes) In the traditional shared-nothing architecture (using Backend Nodes or BEs), StarRocks stores data on the local disks of the BE nodes. * Why 3 replicas? Because if a physical BE node's disk fails, you would lose data if there weren't other copies. Therefore, the default 
replication_num
is 3 to ensure high availability. Summary Comparison Feature | Shared-Nothing (BE) | Shared-Data (CN) Default Replicas | 3 | 1 Data Location | Local BE Disks | Remote Object Storage (S3/HDFS) Durability | Handled by StarRocks (Multi-replica) | Handled by Cloud Storage (S3/HDFS) Scaling | Requires data rebalancing | Instant (no data movement) Can you change it? In a shared-data cluster, even if you try to set 
replication_num
to 3 in your 
CREATE TABLE
statement, StarRocks will effectively ignore it or treat it as 1 because there is no concept of "multiple storage replicas" managed by the engine in this mode. How to verify your mode: You can confirm your cluster is in shared-data mode by checking the FE configuration:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE 'run_mode';
If it says 
shared_data
, then a replication number of 1 is exactly what you should expect to be expected and is perfectly normal. References • administration/management/resource_management/Replica.md • administration/management/FE_configuration.md • deployment/shared_data/hdfs.md
b
@Rocky what do i need to consider when re-partitioning a table
r
Re-partitioning a table in StarRocks can mean two things: changing the partitioning strategy (e.g., from non-partitioned to partitioned, or changing the partition column) or modifying the existing partition structure (e.g., changing granularity or bucket counts). Here is a breakdown of what you need to consider for each scenario. 1. Changing the Partitioning Strategy (Columns or Type) In StarRocks, you cannot directly change the partition column or convert a non-partitioned table to a partitioned one using an 
ALTER TABLE
statement. * The Approach: You must create a new table with the desired partitioning scheme and migrate the data. * Considerations: * Data Migration: Use 
INSERT INTO new_table SELECT * FROM old_table;
. * Atomicity: To avoid downtime, once the data is migrated, use the 
ALTER TABLE old_table SWAP WITH new_table;
command. This atomically swaps the names of the tables. * Primary Key Tables: If using the Primary Key model, ensure the new partition column is part of the Primary Key. 2. Modifying Existing Partitions (Granularity) Starting from v3.4, StarRocks supports merging finer time-based partitions (e.g., Daily) into coarser ones (e.g., Monthly) for Expression Partitioning. * Command: 
ALTER TABLE <tbl> PARTITION BY date_trunc('month', <col>) BETWEEN '2024-01-01' AND '2024-03-31';
* Considerations: * Concurrency: You cannot run partition merging at the same time as other Schema Change or DML operations. * Pruning vs. Metadata: Merging reduces the number of partitions (lowering FE metadata and planning time) but results in coarser partition pruning for queries. 3. Re-bucketing (Re-distributing Data) Often, when users say "re-partitioning," they actually want to change how data is distributed within a partition (the 
DISTRIBUTED BY
clause). Since v3.2, this can be done via 
ALTER TABLE
. * Command: 
ALTER TABLE <tbl> DISTRIBUTED BY HASH(<col>) BUCKETS <num>;
* Considerations: * Background Task: This triggers a background data reorganization (similar to a schema change). The table remains readable but may have some performance impact during the process. * Bucket Count: Follow the rule of thumb: aim for 100MB to 1GB per tablet (the smallest unit of a bucket) for optimal performance. 4. General "Health" Rules for Partitioning Regardless of how you re-partition, keep these best practices in mind: * Tablet Limits: Avoid having too many small tablets. A common limit is to keep the total number of tablets per BE node under 100k-500k to prevent FE memory issues. * Partition Size: Aim for each partition to be $\le$ 100GB. * Query Patterns: Ensure your most frequent query filters align with your partition column to take advantage of partition pruning. * Dynamic Partitioning: If you are moving to a range-based setup, consider enabling 
dynamic_partition
properties to automate the creation of future partitions and the deletion of old ones (TTL). Summary Checklist Goal | Method | Impact Change partition column | Create new table + 
SWAP
| Full data rewrite required. Change daily to monthly | 
ALTER TABLE ... PARTITION BY
| Metadata cleanup, no data rewrite. Change bucket count/keys | 
ALTER TABLE ... DISTRIBUTED BY
| Background data movement. Add/Drop partitions | 
ALTER TABLE ADD/DROP PARTITION
| Metadata update; data in Trash for 24h. References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • best_practices/partitioning.md References
b
@Rocky if i wanted to change a whole table from daily to monthly partitioning what would be the process
r
Changing a table from daily to monthly partitioning depends on your StarRocks version. There are two main ways to handle this: Option 1: The "Modern" Merge (StarRocks v3.4+) If you are on version 3.4 or higher and using Expression Partitioning (e.g., 
PARTITION BY date_trunc('day', col)
), you can merge existing daily partitions into monthly ones without recreating the table. Process: 1. Run the Merge Command: Use 
ALTER TABLE
to redefine the granularity for a specific time range.
Copy code
sql
    -- Example: Merging all of 2024 from daily to monthly
    ALTER TABLE your_table_name
    PARTITION BY date_trunc('month', your_time_column)
    WHERE your_time_column BETWEEN '2024-01-01' AND '2024-12-31';
2. Verify: Run 
SHOW PARTITIONS FROM your_table_name;
to see the new monthly partitions (e.g., 
p202401
). Considerations: * Required Parameter: The 
WHERE
clause is mandatory; you must specify the range of data you want to transform. * DML Lock: You cannot run this while other Schema Changes or DML operations (like 
INSERT OVERWRITE
) are running on that table. * Mixed Granularity: This creates a "mixed" table where historical data might be monthly and new data might still be daily (if the original table definition was daily). To make future data monthly as well, you should also update the table's default property or use Option 2. ────────── Option 2: The "Swap" Method (All Versions / Recommended for Full Change) If you want to ensure the *entire table*—including all future incoming data—is monthly, the most robust way is to create a new table and swap it. This is the only way for versions below 3.4. Process: 1. Create the New Table: Get the DDL of your current table (
SHOW CREATE TABLE
) and modify the partitioning clause to 
month
.
Copy code
sql
    CREATE TABLE your_table_new (
      ...
    )
    PARTITION BY date_trunc('month', your_time_column) -- Change 'day' to 'month'
    DISTRIBUTED BY HASH(...) BUCKETS ...;
2. Migrate the Data:
Copy code
sql
    INSERT INTO your_table_new SELECT * FROM your_table_old;
Tip: If the table is huge, you can do this partition by partition or in batches. 3. Atomic Swap: This command instantly renames the tables so there is zero downtime for your application.
Copy code
sql
    ALTER TABLE your_table_old SWAP WITH your_table_new;
4. Cleanup: Drop the old table (which is now named 
your_table_new
after the swap).
Copy code
sql
    DROP TABLE your_table_new;
Which one should you choose? * Choose Option 1 (Merge) if you have a massive historical table and you only want to "tidy up" old data to reduce the number of partitions (metadata overhead) while keeping current ingestion as daily. * Choose Option 2 (Swap) if you want to fundamentally change how the table behaves for all data, past and future. This is generally cleaner for long-term maintenance. Key Considerations for Monthly Partitioning * Bucket Count: Monthly partitions will hold 30x more data than daily ones. You may need to increase the bucket count (
BUCKETS
) for the monthly table to keep tablet sizes between 100MB and 1GB. * Query Pruning: If your users frequently query for "the last 24 hours," monthly partitioning will be slightly less efficient because StarRocks will have to scan the entire month's partition instead of just one day. References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • best_practices/partitioning.md References
b
@Rocky what is the size of a varchar?
r
In StarRocks, the "size" of a 
VARCHAR(M)
refers to the maximum number of bytes it can store, not the number of characters. Here is a breakdown of how size works for VARCHAR: 1. The Limit (Max Size) * StarRocks 2.1 and later: The maximum value for 
M
is 1,048,576 bytes (1 MB). * Earlier versions: The limit was 65,533 bytes. * Default: If you don't specify 
M
, the default is 1 byte. 2. Bytes vs. Characters This is the most common point of confusion for users coming from MySQL or PostgreSQL. * StarRocks: 
VARCHAR(10)
means 10 bytes. * MySQL: 
VARCHAR(10)
means 10 characters. * UTF-8 Encoding: Since StarRocks uses UTF-8, a standard English letter is 1 byte, but a Chinese character or a complex emoji can take 3 to 4 bytes. _ _Example:* If you need to store 10 Chinese characters, you should define the column as at least 
VARCHAR(30)
. 3. Storage vs. Memory Size * On Disk (Storage): 
VARCHAR
is truly variable. If you define 
VARCHAR(1000)
but only store the word "Rocky" (5 bytes), it only consumes 5 bytes (plus a small 2-byte length prefix) on your disk. * In Memory (Execution): While StarRocks is highly optimized, certain query operations (like complex joins or group-bys) may pre-allocate memory based on the defined length rather than the actual data. * Best Practice: Do not just set every column to 
VARCHAR(65533)
. Set it to the smallest realistic limit for your data to keep memory usage efficient. 4. Comparison to STRING If you don't want to worry about length at all, you can use the 
STRING
type. * 
STRING
is internally equivalent to 
VARCHAR(65533)
(the old limit). * Restriction: You cannot use 
STRING
(or a very large 
VARCHAR
) as a Key Column in some table models (like Duplicate or Aggregate keys). Keys generally have stricter length limits. Summary Table Feature | VARCHAR(M) Unit | Bytes Max Length | 1,048,576 (v2.1+) Storage | Variable (Actual data + 2-byte prefix) Encoding | UTF-8 only References • sql-reference/data-types/string-type/VARCHAR.md • sql-reference/System_limit.md • faq/table_design_faq.md -faq) References • sql-reference/data-types/string-type/VARCHAR.md • faq/Sql_faq.md • faq/table_design_faq.md • sql-reference/data-types/string-type/STRING.md • knowledge/usage/best-practice.md • sql-reference/System_limit.md • sql-reference/data-types/string-type/CHAR.md
b
@Rocky recommended partition size
r
When designing a table in StarRocks, the "size" you choose for your partitions and tablets (the segments within those partitions) directly impacts query speed, background maintenance (compaction), and memory usage. Here are the recommended guidelines for optimal performance: 1. Partition Size (The Logical Group) A partition is a collection of tablets. You typically partition by time (e.g., Daily or Monthly). * Recommended Maximum: Aim for each partition to be $\le$ 100 GB. * Reasoning: Smaller partitions allow for faster "partition pruning" (skipping data during queries) and make lifecycle management (like dropping old data) much more efficient. 2. Tablet Size (The Physical Unit) A tablet is the smallest unit of data distribution. When you set 
BUCKETS 10
, you are splitting a partition into 10 tablets. * Ideal Range: 100 MB to 1 GB per tablet. * Upper Limit: Try not to exceed 10 GB per tablet. * Reasoning: * Too Small: If tablets are only a few MBs, you end up with "small file syndrome." The FE (Frontend) has to manage too much metadata, and the BE (Backend) spends more time opening/closing files than reading data. * Too Large: If tablets are $> 10$ GB, background compaction (merging data) becomes very heavy, and data recovery (if a node fails) takes much longer. 3. Total Tablet Count (The "Health" Limit) The total number of tablets in your cluster is the most critical metric for system stability. * Per Table: Keep the total number of partitions per table under 100,000. * Per BE Node: Aim to keep the total number of tablets per Backend node under 200,000. * Why it matters: Every tablet requires memory in the Frontend (FE). If you have millions of tiny tablets, the FE may run out of memory or become very slow when planning queries. Summary Strategy Feature | Recommended Partition Size | $\le$ 100 GB Tablet Size | 100 MB – 1 GB (max 10 GB) Total Tablets/BE | $\le$ 200,000 Tablets per Partition | Keep low (e.g., 10–50) unless the partition is very large. Pro Tip: If you aren't sure how many buckets to use, StarRocks can help! * Manual: Estimate your data per partition and divide by 1 GB (e.g., 50 GB partition $\div$ 1 GB = 50 buckets). * Automatic: Since v3.2, you can use Random Bucketing or simply omit the bucket count in many cases, and StarRocks will attempt to set a reasonable default based on your cluster size. References • best_practices/partitioning.md • faq/Deploy_faq.md • best_practices/bucketing.md References • best_practices/partitioning.md • faq/Deploy_faq.md • best_practices/bucketing.md • knowledge/usage/best-practice.md
b
@Rocky how do i change the default parition on a table
r
To change the "default" partitioning behavior of a table, the method depends on which partitioning feature you are using: Dynamic Partitioning (the legacy manual-range way) or Expression Partitioning (the modern automatic way). 1. If you use Dynamic Partitioning If your table was created with properties starting with 
dynamic_partition.
, you can change the default behavior (like switching from daily to monthly for future partitions) by modifying the table properties. To change the granularity to Monthly:
Copy code
sql
ALTER TABLE your_table_name SET (
    "dynamic_partition.time_unit" = "MONTH",
    "dynamic_partition.end" = "3" -- Creates 3 months ahead
);
* What this does: StarRocks will stop creating daily partitions and start creating monthly ones starting from the next scheduled check. Existing daily partitions remain as they are. ────────── 2. If you use Expression Partitioning (Automatic) If your table uses the modern 
PARTITION BY date_trunc('day', col)
syntax, the "default" granularity is part of the table's core definition. You cannot change this expression directly with an 
ALTER TABLE
command. To change the "default" for future data, you have two choices: * Option A: The Swap Method (Cleanest) Create a new table with the desired monthly expression and swap it with the old one. This ensures all data (old and new) follows the same rule.
Copy code
sql
    -- 1. Create new table with 'month'
    CREATE TABLE table_new ... PARTITION BY date_trunc('month', col) ...;
    -- 2. Migrate data
    INSERT INTO table_new SELECT * FROM table_old;
    -- 3. Swap
    ALTER TABLE table_old SWAP WITH table_new;
* Option B: Mixed Granularity (v3.4+) Starting in StarRocks v3.4, you can "merge" existing daily partitions into monthly ones using 
ALTER TABLE ... PARTITION BY
. While this tidies up old data, it doesn't change the underlying table definition for new incoming data if that data still maps to the original expression. You would still need to recreate the table to change the "default" for future ingestion. ────────── 3. Changing Default Partition Properties If by "default" you mean properties like replication factor or storage medium for all future partitions, you can use the 
default.
prefix: * Change Default Replication:
Copy code
sql
    ALTER TABLE your_table_name SET ("default.replication_num" = "3");
* Change Default Storage Medium:
Copy code
sql
    ALTER TABLE your_table_name SET ("default.storage_medium" = "SSD");
Summary * Dynamic Partitioning: Use 
ALTER TABLE ... SET ("dynamic_partition.time_unit" = "MONTH")
. * Expression Partitioning: Use the Swap Method (Create New -> Insert -> Swap). * Replication/Storage: Use 
ALTER TABLE ... SET ("<http://default.xxx|default.xxx>" = "yyy")
. References • table_design/data_distribution/dynamic_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/expression_partitioning.md References • table_design/data_distribution/dynamic_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md
2 Views
Open in Slack
PreviousNext
Powered by