Column-Row Hybrid Database Storage for Mixed Workloads
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional relational database systems face inefficiencies in handling mixed workloads of Online Transactional Processing (OLTP) and Online Analytical Processing (OLAP queries, with row-oriented systems being inefficient for OLAP and column-oriented systems being inefficient for OLTP due to high I/O costs and metadata management challenges.
Innovation Solution
A column-row hybrid storage format is introduced, where rows are divided into blocks and data is stored by columns within each block, allowing for efficient retrieval of selected columns for OLAP queries and reduced I/O traffic for OLTP queries, while maintaining metadata separation to ensure Atomicity, Consistency, Isolation, Durability (ACID) properties.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If data is stored in row-oriented format, then retrieval of entire rows is efficient, but OLAP query performance deteriorates when only a few columns are needed
Solution Approach 1:
The patent segments the data storage structure by organizing data into columnar blocks within rows. Each block contains data for a specific column, allowing the system to retrieve only the needed column blocks for OLAP queries while maintaining efficient row retrieval capability. This segmentation enables selective access to column blocks based on query requirements.
Solution Approach 2:
The patent applies local quality by making different parts of the row structure serve different functions. Column blocks within rows are organized to optimize for specific access patterns - some blocks are structured for efficient row retrieval while others are optimized for columnar OLAP queries. This local optimization allows the same data structure to serve both OLTP and OLAP workloads effectively.
2Productivity
If data is stored in column-oriented format, then OLAP query performance is improved, but OLTP query I/O costs increase due to accessing multiple column files
Solution Approach 1:
The patent merges columnar and row-oriented storage approaches by integrating column blocks within row structures. This hybrid approach combines the benefits of both formats - columnar organization for OLAP efficiency and row-oriented structure for OLTP performance. The merging eliminates the need to access separate column files for each row, reducing I/O costs for transactional queries.
Solution Approach 2:
The patent creates a universal data structure that serves multiple functions - the same row structure with embedded column blocks can efficiently handle both OLTP transactions and OLAP analytical queries. This multi-functionality eliminates the need for separate storage formats for different query types, reducing system complexity and I/O overhead.
3Productivity
If multiple copies of data in different formats are maintained, then both row-oriented and column-oriented query performance are benefited, but resource consumption increases
Solution Approach 1:
The patent implements a universal hybrid storage structure that performs both row-oriented and columnar operations within a single data format. This eliminates the need for maintaining separate data copies in different formats, reducing storage requirements and computational overhead while still providing optimized performance for both OLTP and OLAP workloads.
Solution Approach 2:
The patent adds a new dimension to traditional storage models by organizing data in a hierarchical structure where column blocks are embedded within rows. This dimensional change allows the system to access data from multiple perspectives (row-wise or column-wise) without requiring separate physical copies, optimizing resource utilization while maintaining query performance.
Data Source
AI summary
Embodiments of the present disclosure provide methods and devices for formatting a database. The method can include clustering, into each block, a set of sequence of elements from each column of a database; generating a file storing one or more blocks having the clustered set of sequences of elements; providing a first table storing file schema information that associates the elements with the file; and providing a second table for storing block schema information that associates the columns with the one or more blocks.