Adaptive Hybrid Table Scans for OLTP-OLAP Query Contention
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face challenges in efficiently handling concurrent transactions and large analytical read requests, particularly in hybrid environments where OLTP and OLAP workloads coexist, leading to performance degradation and potential downtime.
Innovation Solution
A network-based database system with a compute service manager and execution platform that employs a two-level transaction hierarchy, concurrency control mechanisms, and adaptive scan operators to manage OLTP and OLAP workloads, ensuring data integrity and performance by utilizing a hybrid database architecture with micro-partitions and expression properties for metadata management.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If a database system supports both OLTP and OLAP workloads in a hybrid environment, then the system provides versatile data processing capabilities, but performance degradation and downtime occur due to resource contention between transactional and analytical operations
Solution Approach 1:
The patent segments the database system into separate OLTP and OLAP processing paths with dedicated resources. The OLTP workload is handled by transaction processing nodes while OLAP workloads are handled by analytical processing nodes, allowing both workloads to run concurrently without resource contention. This segmentation resolves the contradiction by providing workload-specific infrastructure that maintains high performance for both transactional and analytical operations simultaneously.
Solution Approach 2:
The patent introduces data virtualization layers and federated query interfaces as intermediaries that enable unified access to both OLTP and OLAP systems. These intermediary components translate and route queries appropriately to the suitable processing path, providing versatility in workload handling while maintaining performance isolation through intelligent query routing and resource management.
2Quantity of substance
If the database system processes large analytical read requests, then comprehensive data analysis is enabled, but concurrent transaction processing is blocked leading to potential downtime
Solution Approach 1:
The patent implements physical separation of analytical and transactional processing workloads onto different infrastructure components. Large analytical read requests are processed by dedicated OLAP nodes that have separate access paths to the data, while OLTP nodes continue handling concurrent transactions without interference. This segmentation enables the system to process voluminous analytical queries while maintaining transaction availability and preventing downtime.
Solution Approach 2:
The patent adds a temporal dimension to data access by implementing versioned data snapshots and time-travel capabilities. Analytical queries can access historical data versions without blocking current transactions, as each query operates on its own data snapshot. This dimensional approach allows large-scale analytical processing to occur in a separate temporal space, ensuring transaction reliability while enabling comprehensive data analysis.
3Ease of operation
If the database system uses traditional scan operators for hybrid tables, then simple query execution is achieved, but performance degrades when dealing with complex analytical queries on large datasets
Solution Approach 1:
The patent implements adaptive scan operators that dynamically adjust their execution strategy based on query characteristics, data distribution, and system state. The scan operators automatically optimize their behavior by evaluating predicates, selecting appropriate access paths, and adjusting parallelism levels during query execution. This dynamic adaptation maintains operational simplicity for users while achieving high performance for complex analytical queries on large datasets through automated optimization.
Solution Approach 2:
The patent changes the operational parameters of scan operators by introducing multiple access methods (e.g., primary key scans, secondary index scans, broadcast scans, and partition-pruned scans). The system selects and switches between different scan parameter configurations based on the specific query requirements, enabling simple query execution to remain easy while complex analytical queries achieve optimal performance through parameter-optimized execution plans.
Data Source
AI summary
The subject technology receives a query. The subject technology performs a secondary index scan operation of a secondary index based on the query. The subject technology determines that a number of rows returned, from the secondary index scan operation, meets a threshold value. The subject technology sends a signal to a primary index probe operation to initiate a blob storage scan using at least one range scan operation. The subject technology extracts a primary key of each row from the rows returned from the secondary index scan operation. The subject technology identifies a corresponding range of each extracted primary key in the blob storage. The subject technology scans each corresponding range of the blob storage to identify a set of rows that satisfy a set of predicates on an index column. The subject technology provides a set of rows as a result of the query.