Per-Segment Columnstore Mapping for Mixed-Workload Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems struggle to efficiently handle workloads that require both transactional and analytical processing, leading to increased resource consumption and slower query response times.
Innovation Solution
Implementing per-segment mapping structures and index tables within a columnstore to facilitate fast seeking of rows, allowing for efficient processing of both transactional and analytical queries by reducing the need for scanning entire segments.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If columnstore tables are used for analytical processing, then query processing speed is improved, but resource consumption increases when handling both transactional and analytical workloads
Solution Approach 1:
The columnstore is divided into multiple segments, with each segment having its own mapping structure and index table. This segmentation allows the system to efficiently handle both analytical queries (by scanning relevant segments) and transactional queries (by using index tables for fast row location), thereby improving query processing speed while optimizing resource consumption through selective segment access.
2Loss of time
If rowstore tables are used for transactional processing, then row location efficiency is improved, but query response time increases for analytical processing
Solution Approach 1:
The columnstore is segmented with each segment containing an index table that maps keys to row positions. This enables fast row location (similar to rowstore) while maintaining the analytical processing capabilities of columnstore, thus reducing row location time without sacrificing query response time for analytical workloads.
Solution Approach 2:
The columnstore structure with per-segment index tables provides multi-functionality by supporting both transactional processing (fast row location via indexes) and analytical processing (efficient data retrieval via columnar storage), eliminating the need to choose between rowstore and columnstore for different workload types.
3Speed
If per-segment mapping structures are implemented, then query processing speed is improved, but device complexity increases
Solution Approach 1:
The data structure is segmented into multiple independent mapping structures, each associated with a specific columnstore segment. This segmentation improves query processing speed by allowing direct access to relevant segments through their respective mapping structures, while the modular nature of segmentation keeps the complexity manageable through repetition of standardized components.
Data Source
AI summary
Disclosed herein is a computer-implemented method of processing a query using a columnstore comprising a plurality of segments, the method comprising: receiving a query comprising a parameter; determining a key corresponding to the parameter; identifying a mapping structure, from a plurality of mapping structures, relating to said key, wherein each mapping structure corresponds to a respective segment of the columnstore; interrogating the identified mapping structure to determine a value corresponding to the key, wherein the value identifies an entry of an index table of a plurality of index tables that corresponds to the respective segment, wherein the entry of the index table identifies a row of the respective segment of the columnstore relating to the parameter; and retrieving data relating to the parameter from a data source based on data stored in the entry of the index table.