Database Table Re-partitioning via Trigger-Based Replay
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database table re-partitioning operations are costly in terms of I/O, memory, and CPU resources, and require exclusive locks, leading to prolonged downtime and inefficiencies, especially for large tables.
Innovation Solution
The method involves generating a replay table with triggers for database operations, allowing asynchronous data movement from a source table to a target table, and replaying these operations on the target table without exclusive locks, enabling efficient re-partitioning without the need for a single server to hold all data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If traditional re-partitioning operations are performed on large database tables, then data can be moved between partitions, but exclusive locks must be held on the table which prevents write operations and causes prolonged downtime
Solution Approach 1:
The re-partitioning process is segmented into multiple phases: (1) create target table with new partitioning, (2) move data asynchronously in chunks, (3) replay captured operations, (4) switch tables atomically. This segmentation allows the operation to proceed without holding exclusive locks for extended periods, as each phase can be executed with minimal locking requirements.
Solution Approach 2:
The target table is created in advance with the desired partitioning scheme, and triggers are installed on the source table before data movement begins. This preliminary setup allows the actual data migration to proceed without requiring the target table to be modified during the operation, reducing lock requirements.
2Ease of manufacture
If all data is moved to a common location for re-partitioning, then re-partitioning can be performed, but a single server must hold all data which increases resource consumption
Solution Approach 1:
Data movement is segmented into multiple smaller transactions that can be processed asynchronously. Instead of moving all data to a single location simultaneously, the process divides data into manageable chunks that can be transferred and processed in parallel across multiple servers, distributing the resource load.
Solution Approach 2:
The patent introduces a temporal dimension to the re-partitioning process by using asynchronous data movement and operation replay. Data can be moved to distributed locations over time rather than requiring simultaneous consolidation at a single location, allowing re-partitioning to proceed without concentrating all data on one server at once.
3Productivity
If re-partitioning is performed on huge tables during normal operation, then operational continuity is maintained, but the prolonged lock time makes it unacceptable for both OLTP and OLAP processing
Solution Approach 1:
The locking strategy is made dynamic rather than static. Instead of holding exclusive locks for the entire duration of the re-partitioning operation, locks are acquired briefly only when necessary (during trigger installation and final table switch), while the bulk of the data movement and processing occurs with minimal or no locking, allowing normal operations to continue.
Solution Approach 2:
Triggers act as intermediaries between the source table and the re-partitioning process. These triggers capture write operations on the source table and queue them for replay on the target table, allowing the source table to remain accessible for normal operations while the re-partitioning proceeds in the background without requiring prolonged exclusive locks.
4Quantity of substance
If re-partitioning operations are performed, then data is moved between partitions, but the operation is very costly from I/O, main memory and CPU perspective
Solution Approach 1:
The re-partitioning process uses continuous asynchronous data movement rather than batch operations. Data is transferred in a continuous stream with triggers capturing and queving operations as they occur, allowing I/O, memory, and CPU resources to be utilized efficiently and continuously rather than in intensive bursts, reducing peak resource consumption.
Data Source
AI summary
Partitioning of a source table of a database to a target table is initiated. Thereafter, a replay table is generated that is populated with triggers for database operations performed on the source table for subsequent replay for the target partitions. Data is later moved (e.g., asynchronously moved, etc.) from the source table to the target table. The database operations are replayed on the target table T subsequent to the moving of the data using the replay table. In addition, the source table is dropped when all of the data has been moved to the target table and there are no operations requiring replay. Related apparatus, systems, techniques and articles are also described.