Parallel Index Updates for Database Bulk Insert Bottlenecks
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database index update methods are inefficient during large-scale data loading, as they perform serial inserts and updates, leading to slow processing due to the need for each row to be handled individually and the sequential updating of indexes.
Innovation Solution
Implementing a BULK insert mode where rows are buffered into data pages and written to disk in parallel, allowing index updates to occur concurrently with data page writing, utilizing multiple threads to update indexes in parallel.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If serial inserts and serial index updates are used, then data integrity is maintained, but processing efficiency deteriorates
Solution Approach 1:
The patent segments the index update process by creating separate worker threads for different index update operations. Each worker thread independently updates a specific index structure (e.g., B-tree, hash index) in parallel, dividing the monolithic serial update into concurrent segmented operations that maintain data integrity while improving throughput
Solution Approach 2:
The patent performs preliminary actions by pre-allocating buffer memory structures and preparing index update templates before the actual data loading begins. This pre-preparation allows the parallel worker threads to immediately begin processing without initialization overhead, maintaining consistency while achieving high-speed parallel updates
2Manufacturing precision
If one row is inserted and updated at a time, then accuracy is ensured, but time consumption increases
Solution Approach 1:
The patent merges multiple individual row insert operations into a single bulk insert operation that processes thousands of rows simultaneously. By combining the insert and index update operations into a coordinated parallel process, the system achieves both high accuracy through validation checks and reduced time consumption through batch processing
Solution Approach 2:
The patent changes the operational parameters from single-row processing to multi-row batch processing by adjusting buffer sizes, thread counts, and batch thresholds. These parameter changes enable the system to process large volumes of data with maintained accuracy through configurable validation rules applied at batch level
3Stability of the object's composition
If indexes are updated sequentially, then consistency is maintained, but processing speed decreases
Solution Approach 1:
The patent introduces dynamic coordination mechanisms where worker threads continuously monitor and communicate their progress. The system dynamically adjusts the execution flow by implementing checkpoint-based synchronization and rollback capabilities, allowing parallel updates to proceed at high speed while maintaining consistency through adaptive coordination rather than rigid sequential control
Data Source
AI summary
A method can include receiving a request to execute a database command identifying a target table; identifying a plurality of rows to insert into the target table based in part on the database command; writing rows, from the plurality of rows, into a data page until the data page is full; determining, by an index thread manager, a number of threads to use for updating indexes defined for the target table; and upon determining the data page is full, updating, in parallel, the indexes defined for the target table using the number of threads.