Database Metadata Storage Using Immutable Micro-Partitions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face limitations in handling large amounts of metadata due to storage capacity constraints in mutable storage devices and poor performance in reading large volumes of metadata, making it difficult to store and manage metadata efficiently.
Innovation Solution
The solution involves storing metadata in immutable storage services like Amazon S3, Microsoft Azure Blob Storage, and Google Cloud Storage, where data is stored in micro-partitions, allowing for efficient tracking of changes and execution of tasks based on a change tracking stream, which advances only after a transaction is fully executed, ensuring tasks are executed once and on all newly ingested data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If metadata is stored in mutable storage devices, then data can be updated in place, but storage capacity is exceeded and performance deteriorates when handling large amounts of metadata
Solution Approach 1:
The patent segments metadata storage into immutable micro-partitions, where each micro-partition contains a portion of the metadata. This segmentation allows the system to handle large volumes of metadata by dividing it into manageable, immutable units that can be efficiently stored and retrieved without overwhelming the storage system's capacity or performance.
Solution Approach 2:
The patent implements preliminary action by creating immutable micro-partitions in advance before metadata needs to be read. Metadata is pre-organized into immutable storage units with associated micro-partition information stored in a cache, so that when queries need to access metadata, the system can quickly retrieve pre-prepared micro-partitions without performing complex updates or reorganizations at query time.
2Productivity
If change tracking stream advances after each transaction, then tasks can be triggered promptly, but tasks may be executed multiple times on the same data
Solution Approach 1:
The patent implements a feedback mechanism where the change tracking stream monitors transaction completion status and provides feedback to the task execution system. When a transaction is fully executed and committed, the change tracking stream advances and triggers task execution. The system uses this feedback loop to ensure tasks are triggered at the appropriate moment while maintaining accuracy through transactional consistency checks.
Solution Approach 2:
The patent introduces a change tracking stream as an intermediary between transactions and task execution. This intermediary component tracks the state of transactions and coordinates task triggering, ensuring that tasks are executed exactly once when transactions are fully committed. The change tracking stream acts as a mediator that prevents both premature execution and duplicate execution of tasks.
Data Source
AI summary
A method includes generating a task using a plurality of logical statements embedded in a database, the plurality of logical statements corresponding to a data modification. Database data is ingested into a staging table that is configured within the database. The task is executed based on applying the data modification to a first set of partitions storing the database data and generating a second set of partitions. The second set of partitions store modified data corresponding to the database data. A stream of committed transactions is advanced at least in part by adding an entry into the stream. The entry corresponds to committed transactions performed on the first set of partitions during the data modification. A data processing task is scheduled for execution on the modified data based on the advancing of the stream offset.