Best practices for asynchronous materialized views

Use cases

Recommended use cases

• Complex aggregate queries: Queries with multi-table joins, aggregate functions (SUM, AVG, COUNT), or window functions. Materialized views avoid recomputing complex logic on every query.

• Reports: Reports that need a consistent snapshot at a fixed time, such as daily at midnight, so all users see the same data.

• Compute-intensive analytics: Analytical queries with complex calculations or transformations, such as customer lifetime value or predictive modeling. Pre-computing results reduces runtime resource consumption.

• Star or snowflake schemas in a data warehouse: Queries that join a fact table with multiple dimension tables, such as sales joined with product, time, and region dimensions. Pre-materializing join results accelerates analytics.

• Lakehouse acceleration: Data lake queries can be slow due to network latency and object storage throughput limits. Use materialized views to leverage SelectDB's local storage and accelerate lakehouse analytics.

• Data warehouse layering: When base tables hold large volumes of raw data, create multiple layers of materialized views to implement data warehouse layering and simplify complex ETL.

When not to use

• Frequently updated base tables: If source data changes very frequently (e.g., multiple times per minute), materialized views cannot keep up and refresh costs become prohibitive.

• Simple queries: Single-table scans or simple filters. The refresh cost outweighs the performance gain.

• Real-time data requirements (within 1-5 minutes): Materialized views have inherent data latency and cannot guarantee real-time freshness.

• Very small source tables: If base tables contain only a few hundred rows, the performance gain from a materialized view is negligible.

Refresh strategies

Asynchronous materialized views support three refresh strategies. Choose based on your data freshness requirements and available system resources.

Manual refresh

Best for reporting without strict freshness requirements, historical analysis, business-process-synchronized refreshes, and large-scale refreshes that need resource coordination.

Benefits: Full control over refresh timing; schedule around peak hours. Drawbacks: Requires manual scheduling and fault-tolerance management.

Scheduled refresh

Best for periodic metric monitoring, tiered data pipelines, reports with varying freshness requirements, and regularly updated source data.

Benefits: Predictable data latency with scheduled processing. Drawbacks: Freshness depends on the schedule, and you must manually sequence dependent view refreshes.

Trigger-based refresh

Best for upper-level views in multi-layered architectures and base tables that update infrequently.

Benefits: High data freshness with full automation. Drawbacks: Can trigger refresh storms, making system load unpredictable.

Refresh frequency

These are general recommendations. Optimal frequency also depends on system resources, the number of materialized views, and other workload demands.

Operations and maintenance

1. Monitoring: Monitor running materialized views through metrics. Check tasks for job count, execution status, and duration.

2. Planning: Plan the number of materialized views, refresh frequency, and cluster compute capacity. Treat materialized views like ETL pipelines—they require ongoing maintenance, not a "set it and forget it" approach.

3. Resource isolation: Refresh operations are compute-intensive. Control resource usage by specifying the workload_group property.