Asynchronous Global Index Maintenance for Database Partitioning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Dropping a partition in a relational database management system can lead to significant downtime and resource bottlenecks due to the time-consuming process of deleting global index entries, especially when dealing with large partitions and concurrent queries.
Innovation Solution
Maintaining a global index asynchronously by identifying and filtering out orphaned entries during query execution and scheduling the deletion of these entries during low-activity periods, allowing for asynchronous index maintenance and coalescing of data blocks to optimize storage and reduce undo/redo records.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If global index entries are deleted synchronously during partition dropping, then the partition can be dropped completely, but database downtime increases and resource availability decreases
Solution Approach 1:
The patent applies preliminary action by marking partition entries as orphaned immediately when a partition is dropped, rather than deleting them right away. The actual deletion is deferred to a later background process, allowing the partition drop operation to complete quickly while maintaining eventual index consistency.
Solution Approach 2:
The patent segments the index maintenance process into two independent phases: (1) immediate partition dropping that marks entries as orphaned, and (2) asynchronous background deletion that removes orphaned entries. This segmentation allows the critical partition drop operation to complete without waiting for the time-consuming deletion process.
2Measurement precision
If global index entries are deleted during partition maintenance, then the index remains accurate, but resource usage increases and performance decreases
Solution Approach 1:
The patent implements periodic action by having a background process periodically scan for and delete orphaned index entries at low-activity periods, rather than performing deletions continuously or synchronously with partition operations. This reduces resource contention while maintaining index accuracy over time.
Solution Approach 2:
The system employs self-service by automatically detecting and cleaning up orphaned index entries through a background process that operates independently of user-initiated partition operations, eliminating the need for manual index maintenance while preserving resource efficiency.
3Stability of the object's composition
If synchronous deletion of global index entries is performed, then index consistency is maintained, but concurrent query performance is blocked
Solution Approach 1:
The patent introduces an intermediary mechanism (orphaned entry markers) that allows the system to maintain index consistency without direct synchronous deletion. The markers serve as intermediaries between the partition drop operation and the eventual deletion, enabling concurrent queries to proceed while consistency is preserved through the marking mechanism.
Solution Approach 2:
The patent applies dynamics by transitioning the index maintenance approach from static synchronous deletion to dynamic asynchronous deletion. The system adapts its behavior by allowing temporary orphaned entries during high-activity periods and performing deletions during low-activity periods, optimizing both consistency and performance based on system state.
Data Source
AI summary
Techniques for maintaining a global index in response to a partition being dropped are provided. In response to an instruction to drop a partition, partition identification data that identifies the partition is stored. Index entries, in the global index, that correspond to the dropped partition become “orphaned” entries. Later, an execution plan for a query is processed, where the execution plan targets a global index. During execution of the execution plan, one or more index entries are accessed. For each accessed index entry, the partition identification data is analyzed to determine if the index entry is an orphaned entry. If so, then the index entry is ignored for purposes of the query. Later, the global index may be updated to delete each orphaned entry. Such deletion may occur much later, such as during a time when the database is not queried or updated frequently.