Positional Delta Tree for Column-Store Database Update Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Column-store database systems face performance penalties due to the complexity and overhead of read/update merge operations, particularly with the use of log structured merge trees (LSMTs), which impact read performance and increase disk I/O and CPU overhead.
Innovation Solution
A positional delta tree data structure is implemented to manage updates and read operations, allowing for efficient pre-computation of read data merge locations and minimizing I/O operations, while maintaining read performance and reducing the impact of updates.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If log structured merge trees (LSMTs) are used to manage updates in column-store databases, then update operations can be performed, but read performance deteriorates due to the complexity and overhead of read/update merge operations
Solution Approach 1:
The patent divides the update management structure into separate B-tree indexes organized by column, rather than using a single LSMT structure. Each B-tree index handles updates for specific columns independently, allowing read operations to access data without merging complex update structures, thus maintaining read performance while enabling update operations.
Solution Approach 2:
The patent introduces B-tree indexes as intermediary structures between the underlying data storage and the update operations. These B-tree indexes act as mediators that handle update operations efficiently while allowing read operations to proceed independently without the overhead of merge operations, resolving the conflict between update capability and read performance.
2Reliability
If LSMTs are used for update management, then updates can be tracked, but disk I/O operations increase due to the merge operation overhead
Solution Approach 1:
The patent segments update tracking into column-specific B-tree indexes rather than using a single LSMT structure. This segmentation allows updates to be tracked efficiently for each column independently, reducing the need for extensive merge operations and thereby reducing disk I/O operations while maintaining reliable update tracking.
Solution Approach 2:
The patent changes the structural parameters of the update management system from LSMT to B-tree indexes. This parameter change fundamentally alters how updates are tracked and managed, enabling efficient update tracking with significantly reduced disk I/O requirements by eliminating the need for frequent merge operations.
3Ease of operation
If LSMTs are used to manage updates, then update operations are supported, but CPU overhead increases due to merge operation complexity
Solution Approach 1:
The patent segments the update management functionality into multiple independent B-tree indexes organized by column. This segmentation eliminates the need for complex merge operations that require significant CPU processing, while still providing full update operation support through the coordinated use of these segmented index structures.
Solution Approach 2:
The patent substitutes the mechanical merge operation process inherent in LSMTs with a different mechanism based on B-tree index structures. This substitution replaces the CPU-intensive merge operations with more efficient B-tree based update handling, significantly reducing CPU overhead while maintaining update operation capability.
4Speed
If conventional B-tree storage with tuple position as key is used, then fast lookup by position is achieved, but update operations become complex due to row-id reassignment
Solution Approach 1:
The patent segments the data storage and indexing by organizing B-tree indexes around columns rather than rows. This columnar segmentation allows fast lookup by position to be maintained through the B-tree structure while simplifying update operations, as updates can be applied to specific column segments without requiring complex row-id reassignment across the entire tuple.
Data Source
AI summary
A computer system hosting a column-store database engine is responsive to database requests for the update and retrieval of data from within a stable data table and providing for the storage of database tuples within a column-store organized database structure. A positional delta tree data structure is implemented in the memory space of the database engine and is operatively coupled in an update data transfer path between a database engine interface and the stable data table. The positional delta tree data structure includes a differential data storage layer operative to store differential update data values in positionally defined relative reference to database tuples stored by the stable data table.