Automated Database Partition Management System
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Managing time-based partitions in cloud-based databases is labor-intensive and constrained by backup size limits, requiring manual intervention and limiting database size due to network throughput and storage limitations.
Innovation Solution
A partition management system that automates the creation, activation, compression, archiving, and purging of partitions based on configuration rules, learning from historical data and customer business requirements, allowing the database to exceed backup size limits without compromising performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If manual partition management is performed in cloud-based databases, then data integrity can be maintained, but labor intensity increases and operational costs rise
Solution Approach 1:
The system implements self-service automation where the partition management system autonomously performs partition creation, activation, compression, archiving, and purging operations based on configured rules and historical learning, eliminating the need for manual database administrator intervention while maintaining data integrity through automated validation mechanisms
2Quantity of substance
If database size is increased to store more historical data, then data retention improves, but backup size limits are exceeded and network throughput constraints are violated
Solution Approach 1:
The system dynamically changes the storage state parameter of partitioned data by compressing inactive partitions and archiving them to cold storage, transforming them from active to inactive state. This allows the database to retain large quantities of historical data while reducing the active data footprint that subject to backup size limits and network throughput constraints
Solution Approach 2:
The system segments the database into active and inactive partitions based on temporal and access pattern criteria. Active partitions remain in primary storage for quick access and are included in backups, while inactive partitions are moved to archive storage, effectively separating the data retention function from the backup size constraint
3Productivity
If partition management operations are performed frequently to maintain performance, then database performance is optimized, but system overhead and operational complexity increase
Solution Approach 1:
The partition management system implements feedback mechanisms by continuously monitoring database performance metrics, access patterns, and partition age. This feedback drives automated decision-making for partition operations, ensuring performance optimization occurs only when and where needed, thereby reducing unnecessary system overhead while maintaining database performance
Solution Approach 2:
The system performs partition management operations periodically based on configured schedules and triggers rather than continuously. Partition compression, archiving, and purging operations are executed at optimal intervals determined by data age and access patterns, reducing system overhead while maintaining performance benefits
Data Source
AI summary
RI/ORASE history and current transaction data are stored in partitioned fact tables and aggregation tables based on business calendar. Most of associated indexes are locally partitioned indexes. In a typical RI customer environment, partitioned transaction data takes 85% or more of overall RI database space. The benefits of partitioning include easy management of data, enhanced performance, and assistance with backup and recovery. A partition management system provides the ability to create initial partitions and manage existing partitions in ways that allow the size of the partitioned database to exceed a backup limit size.