Latency-Aware Database Record Purging
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face challenges in ensuring data integrity and completeness during the purging process from online transaction processing (OLTP) databases to storage databases, due to synchronization latency issues, which can result in irrecoverable data loss if not managed properly.
Innovation Solution
A system and method for automated latency-aware purging, where a server computing device monitors synchronization latency between OLTP and storage databases, and only initiates the purging process when the latency is below a predetermined threshold, ensuring data matches between instances before deletion, thereby preventing data loss.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is purged from OLTP database without verifying synchronization completion, then productivity is improved by enabling faster data retention management, but reliability deteriorates due to risk of irrecoverable data loss
Solution Approach 1:
The system performs preliminary verification of synchronization status and data matching between OLTP and storage databases before executing the purging operation. This preliminary action ensures that data is fully replicated to storage before deletion from OLTP, preventing data loss while enabling efficient retention management
Solution Approach 2:
The system continuously monitors synchronization latency and data matching status between databases, using this feedback to dynamically control the purging process. When synchronization latency exceeds thresholds or data mismatch is detected, the system adjusts or pauses purging operations to maintain data integrity
2Reliability
If synchronization latency monitoring is implemented to ensure data integrity, then reliability is improved by preventing data loss, but device complexity increases due to additional monitoring and verification mechanisms
Solution Approach 1:
The system implements self-service mechanisms where the synchronization framework automatically tracks its own latency metrics and the purging system automatically verifies data matching status. This automated self-monitoring reduces the need for external complexity while maintaining high reliability through continuous verification
3Reliability
If data is retained in OLTP database for longer periods to ensure availability, then reliability is improved by maintaining data accessibility, but productivity deteriorates due to slower transaction processing performance
Solution Approach 1:
The system segments data retention across two separate database systems: OLTP database for active transaction processing and storage database for archival retention. This segmentation allows OLTP to maintain high performance by keeping only necessary data while storage retains historical data for long-term availability and compliance requirements
Data Source
AI summary
Methods and apparatuses are described for automated latency-aware purging of database records. A server determines a synchronization latency between a storage database and an online transaction processing (OLTP) database. When the synchronization latency is below a predetermined threshold, the server identifies a first database object in the OLTP database and selects database records to be purged from the first database object based upon a data purge instruction set. The server identifies a second database object in the storage database that corresponds to the first database object and selects database records in the second database object based upon the data purge instruction set. The server purges the database records from the first database object in the OLTP database when a characteristic of the database records to be purged from the first database object matches a characteristic of the database records selected in the second database object.