Compression-Aware Database Index Design
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database systems face performance issues due to data compression, which reduces storage overhead but increases CPU costs for query and update operations, leading to suboptimal physical database design choices when deciding whether to compress indexes.
Innovation Solution
Incorporating compression into physical database design tools, using a design manager with a request receiving engine, base index engine, and size estimate engine to estimate the size of compressed indexes and select optimal database configurations, considering compression methods like null suppression and dictionary encoding, and employing a directed graph for size estimation and deduction nodes to determine candidate deductions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Volume of stationary object
If data compression is applied to database indexes, then storage space is reduced, but CPU overhead increases
Solution Approach 1:
The patent applies parameter changes by modifying the compression ratio and compression method parameters to optimize the balance between storage reduction and CPU overhead. The system evaluates different compression parameters and selects the optimal configuration based on the specific database workload and storage requirements, thereby resolving the contradiction between reducing storage space and minimizing CPU overhead.
2Loss of time
If compression methods are applied to physical design structures, then I/O overhead is reduced, but system performance may be hampered
Solution Approach 1:
The patent applies local quality by selectively applying compression to specific portions of the database schema rather than uniformly compressing all structures. The system identifies which physical design structures benefit most from compression while maintaining performance-critical areas in an uncompressed or lightly compressed state, thereby reducing I/O overhead without significantly hampering system performance.
3Productivity
If multiple compressed indexes are created, then query performance improves, but storage management complexity increases
Solution Approach 1:
The patent applies feedback by implementing a system that continuously monitors query performance and storage utilization, then automatically adjusts the compression configuration of indexes. The design manager receives feedback on query patterns and performance metrics, and uses this information to dynamically optimize the number and configuration of compressed indexes, thereby improving query performance while managing storage complexity through automated control.
Data Source
AI summary
A plurality of indicators representing a plurality of respective candidate database configurations may be obtained, each of the candidate database configurations including a plurality of database queries and a plurality of candidate database indexes associated with a database table. A portion of the candidate database indexes included in the plurality of database indexes may be selected based on skyline selection. An enumeration of the portion of the plurality of the candidate database indexes may be determined based on a greedy algorithm.