Multi-Key Partitioning with Patch Indexes for Skewed Query Workloads

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Modern data warehouse systems face challenges in achieving balanced partitioning and efficient query performance due to issues with load balancing and data repartitioning when using single partition keys, especially in scenarios with skewed data distributions and complex schemas.

Innovation Solution

A method for multi-key partitioning that assigns data tuples to partitions based on multiple keys while allowing exceptions, using PatchIndexes to maintain sets of patches and optimize query processing, leveraging graph partitioning algorithms to ensure partition-local execution and balanced partitioning.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If value-based partitioning (hash or range) is used to enable partition pruning and partition-local execution, then query performance is improved through partition pruning and local execution, but load balancing deteriorates when data is skewed leading to imbalanced partitions

Engineering Contradiction:

Improvequery performanceVSAvoidload balancing

Core Design Contradiction:

ProductivityVSEase of operation

Solution Approach 1:

The patent segments the partitioning problem into two independent components: (1) partition assignment based on partition keys for query optimization, and (2) load balancing through repartitioning of skewed data. This allows each aspect to be optimized separately without compromising the other.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The system performs preliminary repartitioning of skewed data before query execution. By pre-processing the data to redistribute skewed values across partitions, the system eliminates the need for runtime repartitioning during query execution, improving both load balancing and query performance.

Inventive Principle:

Principle #10Preliminary action

2Device complexity

If a single partition key is used to simplify the partitioning scheme, then device complexity is reduced, but adaptability deteriorates when the chosen partition key does not match join or grouping columns requiring expensive data repartitioning

Engineering Contradiction:

Improvepartitioning scheme complexityVSAvoidquery execution flexibility

Core Design Contradiction:

Device complexityVSAdaptability or versatility

Solution Approach 1:

The patent implements multi-key partitioning where data is partitioned according to multiple partition keys simultaneously. This allows the same partitioning structure to support queries with different join and grouping columns, making the system adaptable to various query patterns without requiring data repartitioning.

Inventive Principle:

Principle #6Universality (Multi-functionality)

Solution Approach 2:

The system extends the partitioning from a single dimension (one partition key) to multiple dimensions (multiple partition keys). By organizing data along multiple key dimensions, the system enables partition-local execution for queries involving any of the partition keys, greatly enhancing adaptability.

Inventive Principle:

Principle #17Another dimension (Dimensionality change)

3Adaptability or versatility

If multiple partition keys are defined to enable partition-local execution for multiple join/grouping columns, then adaptability is improved, but device complexity increases without data replication

Engineering Contradiction:

Improvepartition-local execution capabilityVSAvoidmulti-key partitioning structure

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The patent merges multiple partitioning strategies into a unified multi-key partitioning framework. Instead of maintaining separate partitioned copies of data for different keys (which would require replication), the system combines multiple partition keys into a single partitioning structure that achieves the benefits of both.

Inventive Principle:

Principle #5Merging (Combining)

4Adaptability or versatility

If repartitioning is performed during query execution to handle sub-optimal partition keys, then adaptability is maintained, but productivity deteriorates due to expensive network transfers

Engineering Contradiction:

Improvequery optimization flexibilityVSAvoidquery execution efficiency

Core Design Contradiction:

Adaptability or versatilityVSProductivity

Solution Approach 1:

The system performs the repartitioning action preliminarily, before query execution begins. By pre-distributing data according to multiple partition keys, the system ensures that partition-local execution is possible for various query types without requiring expensive runtime repartitioning and network transfers.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS12561323B2Patched multi-key partitioning for robust query performance

Publication Date: 2026.02.24 ACTIAN CORP

AI summary

Aspects described herein relate to partitioning multiple data tuples stored in a database system to multiple partitions based on multiple partition keys. The multiple data tuples can be assigned to multiple partitions such that each partition of the multiple partitions includes a portion of the multiple data tuples having same values for at least one of the multiple partition keys. A set of exceptions can be generated including data tuples having a first value of at least a first one of the multiple partition keys that is the same as the same value of a first portion of data tuples assigned to a first one of the multiple partitions, and a second value of at least a second one of the multiple partition keys that is the same as the same value of a second portion of data tuples assigned to a second one of the multiple partitions.