Table Snapshot Archival With Tiered Storage Retention
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database archival techniques result in increased storage costs due to saving full copies of databases for regulatory purposes, especially for large datasets.
Innovation Solution
The method involves generating snapshots of database tables using cloning operations, storing them in first-tier storage, transferring to second-tier storage after a first expiration time, and deleting them after a second expiration time to conserve storage resources.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If full copies of databases are saved for archival purposes, then regulatory compliance is achieved, but storage costs increase significantly
Solution Approach 1:
The database is segmented into multiple tables, and only specific tables that require archival are identified and archived individually. This segmentation allows the system to archive only the necessary portions of the database rather than creating full copies, thereby reducing storage costs while maintaining regulatory compliance.
Solution Approach 2:
Instead of copying entire databases, the system creates snapshots of only the specific tables that need to be archived. This selective copying approach maintains the required archival data for compliance while significantly reducing the storage space and costs associated with archiving complete database copies.
2Speed
If snapshots are stored in first-tier storage, then fast access is available, but storage costs increase
Solution Approach 1:
The system dynamically manages snapshot storage by automatically transitioning snapshots from first-tier storage to second-tier storage based on expiration time. This dynamic approach allows snapshots to be stored in fast, expensive first-tier storage only when needed, and automatically moves them to slower, cheaper second-tier storage after the retention period, thereby optimizing the balance between access speed and storage costs.
Solution Approach 2:
The system implements periodic monitoring and automatic transition of snapshots between storage tiers based on time-based expiration policies. This periodic action ensures that snapshots remain in first-tier storage during their active retention period for fast access, then automatically transitions them to second-tier storage afterward, optimizing storage cost efficiency.
3Quantity of substance
If tiered storage is implemented, then storage costs are reduced, but system complexity increases
Solution Approach 1:
The system implements self-service automation where snapshots automatically transition between storage tiers based on pre-configured expiration time policies without requiring manual intervention. This self-service approach reduces storage costs through tiered storage management while minimizing the complexity burden on users, as the system autonomously handles the storage optimization tasks.
Data Source
AI summary
Cloning operations can be used generate snapshots of tables at specified times. The snapshot objects can be stored in a first-tier storage with the table, where the cloned versions of the tables and the table may share files, such as micro-partition files, to conserve storage resources. After a first expiration time, snapshot objects can be transferred from the first-tier storage to a second-tier storage to further save on storage costs. After a second expiration time (e.g., full retention period), the snapshot objects can be deleted from the second-tier storage as well.