Scan-Based Merge for Analytical Query Processing in HTAP Systems
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Cloud-based data warehouses and database systems face challenges in efficiently processing large database requests without compromising data accuracy or integrity, particularly when handling concurrent transactions and complex analytical queries that can degrade performance and lead to deadlocks.
Innovation Solution
A hybrid database system that employs a blob manager and blob workers to convert key-value data into blob data, using snapshot and delta files to efficiently handle large analytical-style read requests by splitting data into range granules, replicating them to blob storage, and allowing clients to reconstruct data, thereby offloading processing from the OLTP database.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If transactional processing is added to cloud-based data warehouses to enable custom operations, then system functionality and versatility are improved, but data integrity and security are compromised due to difficulty in secure compaction
Solution Approach 1:
The patent segments data into immutable micro-partitions that are written once and never modified. Instead of traditional ACID transactions that modify data in place, the system divides data into discrete, immutable units that can be safely added without compromising existing data integrity. This segmentation allows transactional operations to proceed without the risk of corrupting existing data.
Solution Approach 2:
The patent performs preliminary validation and formatting of data before it is written to storage. Data is validated, formatted, and assigned unique identifiers before being committed to immutable micro-partitions. This preliminary action ensures that only valid, properly formatted data is stored, eliminating the need for subsequent modifications and preserving data integrity from the outset.
2Adaptability or versatility
If large analytical queries are processed directly on OLTP databases, then query capability is improved, but performance degrades and deadlocks occur due to resource contention
Solution Approach 1:
The patent segments data into immutable micro-partitions that can be independently queried and processed. This segmentation allows analytical queries to be distributed across multiple partitions and processed in parallel without interfering with transactional operations on other partitions, eliminating deadlocks and reducing resource contention.
Solution Approach 2:
The patent extracts data from the OLTP database into a separate query processing layer that handles analytical queries. By taking large-scale analytical processing out of the transactional database environment, the system allows OLTP operations to continue with high performance while analytical queries are processed separately using optimized techniques that do not interfere with transactional workloads.
3Productivity
If data is compacted to maintain efficiency, then storage optimization is improved, but data accuracy and integrity are affected due to security risks in compaction processes
Solution Approach 1:
The patent segments data into immutable micro-partitions that are never modified after writing. Instead of compacting data by merging and rewriting, the system selectively queries only the necessary micro-partitions for each operation. This eliminates the need for compaction entirely, maintaining both storage efficiency and data accuracy without the security risks associated with data rewriting.
Solution Approach 2:
The patent uses disposable, immutable data units that are written once and discarded when no longer needed for querying. Each micro-partition is a self-contained, immutable unit that can be independently managed. When storage space is needed, old micro-partitions are simply removed rather than compacted, maintaining data integrity while optimizing storage efficiency.
Data Source
AI summary
The subject technology receives a query, the query including a query range for processing the query and a set of requested columns. The subject technology based on the query range, determining a set of blob files and a set of delete vectors. The subject technology for each blob file, storing each row, including the set of request columns, into an array of rowsets. The subject technology for each rowset, generating a delete bitset to at least indicate whether each row has been deleted. The subject technology for each delta file, indicate a previous row of a visible row of the delta file as being deleted based on a delete pointer of the visible row. The subject technology providing a set of rowsets, including a corresponding selection column set, as a result of the query.