ApsaraDB for SelectDB:Best practices for asynchronous materialized views

Use cases

Recommended use cases

• Complex aggregate queries: For queries that involve multi-table joins, complex aggregate functions (such as SUM, AVG, and COUNT), or window functions. An asynchronous materialized view avoids recomputing complex logic for each query.

• Reports: For generating reports that require a consistent snapshot at a specific point in time, such as daily at midnight. This ensures all users see data from the same point in time.

• Compute-intensive analytics: For analytical queries that involve complex calculations or data transformations, such as customer lifetime value calculation or predictive modeling. Pre-calculating the results reduces resource consumption at runtime.

• Star or snowflake schemas in a data warehouse: For scenarios where a fact table is joined with multiple dimension tables, such as joining a sales fact table with product, time, and region dimension tables. Pre-materializing the join results accelerates analytical queries.

• Lakehouse acceleration: Queries on a data lake can be slow due to network latency and the throughput limitations of object storage. You can use asynchronous materialized views to leverage SelectDB's local storage and accelerate data lake analytics.

• Data warehouse layering: When base tables contain large amounts of raw data and queries require complex ETL operations, you can implement data warehouse layering by creating multiple layers of asynchronous materialized views.

When not to use

• Frequently updated base tables: If the source table data changes very frequently (e.g., multiple times per minute), an asynchronous materialized view struggles to stay in sync, and the refresh cost becomes too high.

• Simple queries: For queries that only involve a single-table scan or simple filtering. The benefits of an asynchronous materialized view do not outweigh the cost of refreshing it.

• Real-time data requirements (within 1-5 minutes): If your business requires the latest version of data, be aware that asynchronous materialized views have inherent data latency.

• Very small source tables: If the base tables contain only a few records (e.g., a few hundred rows), the performance improvement from an asynchronous materialized view is negligible.

Refresh strategies

Asynchronous materialized views offer three main refresh strategies. Choosing the right refresh strategy is crucial for balancing data freshness and system performance.

Manual refresh

Use cases include reporting systems without strict data freshness needs, historical data analysis, scenarios requiring synchronization with business processes, and large-scale data refreshes that need resource coordination.

Benefits: Provides full control over refresh timing, allowing you to avoid peak hours. Drawbacks: Requires additional management for scheduling and fault tolerance.

Scheduled refresh

Use cases: Periodic business metric monitoring, tiered data pipelines, reporting systems with different data freshness requirements, and source data that changes at regular intervals.

Benefits: Allows for scheduled data processing with predictable data latency. Drawbacks: Data freshness is limited by the schedule, and you must manually orchestrate the refresh sequence of dependent views.

Trigger-based refresh

Use cases: Upper-level views in a multi-layered materialized view architecture and scenarios where base tables are updated infrequently.

Benefits: Offers high data freshness and a high degree of automation. Drawbacks: Can cause a refresh storm, making system load unpredictable.

Refresh frequency

The following are general recommendations. The optimal frequency also depends on system resources, the number of materialized views, and the resource consumption of other business workloads.

Operations and maintenance

1. Monitoring: Once a materialized view is running, use metrics to monitor its operational status. You can check the tasks to view information such as the number of jobs, their execution status, and duration.

2. Planning: Plan the number of materialized views, their refresh frequency, and the cluster's maximum computational capacity. Avoid a "set it and forget it" approach. A materialized view is essentially an enhanced ETL process and requires ongoing maintenance, just like a traditional ETL pipeline.

3. Resource isolation: A materialized view's refresh operation is a data computation task, so you should implement resource isolation as needed. You can control the resources used by a materialized view's refresh task by specifying the workload_group property.