Distributed Database Table Colocation for Faster Join Queries

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing methods for managing data in distributed database systems face challenges such as increased contention among nodes, reduced throughput, and frequent transaction aborts due to the need to access data from multiple nodes, especially when handling complex queries or join operations.

Innovation Solution

The method involves relocating tables with a defined colocation relationship based on distribution keys to ensure that data from multiple tables reside on the same node, allowing for dynamic partitioning and reducing the need to access multiple nodes during queries.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If data from multiple tables is distributed across multiple nodes, then data availability is improved, but query throughput deteriorates due to increased contention and frequent transaction aborts

Engineering Contradiction:

Improvedata availabilityVSAvoidquery throughput

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The patent segments data by creating distinct table groups with different colocation relationships. Each table group is assigned to specific nodes based on its access patterns and join requirements, allowing queries to be routed to single nodes when possible while maintaining data distribution across the cluster for availability.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent introduces a new dimension of data organization by adding colocation relationships between tables. This creates a multi-dimensional data placement strategy where data can be located both by node distribution (for availability) and by table colocation groups (for query performance), resolving the contradiction between distributed storage and single-node query efficiency.

Inventive Principle:

Principle #17Another dimension (Dimensionality change)

2Speed

If tables are colocated on the same node, then join query performance is improved, but node contention increases and reduces overall system throughput

Engineering Contradiction:

Improvejoin query speedVSAvoidsystem throughput

Core Design Contradiction:

SpeedVSProductivity

Solution Approach 1:

The patent applies local quality by creating different colocation strategies for different table groups based on their specific access patterns. Frequently joined tables are colocated on the same nodes to optimize join performance, while tables with different access patterns are distributed to different nodes to balance load and prevent contention hotspots.

Inventive Principle:

Principle #3Local quality

Solution Approach 2:

The patent enables dynamic adjustment of colocation relationships and data placement based on workload characteristics. The system can adaptively modify which tables are colocated together and which nodes they reside on, optimizing the balance between join performance and system-wide throughput as query patterns change over time.

Inventive Principle:

Principle #15Dynamics

3Adaptability or versatility

If data is distributed across multiple nodes, then system scalability is improved, but the complexity of data management increases

Engineering Contradiction:

Improvesystem scalabilityVSAvoiddata management complexity

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The patent creates a universal data management framework that handles multiple functions through a unified colocation mechanism. The same colocation relationships that optimize join queries also guide data placement during inserts, updates, and deletes, and inform query routing decisions, reducing the complexity of managing distributed data across multiple nodes.

Inventive Principle:

Principle #6Universality (Multi-functionality)

4Adaptability or versatility

If load balancer is used to manage data distribution, then system adaptability is improved, but response time increases due to reactive approach

Engineering Contradiction:

Improvesystem adaptabilityVSAvoidresponse time

Core Design Contradiction:

Adaptability or versatilityVSLoss of time

Solution Approach 1:

The patent implements preliminary action by pre-establishing colocation relationships between tables and pre-determining data placement on nodes based on anticipated query patterns. This proactive approach eliminates the need for reactive load balancing decisions at query execution time, reducing response time while maintaining system adaptability through predefined optimization strategies.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS12386861B2Method and system for efficient data management in distributed database system

Publication Date: 2025.08.12 HUAWEI TECH CO LTD

AI summary

The present disclosure provides methods, systems and apparatus for efficient data management in distributed database system. According to an aspect, a method is provided. The method includes relocating a first table and a second table of a database to a first node of the database, wherein the first table and a second table (or one or more tables which have a colocation relationship with the first table) are associated with one or more distribution keys corresponding to one or more columns of the first table and the second table. The method further includes defining a colocation relationship between the first table and the second table based on a distribution key of the one or more distribution keys. The colocation relationship may indicate that data of the first table and the second table that correspond according to the distribution key are to reside on a same node of the database.