In-Memory Columnar Database Hot-Cold Data Separation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing systems face challenges in efficiently managing 'hot' and 'cold' data in in-memory databases, particularly when supporting both On-Line Analytical Processing (OLAP) and On-Line Transactional Processing (OLTP) scenarios, as traditional caching and archiving methods fail to balance performance and cost, especially with columnar storage, where all data is loaded regardless of relevance.
Innovation Solution
A system that separates data into 'hot' and 'cold' categories based on usage scenarios, using a central software layer to identify and manage OLTP-hot data subsets, transitioning data from fast to slow storage based on age and size, with automatic query rewrites to ensure only relevant data is loaded, and utilizing a 'temperature' column to control data visibility and storage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If all data is loaded into in-memory columnar storage to support both OLAP and OLTP scenarios, then query performance is improved, but memory usage and system cost increase significantly
Solution Approach 1:
The patent segments data into two distinct categories: OLTP-hot data (frequently accessed transactional data) and OLAP-cold data (less frequently accessed analytical data). This segmentation allows the system to load only OLTP-hot data into expensive in-memory columnar storage, while keeping OLAP-cold data in cheaper disk-based storage, thereby reducing memory usage while maintaining query performance for both workloads
Solution Approach 2:
The patent applies local quality by optimizing storage characteristics for different data types. OLTP-hot data receives high-performance in-memory columnar storage with fast access characteristics, while OLAP-cold data uses cost-effective disk-based storage. This localized optimization ensures each data subset receives appropriate storage quality matching its access patterns and performance requirements
2Quantity of substance
If data is separated into hot and cold categories with different storage media, then system cost is reduced, but query performance may deteriorate due to data access complexity
Solution Approach 1:
The patent implements self-service through automatic query rewriting that transparently handles the complexity of accessing data across multiple storage media. The system automatically rewrites OLAP queries to access only OLAP-cold data from disk storage, while OLTP queries automatically access only OLTP-hot data from memory, eliminating the need for manual data management and reducing access complexity
Solution Approach 2:
The patent introduces an intermediary query rewriting layer that sits between the user and the dual-storage system. This intermediary automatically translates user queries into optimized access patterns, determining which storage medium should be accessed based on the query type (OLAP or OLTP), thereby shielding users from the complexity of the underlying storage architecture
3Ease of manufacture
If traditional caching methods are used to manage hot and cold data, then implementation simplicity is maintained, but performance and cost balance is inadequate for columnar storage scenarios
Solution Approach 1:
The patent fundamentally changes the storage parameter from a single unified in-memory columnar storage to a dual-storage architecture with distinct OLTP-hot and OLAP-cold partitions. This parameter change enables the system to optimize performance and cost by matching storage characteristics to workload requirements, achieving superior performance efficiency compared to traditional single-storage approaches
Data Source
AI summary
According to some embodiments, a database, containing entries, may be stored across first and second storage medium, entries in the second storage medium being default invisible for online transactional processing. A central software layer processor between the database and an application may identify a subset of the entries stored in the first storage medium as being default visible for online transactional processing, wherein entries stored in the first storage medium that are not within the subset are default invisible for online transactional processing. Note that entries in the first storage medium may be accessible by the application more quickly as compared to entries in the second storage medium.