Hybrid Database Storage with Segment Index for Query Selectivity
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face challenges in efficiently managing both transactional and analytical processing due to the complexity of synchronizing independent rowstore and columnstore systems, leading to performance issues and increased overhead.
Innovation Solution
A method and system that integrates columnstore and rowstore formats within a single segment of persistent storage, using a segment index to maintain a 1:1 mapping between data items, allowing dynamic selection of either format based on query selectivity, thus simplifying query processing and reducing synchronization overhead.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If independent rowstore and columnstore systems are used separately, then each format can be optimized for specific workloads (OLTP for rowstore, OLAP for columnstore), but synchronization complexity and overhead increase significantly
Solution Approach 1:
The patent merges rowstore and columnstore into a single hybrid storage system where both formats coexist within the same database table. Data is stored in both row-based and column-based formats simultaneously, allowing the system to handle both OLTP and OLAP workloads without requiring separate systems. This integration eliminates the synchronization overhead between independent systems while maintaining the performance benefits of both storage formats.
Solution Approach 2:
The hybrid storage system provides multi-functionality by enabling a single database table to serve both transactional processing (OLTP) and analytical processing (OLAP) purposes. The system can dynamically select between rowstore and columnstore formats based on the query type, making the storage system universal enough to handle diverse workload requirements without sacrificing optimization for specific workloads.
2Adaptability or versatility
If data is stored in both rowstore and columnstore formats, then query flexibility and performance are improved, but storage overhead and processing complexity increase
Solution Approach 1:
The system implements dynamic format selection where the choice between rowstore and columnstore is made at query execution time based on the specific query requirements. For OLTP queries requiring row-level operations, the system automatically uses rowstore format. For OLAP queries requiring columnar analysis, it switches to columnstore format. This dynamic approach provides query flexibility while avoiding the permanent storage overhead of maintaining both formats for all data.
Solution Approach 2:
The patent changes the operational parameters of the storage system by allowing the same data to be accessed in different formats depending on the query parameters. The system monitors query characteristics and adjusts the storage format parameter dynamically, transforming the static storage approach into a flexible, parameter-driven system that optimizes for the current workload without permanently duplicating data in both formats.
3Reliability
If separate rowstore and columnstore systems are employed, then each system can be independently optimized, but system simplicity and synchronization overhead are reduced
Solution Approach 1:
The patent combines rowstore and columnstore into a unified hybrid storage architecture where both formats operate within the same database table and share common metadata management. This merging maintains data integrity through centralized control while simplifying the system architecture by eliminating the need for separate, independently managed storage systems. The unified approach reduces synchronization overhead while preserving the reliability benefits of having data in multiple formats.
Data Source
AI summary
Disclosed herein is a computer-implemented method of storing data items arranged in a plurality of columns and a plurality of rows. In some examples the method comprises storing the data items in a segment of persistent storage having a columnstore comprising one or more column files to store the data items column by column, storing the data items in a rowstore in the segment, the rowstore comprising one or more row files to store the data items row by row, and storing a segment index in the segment, the segment index indicating a location in a said column file of the columnstore and a said row file of the rowstore for at least some of the data items.