Columnar Database Delta Structure Merge Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional columnar database systems face performance issues due to the need for numerous I/O operations during data merges, which negatively impact performance and result in large, impractical backup logs for ensuring recoverability.
Innovation Solution
Implement a database system that asynchronously manages delta structures in both volatile and persisted memory, allowing for efficient merging and recovery processes by copying and truncating delta structures, and utilizing savepoints to maintain transactional consistency without frequent merging of persisted data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If frequent merging of delta structures with columnar data is performed to maintain data consistency, then data recoverability is improved, but I/O operations increase and performance deteriorates
Solution Approach 1:
The patent segments the database storage into multiple columnar tables, where each table maintains its own delta structure independently. This segmentation allows parallel processing of merge operations across multiple tables, reducing the overall I/O burden and improving merge performance while maintaining data consistency through independent delta management for each segment.
Solution Approach 2:
The system performs preliminary actions by maintaining delta structures in memory before committing changes to persisted storage. The delta structures accumulate changes asynchronously, and only when certain conditions are met (such as memory thresholds or transaction commits) are the merges performed, reducing the frequency of I/O operations while ensuring data consistency.
2Reliability
If entire columnar tables are written to persisted database log for crash recovery, then recoverability is improved, but log size increases and backup becomes impractical
Solution Approach 1:
The patent extracts only the essential recovery information (delta structure metadata and change summaries) from the full columnar table data and stores it in the persisted database log. This extraction approach maintains crash recoverability by preserving the necessary information to replay changes, while dramatically reducing log size to make backups practical.
Solution Approach 2:
Instead of writing entire columnar tables to the log, the system creates simplified copies containing only the essential recovery information needed for crash recovery. These compact log entries can be efficiently backed up while still enabling full system recovery through replay of the captured changes.
3Stability of the object's composition
If delta structures are merged frequently to maintain data consistency between volatile and persisted memory, then data consistency is improved, but I/O operations increase and performance deteriorates
Solution Approach 1:
The system implements periodic action by merging delta structures with columnar data at scheduled intervals or when specific thresholds are reached, rather than after every transaction. This periodic merging maintains data consistency over time while significantly reducing the frequency of I/O operations, thereby improving overall merge performance.
Solution Approach 2:
The patent introduces dynamics by making the merge frequency and timing adaptive based on system conditions, such as memory usage thresholds, transaction rates, and I/O workload. This dynamic approach optimizes the balance between maintaining data consistency and minimizing I/O operations, improving merge performance while preserving data integrity.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A system includes reception of a transaction to change a columnar table, recordation of the transaction within a delta structure in a volatile memory, recordation of the transaction within a log of a persisted memory, reception of an instruction to merge the delta structure in the volatile memory with a first instantiation of the columnar table in the volatile memory, and in response to the instruction, merging of the delta structure in the volatile memory with the first instantiation of the columnar table in the volatile memory to create a merged columnar table in the volatile memory, and not storing the merged columnar table in the persisted memory