Multi-Tier Columnar Query Evaluation Across Memory, Flash, and Disk
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies in query processing due to the trade-off between storing data in row-major disk blocks and in-memory columnar formats, leading to underutilization of in-memory column stores during table scans and analytic queries, especially when the dataset does not fit entirely in volatile memory.
Innovation Solution
Implement a multi-tiered columnar storage system that leverages in-memory, flash-based, and disk-based storage tiers, allowing seamless columnar processing across these tiers by initializing multiple columnar storage engines to scan and aggregate results from different storage tiers, optimizing data placement based on access frequency statistics.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If data is stored in row-major disk blocks and cached in buffer cache, then storage capacity is maximized and access from volatile memory is faster than from disk, but query processing performance is suboptimal due to row-major format not being optimal for certain database operations
Solution Approach 1:
The patent segments data storage into multiple storage tiers (volatile memory, flash storage, disk storage) with different data formats optimized for their respective purposes. Each tier stores data in formats appropriate for its access characteristics, allowing the system to leverage the speed of volatile memory while maintaining the efficiency of columnar formats for analytical queries.
Solution Approach 2:
The patent applies different data formats to different storage tiers based on their local characteristics. Volatile memory stores data in row-major format for fast transactional access, while flash and disk storage use columnar formats for efficient analytical queries. This local optimization resolves the contradiction between access speed and query processing efficiency.
2Productivity
If data is stored in in-memory columnar format, then query processing speed is significantly improved for table scans and analytic queries, but storage capacity is reduced and data placement decisions become complex when dataset does not fit entirely in volatile memory
Solution Approach 1:
The patent adds a third dimension to storage capacity by introducing flash storage as an intermediate tier between volatile memory and disk storage. This dimensional expansion allows the system to store more data in columnar format than would fit in volatile memory alone, while still providing fast access for analytical queries, thus resolving the capacity limitation without sacrificing query performance.
Solution Approach 2:
The patent implements dynamic data placement and retrieval strategies that automatically move data between storage tiers based on access patterns and query requirements. This dynamic approach allows the system to optimize for both storage capacity and query performance by keeping frequently accessed analytical data in faster storage tiers while maintaining overall storage efficiency.
3Loss of energy
If only a portion of the table is stored in in-memory column store, then storage costs are reduced, but query performance improvement is minimal because most data must be read from row store
Solution Approach 1:
The patent introduces flash storage as an intermediary tier that bridges the gap between volatile memory and disk storage. This intermediary allows the system to store a larger portion of the dataset in columnar format than would fit in volatile memory alone, thereby achieving significant query performance improvements while maintaining cost efficiency. The flash storage acts as a buffer that enables faster access to analytical data without requiring the entire dataset to reside in expensive volatile memory.
Data Source
AI summary
A multiple-tier operation evaluates a query across storage tiers in columnar format. A database server receives from a client a query for reading values from a set of columns of a database table. The multiple-tier operation comprises accessing a first subset of rows for the set of columns in columnar format in a first tier to generate a first set of results and accessing a second subset of rows for the set of columns in columnar format in a second tier to generate a second subset of results. The multiple-tier operation further comprises aggregating the first set of results and the second set of results to form a query result set and returning the query result set to the client.