Record Block Consolidation for Online Bulk Database Deletion

Resolve Bottlenecks,
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, exceeding machine capacity and being slow due to adding data.

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

VSEngineering 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

Engineering Contradiction:

Improvetransactional integrityVSAvoiddatabase size

Core Design Contradiction:

ReliabilityVSQuantity of substance

Solution Approach 1:

The system performs preliminary consolidation of record blocks before deletion operations. By pre-organizing records into consolidated blocks and identifying deletion candidates in advance, the system avoids the need to add deletion marker records during the actual deletion process, thus preventing database size expansion while maintaining transactional integrity

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The invention extracts and separates the deletion logic from the traditional query-based approach. Instead of adding deletion records to the database, the system extracts the actual record data and consolidates it into new record blocks, leaving the database structure unchanged and avoiding size increase

Inventive Principle:

Principle #2Taking out (Extraction)

2Reliability

If query-based deletion method is used, then deletion can be performed with data integrity, but the process is slow because it adds more data to the database

Engineering Contradiction:

Improvedata integrityVSAvoiddeletion speed

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The system performs preliminary consolidation of record blocks and identification of deletion candidates before the actual deletion operation. This pre-processing step allows the actual deletion to proceed by simply replacing consolidated blocks, rather than adding and processing deletion records, thereby significantly improving deletion speed while maintaining data integrity through the transaction log

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The invention skips the traditional multi-step query-based deletion process that adds and processes deletion records. By using pre-consolidated record blocks and direct block replacement, the system rushes through the deletion operation in a single efficient step, improving productivity without sacrificing data integrity

Inventive Principle:

Principle #21Skipping (Rushing through)

3Productivity

If alternative bulk deletion methods are used to improve speed, then deletion can be performed faster, but the database must be taken offline

Engineering Contradiction:

Improvedeletion speedVSAvoiddatabase availability

Core Design Contradiction:

ProductivityVSEase of operation

Solution Approach 1:

The system performs preliminary consolidation of record blocks and prepares deletion candidates while the database remains online and operational. This allows the bulk deletion to be executed as a simple block replacement operation that does not require taking the database offline, thus maintaining both high speed and continuous availability

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The invention makes the deletion process dynamic by allowing it to occur in two distinct phases: a preliminary consolidation phase that can occur while the database is online, and a final replacement phase that is transactional and brief. This dynamic approach eliminates the need for prolonged offline periods while maintaining deletion speed

Inventive Principle:

Principle #15Dynamics

4Productivity

If traditional bulk deletion is used, then deletion can be performed efficiently, but it requires taking the database offline which reduces availability

Engineering Contradiction:

Improvedeletion efficiencyVSAvoiddatabase downtime

Core Design Contradiction:

ProductivityVSDuration of action of stationary object

Solution Approach 1:

The system performs all heavy lifting of consolidation and deletion candidate identification in advance while the database remains operational. This preliminary action reduces the actual deletion operation to a simple block replacement that completes quickly, thereby maintaining high deletion efficiency while minimizing database downtime to nearly zero

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS12474849B2Systems and methods for efficient consolidation of record blocks

Publication Date: 2025.11.18 KINAXIS INC

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.