In-Memory Columnar Format for OLTP Query Speed
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies when trying to migrate from row-major disk blocks to column-major formats for improved query processing, as this migration is complex and can lead to reduced performance for certain queries, and replication techniques suffer from lag and unpredictable artifacts.
Innovation Solution
The system maintains data in both row-major and column-major formats simultaneously, with the column-major format being used in memory for improved query performance without requiring database migration, and ensures transactional consistency between the two formats.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If data is stored in row-major disk blocks and cached in buffer cache, then data persistence and traditional query support are maintained, but query processing speed is insufficient due to non-optimal memory format
Solution Approach 1:
The patent segments data storage into two distinct formats: row-major format for persistent storage on disk and column-major format for in-memory caching. This segmentation allows each format to serve its optimal purpose - row-major for write efficiency and persistence, column-major for query performance - without requiring a complete system migration to one format or the other.
Solution Approach 2:
The patent applies local quality by using different data formats in different locations within the system. Column-major format is used specifically in the in-memory cache where query processing occurs, while row-major format remains on disk for persistent storage. This localized optimization improves query speed without compromising the overall system architecture.
2Productivity
If database is migrated to column-major table types, then query processing efficiency is improved, but migration complexity increases and performance for certain queries deteriorates
Solution Approach 1:
The patent implements a dynamic data format selection mechanism where the system automatically chooses between row-major and column-major formats based on the query type and data location. This dynamic approach allows the system to leverage column-major efficiency for in-memory queries while maintaining row-major capabilities for disk-based operations, avoiding the need for a static, system-wide migration.
Solution Approach 2:
The patent creates a copy of data in column-major format within the in-memory cache, while the original row-major data remains on disk. This copying approach allows the system to benefit from column-major query processing efficiency without requiring a complete migration of the underlying database structure, thus reducing migration complexity.
3Speed
If column store indexes are added to row-major disk blocks, then query performance is improved, but maintenance overhead increases significantly
Solution Approach 1:
The patent performs the data format conversion to column-major format in advance, during the data loading phase into the in-memory cache, rather than maintaining separate indexes during runtime. This preliminary action eliminates the need for ongoing index maintenance overhead while still achieving the query performance benefits of columnar storage.
4Adaptability or versatility
If database is replicated with different formats, then query flexibility is improved, but replication lag and consistency issues arise
Solution Approach 1:
The patent merges multiple data formats within a single database system rather than using separate replicated databases. By maintaining both row-major and column-major formats in the same system with a unified query optimizer, the patent achieves query flexibility without the replication lag and consistency issues that arise from distributed multi-format systems.
Data Source
AI summary
Techniques are provided for maintaining data persistently in one format, but making that data available to a database server in more than one format. For example, one of the formats in which the data is made available for query processing is based on the on-disk format, while another of the formats in which the data is made available for query processing is independent of the on-disk format. Data that is in the format that is independent of the disk format may be maintained exclusively in volatile memory to reduce the overhead associated with keeping the data in sync with the on-disk format copies of the data.