Asynchronous Database Index Maintenance via Periodic Updates
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Immediate index maintenance in databases can be inefficient, especially when modifications are frequent but reads are infrequent, leading to unnecessary processing power drain during peak times.
Innovation Solution
Implementing asynchronous index maintenance, where index updates are delayed until specific events such as query execution or low server load, reducing the number of inputs/outputs required for maintenance and optimizing resource utilization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If index maintenance is performed immediately when data is modified, then index accuracy is maintained, but server processing power is drained during peak times
Solution Approach 1:
The patent implements periodic index maintenance by scheduling index updates at intervals or based on triggers rather than continuously updating after every data modification. The index maintenance module monitors data changes and performs index updates periodically or event-driven, reducing immediate processing power consumption while maintaining acceptable index accuracy for query operations.
2Power
If index maintenance is delayed asynchronously, then server resources are conserved, but index data may become outdated
Solution Approach 1:
The patent implements a feedback mechanism where the index maintenance module continuously monitors data modification logs and determines when index updates are necessary. The system tracks changes to base tables and triggers index maintenance based on accumulated changes, query patterns, or thresholds, ensuring index data remains sufficiently fresh while optimizing server resource usage through intelligent update timing.
3Measurement precision
If index updates are performed for every data modification, then query accuracy is ensured, but unnecessary processing occurs when modifications are frequent but reads are infrequent
Solution Approach 1:
The patent applies partial action by performing index maintenance selectively rather than for every data modification. The system evaluates whether index updates are actually needed based on query patterns, data change frequency, and importance of affected columns. When modifications are frequent but reads are infrequent, the system reduces or skips index updates to improve processing efficiency while maintaining sufficient query accuracy for the actual workload.
Data Source
AI summary
This disclosure provides techniques for asynchronously maintaining database indexes or sub-indexes. For example, a database management server may receive a data manipulation statement to modify particular data stored in a database and determine whether an index associated with executing the statement is maintained asynchronously. When the index is maintained asynchronously, maintenance of the index to reflect changes made to the particular data by executing the data manipulation statement may be delayed until an index maintenance event. The index maintenance may be based on an isolation level of a transaction including a query that triggered the index maintenance.