Database Change Tracking with Micro-Partition Delta Metadata
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face challenges in efficiently tracking changes to large datasets due to high resource consumption and storage limitations, particularly with mutable storage devices and services, which struggle with large metadata volumes and slow range scans.
Innovation Solution
Implementing metadata storage and management using immutable storage such as micro-partitions, where changes are recorded in new files rather than overwritten, and utilizing change tracking columns to determine deltas between timestamps without storing intermediate changes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If mutable storage devices are used to track database changes, then change tracking functionality is provided, but storage requirements and query time increase significantly due to large metadata volumes and slow range scans
Solution Approach 1:
The patent segments the database table into multiple micro-partitions, each with its own metadata file. This segmentation allows the system to query only relevant micro-partitions rather than scanning all metadata, dramatically reducing query time while maintaining complete change tracking capability across the entire table.
Solution Approach 2:
The patent introduces a new dimension to metadata storage by creating immutable metadata files for each micro-partition that can be independently queried. This dimensional approach allows efficient range scans on specific time periods and partitions without requiring full-table metadata scans, resolving the time-performance contradiction.
2Loss of information
If all intermediate changes are stored to provide complete change history, then comprehensive audit trail is maintained, but storage requirements increase significantly
Solution Approach 1:
The patent extracts only the essential change information (delta) into immutable metadata files rather than storing complete intermediate change states. Each metadata file captures the net change for its micro-partition, providing complete audit capability while minimizing storage requirements by eliminating redundant intermediate data.
Solution Approach 2:
The system discards intermediate change versions and recovers only the essential delta information needed for audit purposes. By storing immutable metadata that represents the net effect of changes rather than every intermediate state, the system maintains complete change history completeness with minimal storage volume.
3Reliability
If metadata is stored in immutable format with new files for each change, then data integrity and security are improved, but device complexity increases due to multiple metadata files
Solution Approach 1:
The patent segments metadata into immutable files organized by micro-partition and time period. This segmentation structure, while creating multiple files, establishes a regular and manageable pattern that simplifies metadata management compared to maintaining a single large mutable metadata structure. Each file's immutability guarantees data integrity without requiring complex locking or transaction mechanisms.
Data Source
AI summary
A method includes detecting, by at least one hardware processor, a change request for a table of a database, the table comprising a plurality of micro-partitions. A transaction associated with the change request is executed at a first timestamp. The transaction causes replacement of a first micro-partition of the plurality of micro-partitions with a second micro-partition. A change tracking column is generated in the second micro-partition. The change tracking column comprises metadata for the transaction. A delta for the table between the first timestamp and a second timestamp is generated using the metadata in the change tracking column. The delta indicates changes made to one or more rows of the table between the first time stamp and the second timestamp.