@Rocky best practices for TTL partition in materialized view

Using Time-To-Live (TTL) for partitioned materialized views (MVs) is a powerful way to manage storage costs while maintaining high performance for recent data. Here are the best practices for implementing TTL in StarRocks materialized views. 1. Choose the Right TTL Property StarRocks provides two main properties to control the materialization scope. You should choose based on your version: *

partition_ttl

(Recommended for v3.1.5+): Specifies a time range (e.g., "7 days"). This is generally more intuitive for business requirements like "keep the last week of data." * `partition_ttl_number`: Specifies the number of most recent partitions to retain. Use this if you are on an older version or prefer keeping a fixed count regardless of the date. 2. Optimize Refresh Behavior To prevent refresh tasks from overwhelming your system resources: * Batching with `partition_refresh_number`: By default, StarRocks may try to refresh many partitions at once. Set this property (e.g.,

"partition_refresh_number" = "1"

) to refresh partitions one by one in a single task, reducing the peak memory and CPU load. * Limit Concurrent Refreshes: Use

auto_refresh_partitions_limit

to cap how many partitions are refreshed in a single automatic trigger. 3. Handle Dimension Table Updates In a typical Star-Schema (Fact + Dimension), a change in a dimension table can trigger a full refresh of the MV, which is expensive. * `excluded_trigger_tables`: Add dimension tables to this list to prevent updates in those tables from triggering an automatic MV refresh. The MV will only refresh when the Fact table changes. * Partial Refresh: If you must refresh when dimensions change, use

partition_ttl

to ensure only the partitions within your retention window are ever updated, avoiding historical data reprocessing. 4. Partition Association * Single Partition Key: A materialized view can only be partitioned by one column from one base table (usually the Fact table). * Date Granularity: Use

date_trunc

to align MV partitions with your query patterns. For example, if you query by day, use

PARTITION BY date_trunc("day", event_time)

. This allows for efficient partition pruning and TTL management. 5. Transparent Query Rewrite One of the best "hidden" features of MV TTL is how it handles queries: * Automatic Routing: If a query's time range falls within the TTL window, StarRocks uses the MV for acceleration. * Fallback to Base Table: If a query requests data older than the TTL, StarRocks automatically routes the query to the base table. This ensures you never get "incomplete" results; you only trade off performance for older data. 6. Syntax Example

sql
CREATE MATERIALIZED VIEW mv_daily_sales
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PARTITION BY sale_date
PROPERTIES (
    "partition_ttl" = "30 days",       -- Keep only last 30 days
    "partition_refresh_number" = "1",  -- Refresh 1 partition at a time
    "excluded_trigger_tables" = "dim_products" -- Don't refresh if product info changes
)
AS 
SELECT 
    sale_date, 
    product_id, 
    sum(amount) as total_revenue
FROM fact_sales
GROUP BY sale_date, product_id;

Key Limitations to Remember * Data Types: Only

DATE

or

DATETIME

columns are supported as partitioning keys.

STRING

is not supported. * Base Table Requirement: You can only build a partitioned MV if the base table it references is also partitioned. * Single Key: Multiple partitioning columns are not currently supported for MVs. References • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md