Dynamic Range Partitioning for Database Storage Efficiency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies in storage and processing due to unpredictable data record sizes, leading to either unused capacity or errors, and re-partitioning results in high system load and requires exclusive locks.
Innovation Solution
Implementing dynamically range-partitioned tables with a partitioning column and threshold size, allowing for automatic allocation and reallocation of partitions based on data size, eliminating the need for manual re-partitioning and reducing system load.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a fixed partition size is defined prior to use, then storage allocation is simple and fast, but storage efficiency deteriorates due to unused capacity or errors when data size is unknown
Solution Approach 1:
The patent implements dynamic partitioning where partition boundaries are not fixed but adapt based on actual data characteristics. The system automatically adjusts partition sizes and ranges by analyzing data distribution patterns, allowing partitions to grow or shrink dynamically. This resolves the contradiction by making the partitioning structure flexible rather than static, improving storage efficiency without sacrificing allocation speed.
Solution Approach 2:
The system changes partitioning parameters (size, range, boundaries) based on monitored data characteristics. By continuously analyzing data distribution and adjusting partition parameters accordingly, the system optimizes storage efficiency while maintaining fast allocation performance. This parameter adaptation allows the system to respond to unknown data sizes without predefined constraints.
2Loss of energy
If re-partitioning is performed based on actual data records, then storage efficiency is improved, but system load increases and exclusive locks are required
Solution Approach 1:
The patent implements self-service partitioning where the system automatically monitors data characteristics and performs partition adjustments without external intervention. The partitioning mechanism serves itself by detecting when re-partitioning is needed and executing the adjustment autonomously. This eliminates the need for manual re-partitioning operations that would require exclusive locks and high system load, as the system handles partition management independently and continuously.
Solution Approach 2:
The system performs partitioning adjustments continuously in the background based on real-time data analysis, rather than requiring periodic stop-and-repartition operations. This continuous adaptation allows the system to maintain optimal storage efficiency without interrupting normal operations, avoiding the system load spikes and lock requirements associated with traditional re-partitioning approaches.
3Ease of manufacture
If manual partition definition is used, then partition creation is simple, but adaptability deteriorates when data characteristics change
Solution Approach 1:
The system performs preliminary analysis of data characteristics automatically upon data arrival, enabling adaptive partitioning without requiring manual reconfiguration. By proactively analyzing data patterns and pre-adjusting partition structures, the system maintains simplicity in partition creation while gaining adaptability to changing data characteristics. This preliminary automated analysis eliminates the need for manual intervention while preserving ease of operation.
Data Source
AI summary
A system includes generation of a definition of a table including a partitioning column of the table and a threshold size, allocation of a first memory partition for the table, determination that a size of the records of the table in the first memory partition is greater than the threshold size, and, in response to the determination that the size of the records of the table in the first memory partition is greater than the threshold size, determination that a maximum value of the partitioning column in the records of the table in the first memory partition, determination of a minimum value of the partitioning column in the records of the table in the first memory partition, generation of metadata indicating that records of the table in which the value of the partitioning column is in a range between and including the minimum value and the maximum value are stored in the first memory partition, and allocation of a second memory partition for the table.