Database Indexing with Concurrent Table Modifications
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Creating an index for a database table can be time-consuming and result in lengthy downtimes, making large or frequently accessed tables unavailable for extended periods, which is unacceptable in modern commercial applications.
Innovation Solution
A method of indexing a database table that allows for the creation of an index without locking the table for an extended period, by populating the index in a way that handles modifications to the table without requiring the entire table to be locked, and using a data structure that can accommodate each row of the table, enabling faster data retrieval without prolonged unavailability.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If an index is created for a database table to improve query speed, then data retrieval time is reduced, but the table must be locked for extended periods during index creation, causing downtime
Solution Approach 1:
The index creation process is divided into multiple concurrent worker threads, each responsible for populating different portions of the index. This segmentation allows the indexing operation to proceed in parallel without requiring a single long-lived lock on the entire table, thereby reducing the overall lock duration while maintaining indexing effectiveness.
Solution Approach 2:
The system performs preliminary actions by creating a snapshot of the table data before indexing begins, and uses this snapshot to build the index structure. This allows the index to be constructed from a static copy while the original table remains accessible, reducing the need for extended locks on the live table during index creation.
2Reliability
If the entire table is locked during index creation to ensure accuracy, then index integrity is maintained, but client applications cannot access the database
Solution Approach 1:
Multiple worker threads operate concurrently on different segments of the index structure, each with its own local locks and data structures. This segmentation enables index creation to proceed without requiring a global lock on the entire table, maintaining both index accuracy through local consistency checks and database availability for client operations.
Solution Approach 2:
The patent introduces an intermediary mechanism in the form of a snapshot copy of the table data and a coordinated locking protocol that allows worker threads to access and validate data without locking the entire table. This intermediary approach ensures index accuracy while permitting continued database access to applications.
3Speed
If a traditional index structure is used, then query performance improves, but the index cannot accommodate long text values efficiently
Solution Approach 1:
The patent extends the traditional B-tree index structure by adding an additional dimension or layer to handle long text values. Instead of storing complete long text values in the index nodes, the structure uses pointers or offsets to reference the actual data in the table, allowing the index to maintain its speed advantages while accommodating arbitrarily long values without performance degradation.
Solution Approach 2:
The index structure creates a simplified copy or reference representation of long text values rather than storing the complete values in the index tree. This copying approach allows the index to maintain its compact, fast-searchable structure while still providing accurate access to the full long text data when needed, thus supporting both query speed and long value adaptability.
Data Source
AI summary
Disclosed herein are system, method, and computer program product embodiments for constructing an index for a database table. An index that comprises a data structure may be created. The index can then be populated with data from the database table. When a request to modify the database table is received, the method may determine that the request to modify the database table relates to a portion of the database table corresponding to a portion of the index that has yet to be populated. An entry indicating the requested modification can be inserted into the portion of the index that has yet to be populated.