Hybrid Row-Column Database Delta Table Ingestion
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Column-oriented databases are inefficient for Online Transactional Processing (OLTP) workloads due to high system costs and difficulty in achieving real-time, high insertion rates and ACID compliance, especially for continuous data ingestion and visibility.
Innovation Solution
Implementing a row-column hybrid database format that supports ACID-compliant data ingestion with delta tables for real-time insert transactions, allowing immediate data visibility and efficient merging of inserted data, while utilizing row-oriented delta tables for buffering and column-oriented storage for blocks to reduce I/O costs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is stored in column-oriented format to efficiently support OLAP queries, then query performance is improved, but I/O costs increase for OLTP transactions such as insert operations
Solution Approach 1:
The patent segments data storage into two distinct formats: column-oriented storage for OLAP query efficiency and row-oriented delta tables for OLTP transaction efficiency. This segmentation allows each storage format to be optimized for its specific workload type, resolving the contradiction between OLAP performance and OLTP I/O costs
Solution Approach 2:
The system dynamically routes incoming data operations based on the type of operation: OLTP transactions are directed to row-oriented delta tables while OLAP queries access column-oriented storage. This dynamic routing ensures optimal performance for each operation type without incurring the penalties of the alternative approach
2Speed
If data is stored in row-oriented format to efficiently retrieve entire rows, then OLTP transaction speed is improved, but OLAP query performance deteriorates when only a few columns are needed
Solution Approach 1:
The patent creates separate storage segments: row-oriented delta tables for OLTP operations and column-oriented storage for OLAP queries. This segmentation allows row-oriented storage to provide fast transaction processing while column-oriented storage handles analytical queries efficiently
Solution Approach 2:
The hybrid architecture provides universal support for both OLTP and OLAP workloads through a unified system that automatically selects the appropriate storage format based on the operation type, making the system multi-functional without requiring separate database systems
3Adaptability or versatility
If a hybrid row-column database format is implemented to support both OLTP and OLAP workloads, then system versatility is improved, but device complexity increases
Solution Approach 1:
The patent introduces a mediation layer that manages the interaction between row-oriented delta tables and column-oriented storage. This intermediary handles data routing, format conversion, and operation dispatch, providing versatility while managing complexity through a unified interface that abstracts the underlying complexity
Data Source
AI summary
Embodiments of the present disclosure provide methods and devices for inserting data in a database. The method can include caching one or more rows of data in a delta table in response to a request to insert the one or more rows in a destination table, wherein the destination table is one of a group of destination tables of a first database, and wherein the delta table contains requested changes of rows of the destination table, the changes being caused by a data operation; determining a priority of the destination table based on runtime information of the destination table relative to the group of destination tables; and scheduling the merging of the inserted rows from the delta table to the destination table based on the determined priority of the destination table. The destination table is in a format of a file storing one or more blocks, each block having a set of sequence of elements clustered from each column of the destination table.