Database Partition Key Update via Row ID Extraction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In distributed database systems, updating partitioning key fields leads to inefficient transaction latency and resource consumption due to the need to move rows across database nodes, as conventional methods require fetching and analyzing entire rows for re-partitioning.
Innovation Solution
Fetching and updating only the row identifiers to determine which rows need to be moved, allowing for efficient relocation of rows to new partitions with reduced bandwidth and resource usage by utilizing the unique $row_id column values.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If conventional methods are used to update partitioning key fields, then rows can be moved to new partitions, but transaction latency increases and resource consumption increases due to fetching and analyzing entire rows
Solution Approach 1:
The patent extracts only the row identifier field from the complete row data, updating only this minimal subset of information to determine partition movement requirements. This extraction principle resolves the contradiction by eliminating the need to fetch and analyze entire rows (which causes time loss and reduced productivity) while still enabling the necessary partition update functionality.
Solution Approach 2:
The patent segments the row data processing into distinct components: first updating only the row identifier field to determine partition requirements, then separately fetching complete row data only for rows that need to be moved. This segmentation resolves the contradiction by separating the critical update operation from the optional data transfer operation, improving transaction latency while maintaining accuracy.
2Productivity
If conventional methods are used to update partitioning key fields, then rows can be moved to new partitions, but resource consumption increases due to fetching and storing complete rows
Solution Approach 1:
The patent applies the extraction principle by retrieving only the row identifier field instead of complete row data during the update operation. This dramatically reduces the quantity of data (substance) that needs to be fetched, processed, and transferred across the network, thereby reducing resource consumption while maintaining the ability to accurately determine which rows require partition movement.
Solution Approach 2:
The patent implements partial action by performing the update operation on only the necessary subset of data (row identifiers) rather than complete rows. This partial approach reduces data transfer volume and resource consumption while still achieving the essential function of determining partition movement requirements. Complete row data is fetched only partially - and only after it has been determined that movement is necessary.
3Measurement precision
If complete rows are fetched for update, then accurate partition determination can be made, but bandwidth consumption increases
Solution Approach 1:
The patent extracts only the row identifier field for the update operation, which is sufficient to determine partition requirements. This extraction maintains measurement precision for partition determination while dramatically reducing bandwidth consumption by avoiding transmission of complete row data. The row identifier contains all necessary information to identify the row and determine its new partition assignment.
4Productivity
If rows are moved to new partitions based on updated partitioning keys, then data distribution is optimized, but transaction latency increases due to data movement overhead
Solution Approach 1:
The patent performs preliminary action by first updating only the row identifier field to determine which rows require partition movement, before fetching and moving the complete row data. This preliminary update step allows the system to identify and prepare for necessary movements without immediately incurring the time cost of data transfer, optimizing the overall transaction process by separating determination from execution.
Solution Approach 2:
The patent segments the row movement process into distinct phases: (1) update row identifier and determine partition requirements, (2) fetch complete row data only for rows requiring movement, (3) move rows to new partitions. This segmentation reduces transaction latency by avoiding unnecessary data fetching and movement for rows that remain in their current partitions, while still achieving optimal data distribution where needed.
Data Source
AI summary
A system includes reception of a query to update a partition key value of a first set of rows of a database table, determination, based on fetched row identifiers, of a first subset of the first rows which are not stored on the first database server node and a second subset of the first rows which are stored on the first database server node, fetching of the first subset of rows from the database server nodes, update of the partition key value of each row of the fetched first subset of rows, instructing of the first database server node to: store the updated rows of the fetched first subset in the first partition stored on the first database server node, and update the partition key value of each of the second subset of rows of the partition stored on the first database server node.