Hybrid Columnar Compression for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database query performance is hindered by the need to uncompress data before evaluation, which slows down query processing, especially when dealing with compressed data stored in row-major or column-major formats that are inefficient for mixed workloads involving both row retrieval and table scans.
Innovation Solution
The implementation of compression units (CUs) in a hybrid columnar format, where each CU stores a small subset of a database table in column-major format, allowing for efficient retrieval and scanning, with metadata indicating column boundaries and value ranges to minimize decompression and optimize predicate evaluation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If data is stored in compressed format, then storage space is reduced, but query processing speed deteriorates due to decompression requirements
Solution Approach 1:
The patent segments compressed data into columnar blocks that can be independently processed. Each block contains compressed data for a specific column, allowing the system to decompress and process only the relevant blocks needed for a given query, rather than decompressing entire tables or rows. This segmentation enables selective processing that maintains compression benefits while improving query speed.
Solution Approach 2:
The patent implements partial decompression by reading and decompressing only the specific column blocks required for predicate evaluation, leaving other compressed blocks untouched. This partial action approach avoids the excessive decompression of entire datasets, reducing CPU overhead and I/O operations while still enabling efficient query processing on the necessary data portions.
2Speed
If data is stored in row-major format, then row retrieval is efficient, but table scan performance deteriorates
Solution Approach 1:
The patent transitions from traditional row-major storage to columnar storage, fundamentally changing the data organization dimension. In columnar storage, data is organized by columns rather than rows, with each column stored as a separate contiguous block. This dimensional change enables efficient table scans by allowing the system to process entire columns in sequence, while row retrieval is achieved by reconstructing rows from the column blocks as needed.
3Productivity
If data is stored in column-major format, then table scan is efficient, but row retrieval performance deteriorates
Solution Approach 1:
The patent creates a universal columnar storage system that efficiently handles both table scan and row retrieval operations through a unified architecture. The columnar blocks serve multiple functions: they enable efficient sequential scanning for analytics while also supporting row reconstruction for transactional operations. The system provides multi-functionality by allowing the same columnar structure to optimize both access patterns without requiring separate storage formats.
4Reliability
If full decompression is performed before predicate evaluation, then query accuracy is ensured, but processing time increases
Solution Approach 1:
The patent applies partial decompression by reading compressed column blocks from storage, decompressing only the portions needed for predicate evaluation, and leaving the rest in compressed form. This partial action maintains query accuracy by ensuring that the specific data blocks required for evaluation are fully decompressed and accessible, while avoiding the time cost of decompressing unnecessary data blocks.
Solution Approach 2:
The patent performs preliminary decompression of column blocks that are anticipated to be needed for query evaluation, based on query analysis and metadata inspection. By pre-decompressing relevant blocks before actual predicate evaluation begins, the system reduces the critical path processing time while ensuring that accurate, decompressed data is ready when needed for reliable query results.
Data Source
AI summary
A method and apparatus is provided for optimizing queries received by a database system that relies on an intelligent data storage server to manage storage for the database system. Storing compression units in hybrid columnar format, the storage manager evaluates simple predicates and only returns data blocks containing rows that satisfy those predicates. The returned data blocks are not necessarily stored persistently on disk. That is, the storage manager is not limited to returning disc block images. The hybrid columnar format enables optimizations that provide better performance when processing typical database workloads including both fetching rows by identifier and performing table scans.