Hybrid Columnar Compressed Tables for Database Storage
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for storing and managing tabular data in computers are inefficient, particularly in balancing data compression and query performance, as they often require complex compression techniques and do not allow for flexible storage formats within the same table.
Innovation Solution
The implementation of a hybrid storage schema that allows tabular data to be stored in both row-major and column-major formats within the same table, enabling users to select a balance point for compression and performance, and using compression units that can be analyzed and manipulated efficiently without altering existing data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If data is stored in column-major format with compression, then compression ratio is improved, but query performance deteriorates
Solution Approach 1:
The patent divides the table into multiple segments, where each segment can be stored in a different format (row-major or column-major). This segmentation allows the system to optimize different portions of data independently, achieving high compression ratios for column-major segments while maintaining fast query performance for row-major segments that are frequently accessed.
Solution Approach 2:
Different segments of the same table are assigned different storage qualities based on their access patterns. Frequently accessed segments are stored in row-major format for speed, while less frequently accessed segments are stored in column-major format for compression. This local quality differentiation resolves the contradiction between compression and query performance.
2Speed
If data is stored in row-major format, then query performance is improved, but compression ratio deteriorates
Solution Approach 1:
The table is segmented into multiple parts that can be stored in different formats. Row-major segments provide fast query performance for frequently accessed data, while column-major segments provide high compression for less frequently accessed data. This segmentation allows the system to achieve both high query performance and good compression ratios simultaneously.
3Adaptability or versatility
If hybrid storage schema is implemented, then flexibility is improved, but device complexity increases
Solution Approach 1:
The system automatically determines the optimal storage format for each segment based on access patterns and data characteristics, eliminating the need for manual configuration. This self-service approach provides flexible hybrid storage while keeping the management complexity low, as the system autonomously makes decisions about which segments should be row-major and which should be column-major.
4Quantity of substance
If compression techniques are applied to existing data, then compression ratio is improved, but data manipulation overhead increases
Solution Approach 1:
Data is pre-compressed into column-major format segments during the initial storage phase, before any manipulation operations are performed. This preliminary compression action allows subsequent queries and manipulations to work with the pre-compressed data structure, reducing the overhead of compression during active manipulation while still achieving high compression ratios.
Data Source
AI summary
Techniques for storing and manipulating tabular data are provided. According to one embodiment, a user may control whether tabular data is stored in row-level or column-major format. Furthermore, the user may control the level of data compression to achieve an optimal balance between query performance and compression ratios. Tabular data from within the same table may be stored in both column-major and row-major format and compressed at different levels. In addition, tabular data can migrate between column-major format and row-major format in response to various events. For example, in response to a request to update or lock a row stored in column-major format, the row may be migrated and subsequently stored into row-major format. In one embodiment, table partitions are used to enhance data compression techniques. For example, compression tests are performed on a representative table partition, and a compression map is generated and applied to other table partitions.