Database Latency Reduction via External Indexing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face latency issues due to lock contention when multiple transactions attempt to access the same data simultaneously, particularly during INSERT operations that reference pre-existing rows.
Innovation Solution
The system generates database logging data to detect contention risk conditions by analyzing the frequency of INSERT queries and lock contention. When a contention risk condition is detected, incoming INSERT query statement transaction data is stored in an external indexing structure external to the leaf page, allowing INSERT operations to proceed without triggering contention locks.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If INSERT operations are performed on existing rows in a database leaf page, then data integrity is maintained through locking mechanisms, but latency increases due to lock contention when multiple transactions access the same data simultaneously
Solution Approach 1:
The patent segments the indexing structure by introducing an external indexing structure separate from the traditional B-tree leaf pages. INSERT operations are routed to the external indexing structure when contention is detected, while SELECT operations continue to use the original B-tree structure. This segmentation allows independent optimization of INSERT and SELECT paths, resolving the contradiction between maintaining data integrity through locks and reducing latency by avoiding lock contention.
Solution Approach 2:
The patent introduces an intermediary component - the external indexing structure with its own insert buffer and management logic - that mediates between the traditional B-tree structure and incoming INSERT operations. This intermediary absorbs the lock contention by handling INSERT operations outside the traditional locked leaf pages, while maintaining consistency with the original structure through coordinated updates. The intermediary enables INSERT operations to proceed with reduced latency while preserving data integrity through the mediation of consistency protocols.
2Loss of time
If lock contention is avoided by using external indexing structures, then INSERT operation latency is reduced, but device complexity increases due to multiple indexing structures
Solution Approach 1:
The patent implements dynamic routing logic that determines at runtime whether INSERT operations should use the traditional B-tree leaf pages or the external indexing structure, based on detected contention levels. The system dynamically adjusts its behavior by examining logging data to identify contention risk conditions and switching paths accordingly. This dynamic approach allows the system to optimize for low latency when contention is present while falling back to the simpler traditional structure when contention is absent, thereby managing the trade-off between performance and complexity adaptively.
Solution Approach 2:
The patent applies local quality by implementing contention detection and external indexing specifically for INSERT operations on row intervals where contention risk is detected, rather than globally transforming the entire indexing system. The external indexing structure is selectively activated for specific leaf pages or row intervals exhibiting contention patterns, while other portions of the database continue to use the traditional B-tree structure. This localized application reduces the overall complexity burden while providing performance benefits where needed.
3Device complexity
If traditional B-tree leaf pages are used for INSERT operations, then device complexity is minimized with a single indexing structure, but productivity decreases due to lock contention delays
Solution Approach 1:
The patent performs preliminary action by proactively detecting contention risk conditions through logging data examination before INSERT operations are blocked by lock contention. The system analyzes patterns in the logging data to identify row intervals prone to contention and pre-configures external indexing structures for these intervals. This preliminary detection and preparation enables the system to route INSERT operations to the external structure before contention occurs, maintaining high productivity while avoiding the need for complex global restructuring.
Data Source
AI summary
Methods, computer program products, and systems are presented. The method computer program products, and systems can include, for instance: generating database logging data of a database, wherein the database includes a certain leaf page associated to a certain row interval; examining logging data of the database logging data, and based on the examining the logging data, determining that a contention risk condition is present with respect to the certain row interval; and in response to the determining that the contention risk condition is present with respect to the certain row interval, storing incoming subsequent INSERT query statement transaction data for the certain row interval to an external indexing structure external to the certain leaf page.