@Rocky can I update table partition from month to day ? new partitions loading will go to day and older will be in month only

Yes, you can have a table with mixed partition granularities in StarRocks (e.g., historical data by month and new data by day). However, the method depends on how your table is currently partitioned. 1. If using Dynamic Partitioning If your table uses Dynamic Partitioning (managed via table properties like

dynamic_partition.time_unit

), you can update the granularity for future partitions by altering the table properties:

sql
ALTER TABLE your_table_name SET (
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.prefix" = "p", -- optional: ensure naming consistency
    "dynamic_partition.end" = "3"    -- how many days ahead to create
);

Result: • Existing monthly partitions remain unchanged. • New partitions created automatically by the system will now be daily. ────────── 2. If using Expression Partitioning (

date_trunc

) If you created the table using

PARTITION BY date_trunc('month', col)

, you cannot directly "update" the

date_trunc

expression to

'day'

for future automatic creation via an

ALTER

statement. However, since expression partitioning is treated as Range Partitioning internally, you can manually add daily partitions for the future:

sql
ALTER TABLE your_table_name 
ADD PARTITIONS START ("2024-01-01") END ("2024-02-01") EVERY (INTERVAL 1 DAY);

Warning: If you rely on automatic partition creation (loading data without pre-creating partitions), StarRocks will still try to create monthly partitions for any data that doesn't fit into your manually added daily ranges, because the table's original expression is still monthly. ────────── 3. Recommended Approach (v3.4+) Starting from version 3.4, StarRocks introduced a feature to merge fine-grained partitions into coarser ones. The best practice is now often to: 1. Use daily partitions for all new data. 2. Periodically merge older daily partitions into monthly ones to reduce metadata overhead. Example of merging existing daily partitions into a month:

sql
ALTER TABLE your_table_name 
PARTITION BY date_trunc('month', your_date_column)
WHERE your_date_column BETWEEN '2023-01-01' AND '2023-01-31';

Summary of Requirements * Mixed Granularity Support: Supported by default in Range Partitioning. * Manual Control: You can always use

ALTER TABLE ... ADD PARTITION

to define any range you want (day, month, or year) as long as they do not overlap. * Version Note: If you want to use the

ALTER TABLE ... PARTITION BY ...

(merging) syntax, you must be on StarRocks v3.4 or later. References • best_practices/partitioning.md • sql-reference/sql-functions/date-time-functions/date_trunc.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • table_design/data_distribution/expression_partitioning.md • table_design/data_distribution/Data_distribution.md

@Rocky changing tables partition from month to daycan be directly done instead of merging from days->month. I have table which is monthly partition, data is growing, so I want to have daily partitoin from today, using expression partition, version 4.0.8

Yes, you can transition a table from monthly partitions to daily partitions starting from today. StarRocks supports tables with mixed partition granularities, where historical data remains in monthly partitions while new data is loaded into daily partitions. The method depends on how your table is currently partitioned: 1. If using Dynamic Partitioning If your table uses the

dynamic_partition

properties (where StarRocks manages partitions automatically based on a fixed schedule), you can simply update the table properties:

sql
ALTER TABLE your_table_name SET (
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.end" = "3", -- Create daily partitions 3 days in advance
    "dynamic_partition.prefix" = "p" -- Ensure your prefix matches or is updated
);

* Result: StarRocks will stop creating monthly partitions and start creating daily ones. Existing monthly partitions will remain untouched. 2. If using Automatic (Expression) Partitioning If you created the table using

PARTITION BY date_trunc('month', col)

, you cannot currently "re-define" that global expression to

'day'

via an

ALTER

statement for future automatic creation. However, since expression partitioning is internally treated as Range Partitioning, you can manually define daily ranges for the future:

sql
ALTER TABLE your_table_name 
ADD PARTITIONS START ("2024-08-11") END ("2024-09-01") EVERY (INTERVAL 1 DAY);

* Result: Any data falling into these manually created daily ranges will go there. * Caution: If you rely on automatic creation for future months (e.g., October), StarRocks will still default to the table's original monthly expression if a specific daily partition doesn't exist. To avoid this, you should continue to manually add daily partitions or switch to the Dynamic Partitioning method described above. 3. Best Practice: "Daily First, Merge Later" (v3.4+) The recommended strategy in newer StarRocks versions is actually the opposite: use daily partitions for everything to allow for precise pruning of "hot" data, and then merge them into monthly partitions once they become "cold" to save metadata. If you are on StarRocks v3.4 or later, you can manage this mixed granularity easily. While you can keep your old monthly data as-is, the system is optimized for merging daily into monthly:

sql
-- Example: Merging old daily partitions into one month
ALTER TABLE your_table_name 
PARTITION BY date_trunc('month', your_date_column)
WHERE your_date_column BETWEEN '2024-07-01' AND '2024-07-31';

Summary of your scenario * Existing monthly data: Stays in the monthly partitions. * New data: Will go into daily partitions as long as you have created them (manually or via dynamic partitioning properties). * Queries: The StarRocks optimizer will automatically prune across both monthly and daily partitions based on your query predicates (e.g.,

WHERE date >= '2024-01-01'

). Note: You cannot "split" an existing monthly partition into daily ones without dropping the partition and reloading the data. But for your goal—keeping old data monthly and new data daily—this is not required. References • best_practices/partitioning.md • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md