Database Hyper-Column Compaction for Wide Table Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Databases with a large number of columns face increased write operation times and storage limitations, particularly in legacy systems where data migration and query inefficiencies are common, and existing solutions provide little benefit for single-machine and row-based database instances.
Innovation Solution
A system that compacts frequently co-appearing columns into hyper-columns within a database, using a database rebuild module to reorganize the schema and create an optimized database structure, while maintaining compatibility through an interface layer that translates queries and supports compression and indexability.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If multiple tables are joined into a single large table, then data retrieval efficiency is improved, but the number of columns increases causing write operation time to increase
Solution Approach 1:
The patent segments the large wide table into multiple narrow tables, each with a limited number of columns. This segmentation allows write operations to be performed on smaller subsets of columns, reducing write operation time while maintaining the ability to retrieve data efficiently across the segmented structure.
Solution Approach 2:
The patent introduces a new dimension by creating a hierarchical table structure with parent and child tables. Instead of flattening all data into a single wide table, it organizes data in a multi-dimensional hierarchy where parent tables contain summary or frequently accessed columns, and child tables contain additional detailed columns, enabling efficient writes to parent tables and selective joins when needed.
2Quantity of substance
If the number of columns increases, then more information can be stored per row, but storage limits in database systems are exceeded
Solution Approach 1:
The patent divides the storage structure into multiple narrow tables, each with a manageable number of columns that fits within database storage limits. This segmentation allows the system to store large amounts of information by distributing it across multiple tables rather than attempting to store all columns in a single table that would exceed storage limits.
Solution Approach 2:
The patent implements a nested table structure where child tables are logically nested within parent tables through foreign key relationships. This nesting allows the system to store information in a hierarchical manner, with parent tables containing core information and child tables containing additional details, effectively increasing storage capacity while maintaining structured access patterns.
3Productivity
If existing projection techniques are used, then query optimization is achieved, but significant data duplication and migration are required
Solution Approach 1:
The patent segments the data into multiple narrow tables that can be independently managed and queried. This segmentation eliminates the need for data duplication across multiple projections because each narrow table contains a specific subset of columns that can be efficiently queried on its own, reducing the need for data migration and duplication while maintaining query efficiency.
Solution Approach 2:
The patent creates a hierarchical dimension with parent and child tables, allowing queries to be optimized by selecting the appropriate level of the hierarchy. This dimensional approach eliminates the need for flat projections and data duplication because the hierarchical structure naturally supports different query patterns at different levels, reducing the need for data migration while maintaining query efficiency.
Data Source
AI summary
Systems for database optimization include a database rebuild module configured to compact columns in an unoptimized database that co-appear in queries to the unoptimized database into a single hyper-column to form an optimized database. A database interface module is configured to accept queries according to a schema for the unoptimized database and to translate the accepted queries into queries according to a schema for the optimized database.