Resumable Database Index Build via State Persistence
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database index build operations are prone to failure due to resource-intensive processes and long transaction durations, leading to significant losses when failures occur, and they often require restarting from scratch, which is inefficient and can cause log management issues.
Innovation Solution
Implementing a resumable index build method that allows pausing and resuming index build operations, enabling them to withstand failures with minimal data loss by persisting operation state and using an updated query plan, thereby avoiding the need for complete restarts and reducing log size requirements.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If index build operations are executed in a single transaction, then data consistency is maintained, but the operation duration increases and log size becomes unmanageable
Solution Approach 1:
The patent divides a single large index build transaction into multiple smaller transactions or batches. Each batch processes a portion of the data and can be committed independently, reducing the duration of individual transactions while maintaining overall data consistency through proper transaction boundaries and recovery mechanisms.
Solution Approach 2:
The patent performs preliminary actions by creating the index structure in advance and then populating it incrementally. The index structure is prepared and metadata is set up beforehand, allowing the actual data processing to occur in smaller, manageable batches rather than in a single long-running transaction.
2Reliability
If index build operations are restarted from scratch after failure, then data consistency is ensured, but time loss increases significantly
Solution Approach 1:
The patent performs preliminary actions by establishing the index structure and metadata in advance. When a failure occurs, the preliminary structure remains intact and can be reused, allowing recovery to proceed by continuing from the point of failure rather than restarting the entire operation.
Solution Approach 2:
The system implements self-service recovery mechanisms that automatically detect failures, preserve the current state of index construction, and enable resumption without manual intervention. The index build process monitors its own state and can automatically continue from where it left off, reducing both time loss and operational complexity.
3Reliability
If large indexes are built in a single operation, then completeness is achieved, but resource consumption increases and failure risk increases
Solution Approach 1:
The patent segments the index build operation into multiple smaller batches or chunks. Each batch processes a portion of the data with corresponding resource requirements, distributing the total resource consumption over time and preventing resource exhaustion. This segmentation also reduces the risk associated with any single operation.
Solution Approach 2:
The patent implements partial action by building the index in increments rather than completing the full operation in one step. Each batch completes a portion of the index construction, allowing the system to manage resources more effectively and recover from failures without having committed excessive resources to a single all-or-nothing operation.
4Quantity of substance
If index build operations run for extended periods, then large indexes can be constructed, but the risk of interruption increases
Solution Approach 1:
The patent divides large-scale index construction into multiple smaller, independent batches. Each batch has a shorter duration and lower risk of interruption, yet collectively they build the complete large index. This segmentation maintains operation stability while still achieving the goal of constructing large indexes.
Solution Approach 2:
The system implements self-monitoring and self-recovery capabilities that detect interruptions and automatically resume operations from the last successful checkpoint. This self-service approach maintains reliability during extended operations by continuously monitoring system state and recovering from interruptions without manual intervention.
Data Source
AI summary
A database command is received from a user for modifying an existing data structure or creating a new data structure. The database command is used to construct a query that is provided to a query optimizer component where the query is transformed into a first query execution plan including operations that persist operation state. Some data specified in the query is received, and some of the new data structure is constructed by executing some of the first query plan. While receiving some of data specified in the query and constructing some of the new data structure, operation state of execution is persisted. When an interruption of execution of the first query plan occurs, an updated query plan is generated using persisted operation state. External updates occur, before or after the interruption, but before executing the updated query plan. The external updates are transactionally validated.