Database Table Re-partitioning Using Transition Partition Specification
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database table re-partitioning operations require exclusive locks, leading to prolonged downtime and high resource consumption, especially for large tables, which is not acceptable during normal database operation and can take up to one hour to finalize, necessitating re-partitioning during downtime.
Innovation Solution
The solution involves initiating the partitioning of source partitions to target partitions using a transition partition specification, allowing data to be moved asynchronously while handling operations concurrently, and updating the transition partition once all data is moved, enabling exclusive locking only for a short duration.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If re-partitioning is performed using traditional methods, then data is correctly redistributed to target partitions, but exclusive locks must be held for prolonged periods (up to one hour or longer) preventing write operations
Solution Approach 1:
The patent creates target partitions and establishes the transition partition specification before actually moving data. This preliminary setup allows the re-partitioning operation to proceed without requiring long-lasting exclusive locks, as the target structure is already in place and operations can be performed concurrently.
Solution Approach 2:
The patent implements a dynamic re-partitioning process where data is moved asynchronously from source to target partitions while the table remains operational. The transition partition specification enables flexible switching between old and new partition schemes, allowing the system to adapt during the re-partitioning process without complete downtime.
2Reliability
If re-partitioning is performed on large tables, then complete data redistribution is achieved, but resource consumption (I/O, main memory, CPU) increases significantly
Solution Approach 1:
The patent segments the re-partitioning process into manageable units by moving data in batches or chunks from source partitions to target partitions. This segmentation allows the operation to proceed incrementally rather than requiring all resources simultaneously, reducing peak resource consumption while still achieving complete data redistribution.
Solution Approach 2:
The patent enables continuous data movement from source to target partitions during the re-partitioning process. By maintaining continuous asynchronous operations rather than stopping and starting, the system achieves complete redistribution while optimizing resource utilization through sustained lower-intensity operations.
3Ease of manufacture
If all data is moved to a common location for re-partitioning, then re-partitioning can be performed, but the operation requires a single server with sufficient resources to hold all data
Solution Approach 1:
The patent segments data movement operations to allow different partitions to be moved to different target servers simultaneously. This eliminates the requirement for a single centralized server to hold all data, distributing the resource requirements across multiple servers and reducing the complexity burden on any single device.
Solution Approach 2:
The patent transitions from a centralized re-partitioning model to a distributed model by enabling parallel data movement across multiple servers. This dimensional shift from single-server to multi-server architecture reduces the resource burden on individual devices while maintaining operational feasibility.
4Productivity
If re-partitioning is performed during normal database operation, then database availability is maintained, but write operations are blocked by exclusive locks
Solution Approach 1:
By pre-creating target partitions and establishing the transition specification before data movement begins, the patent enables the table to remain accessible for write operations during re-partitioning. The preliminary setup eliminates the need for prolonged exclusive locks that would block write operations.
Solution Approach 2:
The transition partition specification acts as an intermediary mechanism that enables concurrent operations. It mediates between the source and target partitions, allowing the system to maintain write operation capability while data is being redistributed, thus preserving database availability during re-partitioning.
Data Source
AI summary
Partitioning of source partitions of a table of a database to target partitions is initiated. Thereafter, a transition partition specification is specified that identifies the source partitions and the target partitions. Data is then moved (e.g., asynchronously moved, etc.) from the source partitions to the target partitions. Concurrently with the moving of the data, operates are handled using the transition partition specification. Subsequently, the source partitions are dropped when all of the data has been moved to the target partitions and there are no open transactions accessing the source partitions. Related apparatus, systems, techniques and articles are also described.