Table Placement Rules for Distributed Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In distributed databases, transferring large database tables between nodes for query execution can lead to significant bottlenecks due to the time-consuming process of data transfer, resulting in decreased query performance and user experience, especially in high-performance in-memory database systems.
Innovation Solution
Implementing table placement rules and redistribution methods to store tables or their partitions on multiple computing nodes, ensuring that frequently joined tables are co-located on the same node, thereby minimizing the need for inter-node data transfer and optimizing query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If tables are distributed across multiple nodes in a distributed database, then storage capacity and system scalability are improved, but query execution time increases due to inter-node data transfer requirements
Solution Approach 1:
The patent segments database tables into partitions that can be distributed across multiple nodes. Each partition contains a subset of the table data, allowing the database to scale storage capacity across nodes while enabling selective local access to partitions, thereby reducing inter-node data transfer for queries that only need specific partitions.
Solution Approach 2:
The patent implements preliminary actions by pre-joining tables that are frequently accessed together into a single materialized view or wide table. This pre-processing step consolidates data that would otherwise require multiple inter-node transfers, allowing queries to execute locally without repeated data movement across the network.
2Productivity
If tables are stored on separate nodes, then storage distribution and load balancing are improved, but data transfer overhead increases for join operations
Solution Approach 1:
The patent merges frequently joined tables into a single consolidated table or materialized view that is stored on one node. This combining eliminates the need for repeated data transfers between nodes for join operations, reducing energy overhead while maintaining load balancing for other operations across the distributed system.
Solution Approach 2:
The patent creates copies of tables or partitions on multiple nodes through replication. This allows queries to be executed locally using cached copies, eliminating inter-node data transfer for read operations while the primary node maintains the authoritative copy for write operations, thus balancing load and reducing transfer overhead.
3Adaptability or versatility
If large tables are transferred between nodes for query execution, then query flexibility and data accessibility are improved, but network bandwidth consumption and query performance deteriorate
Solution Approach 1:
The patent implements local quality by allowing different nodes to store different partitions or copies of tables based on query patterns and data access requirements. Each node optimizes its local storage to handle specific query types efficiently, providing query flexibility through distributed specialization while avoiding unnecessary data transfers by serving queries locally when possible.
Data Source
AI summary
A node type of a plurality of distributed nodes to which a table to be added to a distributed database should be assigned can be identified by applying a set of placement rules defined for the table. The set of placement rules can also be applied to determine whether the table should be partitioned into more than one partition. A table group name associated with the table can be obtained and used in conjunction with the node type and determination of whether to partition the table to store the table in the distributed database on at least one node of the plurality of nodes as one or more partitions.