Automatic Vertical Table Decomposition for Database IO Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face challenges in input/output and memory utilization, particularly with column stores experiencing high IO requests, memory pressure, and inefficient disk and buffer space usage due to the limitations of N-ary Storage Model (NSM) and Decomposed Storage Model (DSM), while automatic vertical fragmentation techniques require prior knowledge of queries and are not suitable for schemas with 1000+ columns.
Innovation Solution
The automatic vertical decomposition of tables into PaxGroups, where the method dynamically adjusts the number of columns and groups based on predefined criteria such as maximum allowed columns per group, sum of column width, and number of groups, allowing for flexible storage that balances IO and memory efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of substance
If Decomposed Storage Model (DSM) is used to store data in columns, then data compression potential increases and IO volume reduces, but the number of IO requests increases and memory pressure significantly increases
Solution Approach 1:
The patent segments the table into multiple PaxGroups based on column characteristics (width, nullability, data types). Each PaxGroup contains a subset of columns stored together in a single disk block, reducing the number of separate IO requests needed while maintaining compression benefits for columns with similar characteristics.
Solution Approach 2:
The patent introduces a new dimension of organization by grouping columns into PaxGroups that can be stored together in single disk blocks. This creates a hybrid structure that is neither fully row-based nor fully column-based, allowing multiple columns to be fetched in a single IO operation while maintaining compression.
2Loss of substance
If Decomposed Storage Model (DSM) stores data in columns, then data compression potential increases, but many blocks need to be cached in memory simultaneously significantly increasing pressure on buffer pool
Solution Approach 1:
The patent segments columns into PaxGroups that are stored together in single disk blocks. This reduces the total number of blocks that need to be cached in memory simultaneously, as multiple columns are now accessed through a single block cache entry rather than requiring separate cache entries for each column.
Solution Approach 2:
The patent merges multiple columns into PaxGroups that are stored together in single disk blocks. This combining of columns reduces the number of separate memory blocks needed to cache the data, thereby reducing buffer pool pressure while maintaining compression benefits.
3Ease of operation
If Decomposed Storage Model (DSM) allocates a disk block for each column separately, then columnar storage is achieved, but significant waste of disk space and buffer memory occurs especially when there are relatively few records
Solution Approach 1:
The patent merges multiple columns into PaxGroups that share a single disk block. This is particularly beneficial when there are few records, as the fixed overhead of allocating a separate disk block for each column is eliminated. Multiple columns now share the block space efficiently.
Solution Approach 2:
The patent changes the storage parameter from one block per column to one block per PaxGroup (which contains multiple columns). This parameter change in the storage organization reduces disk space waste by eliminating the fixed overhead associated with allocating separate blocks for each column when record counts are low.
4Ease of manufacture
If N-ary Storage Model (NSM) stores all attributes of a record together, then storage simplicity is maintained, but with very many or very wide columns only a few tuples fit in a single disk block reducing compression opportunities
Solution Approach 1:
The patent segments the attributes into multiple PaxGroups, each containing a subset of columns. This segmentation allows more tuples to fit in each disk block by reducing the width of each group, thereby increasing compression opportunities while maintaining manageable storage organization.
Solution Approach 2:
The patent introduces a hierarchical dimension to storage organization, creating PaxGroups that organize columns into manageable subsets. This allows the system to maintain storage simplicity at the PaxGroup level while enabling better compression at the column level within each group.
5Ease of operation
If N-ary Storage Model (NSM) stores all attributes together, then row-based storage is achieved, but with very many or very wide columns a single tuple may not even fit in a block
Solution Approach 1:
The patent segments wide tables into multiple PaxGroups, each containing a subset of columns. This segmentation ensures that individual tuples within each PaxGroup remain compact and can fit within disk blocks, even when the overall table has very many or very wide columns.
Solution Approach 2:
The patent introduces a dimensional organization where columns are grouped into PaxGroups of manageable width. This creates a hierarchical structure that preserves row-based storage semantics at the logical level while ensuring physical tuples fit within block constraints at the storage level.
Data Source
AI summary
A system and method for improving input/output and memory utilization in a database are described. The system may implement an automatic vertical table decomposition process to achieve the improved input/output, storage and memory utilization in a database.