Database Data Scrubbing via Temporary Table Exchange
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
PostgreSQL databases experience index expansion and junk data accumulation over time, leading to increased disk space usage and system performance degradation, with native VACUUM FULL operations causing significant service interruptions during data scrubbing.
Innovation Solution
A data scrubbing method involving the addition of a share update exclusive lock to prevent DDL operations, creation of a temporary table and index, data copying, and exchange of data and index information, with an access exclusive lock added only during data exchange to minimize service interruptions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If native VACUUM FULL is used for data scrubbing, then disk space can be reclaimed and index expansion removed, but the system experiences significant service interruptions and table data cannot be operated for a long time
Solution Approach 1:
The patent segments the data scrubbing process into multiple phases with different lock types: first applying a share update exclusive lock that allows DML operations, then briefly applying an access exclusive lock only during the critical data exchange phase. This segmentation allows the system to maintain availability during most of the scrubbing process while ensuring data consistency during the critical exchange moment.
Solution Approach 2:
The patent creates a temporary table and copies data to it before performing the actual data exchange. This preliminary action prepares the environment in advance, so that when the access exclusive lock is applied during data exchange, the operation can be completed quickly with minimal service interruption.
2Stability of the object's composition
If an access exclusive lock is applied to the target table during data scrubbing, then data consistency can be ensured, but all DML operations are blocked causing service interruption
Solution Approach 1:
The patent creates a temporary table with the same structure as the target table beforehand, and copies data to it during the share update exclusive lock phase when DML operations are still allowed. This preliminary data preparation ensures that when the access exclusive lock is applied for the actual exchange, the operation is already nearly complete, minimizing service interruption time while maintaining data consistency.
3Loss of substance
If data scrubbing is performed on the target table, then disk space can be reclaimed, but the target table cannot accept any operations during the process
Solution Approach 1:
The patent divides the scrubbing process into two lock phases: a share update exclusive lock phase that allows DML operations (inserts, updates, deletes) and a brief access exclusive lock phase only for data exchange. This segmentation enables the table to remain operational during most of the scrubbing process, significantly improving ease of operation while still achieving complete disk space reclamation.
Solution Approach 2:
The patent introduces a temporary table as an intermediary structure. Data is copied to this temporary table during the share update phase, then exchanged with the target table during the brief access exclusive lock phase. This intermediary approach allows the target table to remain accessible for DML operations while the scrubbing process progresses in the background.
Data Source
AI summary
This application belongs to the field of database application technologies, and discloses a data scrubbing method and apparatus. In the method, a server adds a share update exclusive lock to a target table, to prevent a system from performing a data definition language (DDL) operation on the target table. The server then creates a temporary table and a temporary index, copies data from the target table to the temporary table, and generates index information according to the data in the temporary table. Next, the server adds an access exclusive lock to the target table to prevent the system from performing a data manipulation language (DML) operation on the target table. Then data in the target table and the temporary table as well as corresponding index information is exchanged, thereby completing cleaning and reclaiming of disk space.