Graph Representation of Join History for Database Data Distribution

Solution Overview

Problem

Existing database technologies face performance issues due to the distribution of large data sets across multiple storage locations, as the location of data significantly impacts query performance, particularly for join operations, where similar values are not co-located, leading to increased resource consumption and communication costs.

Innovation Solution

Implementing a graph representation of join history to identify and co-locate columns likely to be joined, allowing for efficient data distribution across multiple storage locations, thereby optimizing join operations by maximizing the likelihood and cost-effectiveness of column co-location.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If data is distributed across multiple storage locations to enable parallel processing, then query processing capacity increases, but query performance deteriorates due to increased communication costs and data access complexity

Engineering Contradiction:

Improvequery processing capacityVSAvoidquery performance

Core Design Contradiction:

ProductivityVSLoss of time

Solution Approach 1:

The system performs preliminary analysis of join history to identify columns that are likely to be joined, and proactively co-locates these columns in the same data partitions before queries are executed. This advance preparation ensures that when join queries are run, the necessary data is already positioned for efficient access, eliminating the need for costly data movement during query execution.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent applies different data distribution strategies to different columns based on their join characteristics. Columns identified as having high join probability are co-located in the same partitions, while other columns may be distributed differently. This localized optimization for specific columns resolves the contradiction by improving join query performance without sacrificing overall system parallelism.

Inventive Principle:

Principle #3Local quality

2Productivity

If data is distributed across multiple storage locations, then system scalability improves, but communication and processing costs increase due to data movement between locations

Engineering Contradiction:

Improvesystem scalabilityVSAvoidcommunication and processing costs

Core Design Contradiction:

ProductivityVSLoss of energy

Solution Approach 1:

The system performs preliminary analysis of join history to identify columns that are likely to be joined, and proactively co-locates these columns in the same data partitions before queries are executed. This advance preparation ensures that when join queries are run, the necessary data is already positioned for efficient access, eliminating the need for costly data movement during query execution.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent applies different data distribution strategies to different columns based on their join characteristics. Columns identified as having high join probability are co-located in the same partitions, while other columns may be distributed differently. This localized optimization for specific columns resolves the contradiction by improving join query performance without sacrificing overall system parallelism.

Inventive Principle:

Principle #3Local quality

3Loss of time

If similar values are co-located in similar storage locations, then query performance improves by reducing the number of storage locations accessed, but data distribution complexity increases

Engineering Contradiction:

Improvequery performanceVSAvoiddata distribution complexity

Core Design Contradiction:

Loss of timeVSDevice complexity

Solution Approach 1:

The system automatically analyzes join history and determines optimal data distribution strategies without requiring manual intervention. The automated analysis of join patterns and the subsequent co-location decisions eliminate the need for complex manual data distribution planning, resolving the contradiction by improving query performance through automated rather than manual processes.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system continuously monitors join operations and uses this feedback to refine its understanding of which columns are likely to be joined. This feedback loop enables the system to adaptively optimize data distribution over time, improving query performance while keeping the distribution strategy manageable through data-driven decision-making rather than complex predetermined rules.

Inventive Principle:

Principle #23Feedback

Data Source

PatentUS11500931B1Using a graph representation of join history to distribute database data

Publication Date: 2022.11.15 AMAZON TECH INC

AI summary

Using a graph representation of join history may be performed to distribute database data. Join history may be collected, captured, or tracked which describes the history of join operations between columns of different tables in a database. A graph representation of the join history may be generated. The graph representation may indicate a likelihood of different joins that may be performed between the columns of the tables of a database. An evaluation of the join history may be performed to identify columns for tables in the database to distribute the data of the tables amongst multiple storage locations according to the identified columns.