Composite Primary Key for LSM-Tree Partition Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face challenges in storing data from tables without primary keys using the log-structured-merge tree (LSM-tree) storage architecture, as they lack a valid primary key for data organization.
Innovation Solution
A method and apparatus for creating a partition table with a composite primary key, which includes a partition identifier of a physical partition and a partition-level auto-increment identifier, allowing data to be stored and retrieved efficiently using LSM-Tree.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If a table without primary keys stores data using LSM-Tree, then data storage capability is improved, but data organization becomes impossible due to lack of valid primary key
Solution Approach 1:
The primary key is segmented into two parts: partition identifier (indicating data location) and row identifier (indicating data position within partition). This segmentation allows data organization without requiring traditional primary keys, enabling LSM-Tree storage for tables without primary keys while maintaining data organization capability.
Solution Approach 2:
A hidden primary key is introduced as an intermediary element that bridges the gap between tables without primary keys and the LSM-Tree storage requirement. This hidden primary key (comprising partition identifier and row identifier) enables data organization and storage in LSM-Tree without modifying the original table schema or requiring actual primary keys.
2Adaptability or versatility
If partition management operations (merging and switching) are performed, then system flexibility is improved, but primary key uniqueness may be compromised
Solution Approach 1:
The partition identifier is assigned in advance to each physical partition, establishing a globally unique identifier before any partition management operations occur. This preliminary assignment ensures that even when partitions are merged or switched, the uniqueness of primary keys is maintained because the partition identifier component remains distinct and traceable.
Solution Approach 2:
The system changes the parameter of partition identifiers to be globally unique across all partitions. This parameter change ensures that when partition management operations (merging and switching) are performed, the primary key uniqueness is preserved because the partition identifier component maintains its uniqueness property throughout these operations.
3Measurement precision
If composite primary key with partition identifier is used, then data retrieval accuracy is improved, but system complexity increases
Solution Approach 1:
The partition identifier is extracted as a separate component from the traditional primary key concept. This extraction allows the system to use only the necessary elements (partition identifier and row identifier) for data retrieval, improving accuracy while keeping the implementation relatively simple by focusing on essential components rather than complex primary key structures.
Data Source
AI summary
A computer-implemented method for partition table data writing includes obtaining a first data row to be written into a first partition table and a current partitioning rule indicating at least that the first partition table includes a partitioning key that is a basis for dividing data rows to be written into logical partitions and a first quantity of partitions. A target logical partition corresponding to the first data row is determined. A target physical partition is determined, where the target physical partition has a globally unique partition identifier. The globally unique partition identifier of the target physical partition and an auto-increment identifier of the first data row to the first data row are added as a composite primary key value. The first data row with the composite primary key value is stored to the first partition table.