Database Full Delete via Container Timestamps
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems experience slow performance during full table deletes due to the need to scan every row, including logically deleted records, which can block subsequent write transactions and conflict with analytical tasks.
Innovation Solution
A method where a processor performs a delete operation by associating a time with the data container and record, allowing access based on the chronological order of write and delete operations, enabling quick logical deletion without marking individual records as deleted, and allowing full delete operations to start concurrently with ongoing transactions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the system scans every single row including logically deleted records to perform full delete operations, then transactional consistency is maintained, but performance deteriorates and subsequent write transactions are blocked
Solution Approach 1:
The patent extracts the delete timestamp information from individual row scanning and stores it as a container-level metadata attribute. This allows the system to determine delete status by comparing timestamps rather than scanning each row, thereby maintaining consistency while dramatically improving performance and enabling concurrent operations.
Solution Approach 2:
The patent performs preliminary action by recording the delete timestamp at the container level immediately when a delete operation occurs, rather than waiting to scan and mark individual rows. This preliminary timestamp recording enables subsequent operations to quickly determine delete status without full scanning, resolving the performance contradiction.
2Reliability
If individual row delete operations are performed in MVCC systems, then data integrity is maintained, but performance deteriorates due to comprehensive scanning requirements
Solution Approach 1:
The patent merges individual row delete operations with container-level timestamp recording. By combining these operations, the system maintains data integrity through timestamp-based visibility rules while avoiding the performance penalty of scanning every row, as the container timestamp provides a quick delete status indicator.
3Reliability
If full table delete operations are performed to clear data containers, then data freshness is ensured, but analytical tasks are blocked and performance deteriorates
Solution Approach 1:
The patent extracts the delete operation from a comprehensive full-table scan and represents it as a container-level timestamp. This extraction allows analytical tasks to quickly check the container timestamp to determine if data is fresh, without being blocked by full table scans, thus maintaining data freshness assurance while enabling concurrent analytical processing.
4Ease of operation
If the system marks each row with a delete marker to indicate deletion, then delete status tracking is simplified, but computational overhead increases and performance deteriorates
Solution Approach 1:
The patent merges the delete status tracking function from individual row markers into a container-level timestamp attribute. This merging eliminates the need for per-row delete markers and their associated computational overhead, while maintaining simple delete status tracking through timestamp comparison at the container level.
Data Source
AI summary
In an approach for processing data in a database management system, a processor performs a delete operation on a first data container, wherein the first data container includes one or more data records and wherein the delete operation logically deletes at least one data record of the one or more data records. A processor associates a time of the performed delete operation with the first data container. A processor performs a write operation on a first data record of the first data container. A processor associates a time of the performed write operation with the first data record of the first data container. Responsive to receiving a database query directed at the first data container, a processor grants access to the first data record of the first data container based on, at least, the time of the performed write operation being chronologically after the time of the performed delete operation.