Hybrid Database Table Row Column Store Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional databases are limited to either row-store or column-store formats, which are not adaptable to handle both transactional and analytical queries efficiently, leading to memory-intensive issues and suboptimal performance in mixed query scenarios.
Innovation Solution
A hybrid database table is implemented as both a row and column store, with a data movement manager that automatically moves records from a row partitioned store to a column partitioned store based on access frequency, business logic, or boundary conditions, optimizing access and size of processing-intensive column store data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is stored in row-oriented format, then transactional query performance is improved, but analytical query performance deteriorates and memory consumption increases
Solution Approach 1:
The database table is segmented into two separate storage formats: row-oriented storage for transactional data and column-oriented storage for analytical data. This segmentation allows each storage type to be optimized for its specific use case, with row storage handling OLTP operations and column storage handling OLAP operations, thereby reducing overall memory consumption while maintaining transactional performance.
Solution Approach 2:
The patent introduces a dual-dimensional storage architecture where data exists in both row-oriented and column-oriented formats simultaneously. This dimensional transformation allows the system to access the same data through different organizational structures depending on the query type, enabling efficient analytical processing without sacrificing transactional capabilities.
2Productivity
If data is stored in column-oriented format, then analytical query performance is improved, but transactional query performance deteriorates
Solution Approach 1:
The database table is segmented into two separate storage formats: row-oriented storage for transactional data and column-oriented storage for analytical data. This segmentation allows each storage type to be optimized for its specific use case, with row storage handling OLTP operations and column storage handling OLAP operations.
Solution Approach 2:
The hybrid table structure provides universal access to data through multiple storage orientations. The same logical table can serve both transactional and analytical workloads by maintaining data in both row and column formats, making the system multi-functional and adaptable to different query types without performance degradation.
3Adaptability or versatility
If hybrid table format is used, then query handling flexibility is improved, but system complexity increases
Solution Approach 1:
The patent introduces a hybrid table manager as an intermediary component that automatically manages data distribution between row and column storage formats. This mediator handles the complexity of maintaining dual storage formats, determining which data resides in which format, and coordinating access operations, thereby shielding users from system complexity while providing query handling flexibility.
Solution Approach 2:
The hybrid table structure enables self-service capabilities where the system automatically determines the optimal storage format for different data access patterns. Frequently accessed transactional data is automatically maintained in row format, while analytical data is optimized in column format, reducing the need for manual intervention and simplifying operations despite the underlying system complexity.
Data Source
AI summary
A hybrid database table is stored as both a row and a column store. One or more techniques may be employed alone or in combination to enhance performance of the hybrid table by regulating access to, and/or the size of, the processing-intensive column store data. For example during an insert operation, the column store data may be searched for a uniqueness violation only after certain filtering and/or boundary conditions have been considered. In another technique, a hybrid table manager may control movement of data to the column store based upon considerations such as frequency of access, or underlying business logic. In still another technique, querying of the hybrid table may result in a search of the column store data only after an initial search of row store data fails to return a result.