Immutable Micro-Partition Database Change Tracking
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face challenges in efficiently tracking intermediate changes to database data, requiring significant resources and storage capacity, and are limited by the use of mutable storage devices that struggle with large metadata volumes.
Innovation Solution
The implementation of a system that stores metadata in immutable micro-partitions, allowing for efficient tracking of intermediate changes by generating new micro-partitions for each transaction, associating transaction data with these partitions, and querying joined data to determine comprehensive change tracking summaries between timestamps.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If traditional mutable storage devices are used to track database changes, then change tracking functionality is provided, but storage capacity and computing resources become insufficient for large metadata volumes
Solution Approach 1:
The patent divides the storage system into immutable micro-partitions, each containing specific change tracking metadata. This segmentation allows the system to handle large volumes of metadata by distributing them across multiple small, manageable partitions rather than relying on a single large mutable storage device, thereby resolving the contradiction between storage capacity and reliability.
Solution Approach 2:
The patent creates immutable copies of change tracking metadata in micro-partitions. Instead of relying on a single mutable storage location that can be corrupted or become insufficient, the system maintains multiple immutable copies that preserve change tracking reliability while enabling scalable storage capacity through copy distribution.
2Loss of information
If comprehensive change tracking summaries are generated, then complete change history is obtained, but computing resource requirements increase significantly
Solution Approach 1:
The patent performs preliminary action by pre-computing and storing change tracking metadata in immutable micro-partitions during transaction processing. This preliminary computation and storage of change information eliminates the need for expensive real-time comprehensive comparisons when generating change tracking summaries, thereby reducing computing resource consumption while maintaining information completeness.
Solution Approach 2:
The patent replaces complex mechanical computing operations with more efficient immutable storage mechanisms. By substituting computationally intensive change detection algorithms with pre-computed immutable metadata, the system achieves complete change history with significantly reduced computing resource consumption.
3Adaptability or versatility
If mutable storage devices are used for metadata, then flexibility in data modification is maintained, but performance degrades with large metadata volumes
Solution Approach 1:
The patent changes the fundamental parameter of storage mutability from mutable to immutable for micro-partitions. This parameter change transforms the storage mechanism to provide better performance for large metadata volumes while maintaining adaptability through the ability to create new immutable partitions as needed, rather than modifying existing mutable structures.
Data Source
AI summary
Systems, methods, and devices for tracking a series of changes to database data are disclosed. A method includes executing a transaction to modify data in a micro-partition of a table of a database by generating a new micro-partition that embodies the transaction. The method includes associating transaction data with the new micro-partition, wherein the transaction data comprises a timestamp when the transaction was fully executed, and further includes associating modification data with the new micro-partition that comprises an indication of one or more rows of the table that were modified by the transaction. The method includes joining the transaction data with the modification data to generate joined data and querying the joined data to determine a listing of intermediate modifications made to the table between a first timestamp and a second timestamp.