Automatic Materialized View Refresh by Performance Benefit
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face increased complexity and cost due to growing data storage and management demands, with materialized views becoming stale and requiring manual refreshes, which disrupts workload performance.
Innovation Solution
Implementing automatic refresh mechanisms for materialized views based on performance benefits, utilizing ordering techniques, concurrency scaling, and predictive spare capacity management to ensure up-to-date views without disrupting other workloads.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of information
If materialized views are manually refreshed, then data freshness is improved, but workload performance is disrupted
Solution Approach 1:
The system performs refresh operations in advance during periods of low utilization, preparing updated materialized views before they are needed. This preliminary action ensures data freshness is maintained without disrupting peak workload performance, as the heavy lifting is done proactively when resources are available.
Solution Approach 2:
The system dynamically adjusts refresh scheduling based on real-time utilization metrics. When utilization is low, refresh operations are accelerated or initiated; when utilization is high, refresh operations are deferred or throttled. This dynamic adaptation resolves the contradiction by making the system flexible enough to maintain freshness without causing performance disruption.
2Speed
If materialized views are kept fresh through frequent refreshes, then query performance is improved, but computational load increases
Solution Approach 1:
The system implements feedback loops that monitor query performance metrics and utilization levels. Based on this feedback, the system intelligently determines when refresh operations are actually needed versus when they can be deferred. This feedback mechanism ensures query performance is maintained only when necessary, reducing unnecessary computational load while still delivering performance benefits when they matter.
Solution Approach 2:
The system changes operational parameters dynamically - adjusting refresh frequency, batch sizes, and resource allocation based on current system state and performance requirements. This allows the system to optimize the balance between query performance and computational load by adapting parameters to current conditions rather than using fixed refresh schedules.
3Loss of information
If manual refresh operations are performed, then materialized view freshness is maintained, but system complexity increases
Solution Approach 1:
The system implements self-service automation where the database automatically monitors its own state, determines when refresh operations are needed, executes refreshes autonomously, and adjusts based on performance feedback. This eliminates the need for manual intervention and complex external scheduling systems, maintaining freshness while actually reducing overall system complexity by consolidating control within the database itself.
Data Source
AI summary
Materialized views for a database system may be automatically refreshed according to performance benefits. Materialized views may be ordered according to determined performance benefits for the materialized views indicating the performance benefit obtained when a materialized view is used to perform a query at the database system. Materialized views may be selected for refresh operations according to the ordering based on a capacity of the database system to perform refresh operations.