Record Block Consolidation for Online Bulk Database Deletion
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for deleting large numbers of records from a database increase the database size and require taking it offline, leading to capacity issues and slow performance.
Innovation Solution
A system and method for bulk data deletion using an in-memory database representation with exclusive locks, record block indexes, and a persistent database representation, allowing parallel processing and transaction logging to maintain database integrity without offline operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If query-based deletion method is used to delete large numbers of records, then deletion can be performed transactionally, but the database size increases proportionally to the number of record versions being deleted, exceeding machine capacity
Solution Approach 1:
The system performs preliminary consolidation of record blocks before deletion operations. By pre-organizing records into consolidated blocks and maintaining an updated record block index, the system eliminates the need to create temporary deletion records, thus preventing database size expansion while preserving transactional deletion capabilities
Solution Approach 2:
The invention extracts the deletion metadata from the main database storage by maintaining a separate record block index that tracks which records have been deleted. This separation allows the actual database records to be consolidated and compacted without retaining deletion markers, thereby reducing database size while maintaining transactional integrity through the index
2Reliability
If query-based deletion method is used, then deletion can be performed transactionally, but the process is slow due to adding more data to the database
Solution Approach 1:
The system performs preliminary consolidation of record blocks and updates the record block index before executing deletions. This pre-processing organizes data into optimal blocks, enabling subsequent deletion operations to proceed by simply updating the index rather than physically processing and storing deletion records, dramatically improving deletion speed while maintaining transactional properties
Solution Approach 2:
The invention replaces the mechanical process of physically adding and managing deletion records in the database with an index-based logical deletion system. By substituting the physical data manipulation mechanism with an index update mechanism, the system achieves both high-speed deletion and transactional integrity without the overhead of data expansion
3Productivity
If alternative bulk deletion methods are used, then deletion speed can be improved through batch processing, but the database must be taken offline
Solution Approach 1:
The system performs preliminary consolidation of record blocks and maintains an up-to-date record block index before bulk deletion operations. This pre-organization enables the deletion process to proceed by updating the index structure rather than physically scanning and processing individual records, allowing the database to remain online and operational during bulk deletion while achieving high deletion speeds
Solution Approach 2:
The record block index serves as an intermediary layer between the user and the actual database records during bulk deletion operations. Users can issue bulk deletion commands while the index handles the coordination of deletions across consolidated record blocks, enabling the database to remain accessible for other operations while efficiently processing bulk deletions in the background
4Productivity
If traditional bulk deletion is performed, then large numbers of records can be deleted quickly, but the database size increases and requires offline operations
Solution Approach 1:
The system performs preliminary consolidation of record blocks and maintains an optimized record block index before bulk deletion. This pre-processing eliminates fragmentation and organizes records into compact blocks, enabling rapid bulk deletion through index updates without creating temporary deletion records that would increase database size, thus achieving both high speed and size efficiency
Solution Approach 2:
The invention merges the deletion operation with the record block consolidation process. By combining these operations, the system deletes records and consolidates remaining records into compact blocks simultaneously, preventing database size increase while maintaining high deletion speed through efficient batch processing of consolidated blocks
Data Source
AI summary
Systems and methods for efficient consolidation of record blocks in a data base. The system comprises: 1) a deletion record set; an in-memory database representation comprising: tables and records; one or more exclusive locks for the records; and a record block index; 2) a persistent database representation comprising: record blocks; and a transaction log. The method comprises: receiving, by a processor, a deletion record set; acquiring, by the processor, an exclusive lock for one or more records in the deletion record set; consolidating, by the processor, one or more record blocks; updating, by the processor, an in-memory record block index; and adding, by the processor, a transaction log entry for the updated record block index update.