Database Partition Purging for Storage Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Columnar databases face inefficiencies in high-frequency updates, leading to increased data versioning, larger database tables, and impaired query performance, which raises the total cost of ownership and reduces concurrency.
Innovation Solution
Implement a method to identify and purge outdated data by copying the latest version of data entries from an oldest storage partition to a future partition, creating a dummy entry in another partition, and updating the cache to maintain the latest versions, thereby reducing storage size and maintaining data integrity.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If data versioning is implemented to facilitate high-frequency updates in columnar databases, then update capability is improved, but database table size increases and query performance deteriorates
Solution Approach 1:
The database table is segmented into multiple partitions based on time ranges. Each partition stores data for a specific time period, allowing the system to manage versioned data in distributed units. This segmentation enables selective purging of old partitions while preserving recent data, thus controlling overall table size while maintaining update capability.
Solution Approach 2:
The system implements periodic purging of outdated partitions based on time thresholds. By systematically removing partitions older than a predefined threshold (e.g., weekly purging of partitions older than one week), the database maintains a manageable size while preserving recent versioned data needed for high-frequency updates.
2Adaptability or versatility
If data versioning is implemented to facilitate high-frequency updates, then update capability is improved, but query performance deteriorates
Solution Approach 1:
Querying is optimized by segmenting the search space into time-based partitions. When querying for latest versions, the system can efficiently navigate partition metadata to identify relevant partitions, reducing the scan scope from the entire database to specific time-range partitions, thus improving query performance despite versioning overhead.
Solution Approach 2:
The system extracts and maintains metadata about data locations and versions separately from the actual data partitions. This metadata layer enables efficient query routing to the correct partitions without scanning entire tables, isolating the query optimization function from the versioned data storage.
3Quantity of substance
If outdated data is purged from storage partitions, then storage size is reduced, but data integrity may be compromised if latest versions are lost
Solution Approach 1:
Before purging a partition, the system performs preliminary checks to verify that no later versions of the data exist in newer partitions. This preliminary validation ensures that only partitions containing obsolete data are removed, maintaining data integrity while enabling storage reduction.
Solution Approach 2:
The system implements feedback mechanisms through metadata tracking and version verification. Before purging, it queries the metadata to confirm that all references to data in the target partition have been superseded by later versions. This feedback loop prevents accidental deletion of currently needed data, ensuring integrity before storage reduction.
4Quantity of substance
If frequent purging is performed to reduce storage size, then storage efficiency is improved, but database concurrency is reduced
Solution Approach 1:
Purging is performed periodically at scheduled intervals rather than continuously or on every update. This periodic approach allows concurrent read operations to complete during purging intervals without interference, reducing the impact on database concurrency while still achieving storage efficiency goals over time.
Solution Approach 2:
The system uses metadata and partition management structures as intermediaries to coordinate purging operations with active transactions. The metadata layer acts as a mediator that tracks data locations and coordinates purge timing, allowing the system to manage storage efficiently while maintaining concurrency through proper transaction isolation and coordination.
Data Source
AI summary
Examples relate to purging storage partitions of a database. The examples disclosed herein identify a first partition of a database to be purged and identify a data entry in the first storage partition, where the data entry is to be copied. Examples herein copy an updated version of the data entry to a future storage partition of the database and purge the first storage partition. A dummy data entry is created in a second storage partition of the database, where the dummy data entry identifies the future storage partition.