Distributed Database Column Switching Without GSI Access Failures
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database solutions fail to perform column changing operations in distributed databases without causing execution failures of user's write or read access requests, particularly due to inconsistencies in column types between data tables and global secondary indexes, and require lengthy data copying and locking mechanisms.
Innovation Solution
A method involving adding a second column with a change target value, configuring user access logic to replace the first column, and synchronizing structure definitions across computing nodes to ensure seamless column attribute changes without user access failures.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If traditional column changing method is used on distributed database, then column type modification can be performed, but user's write or read access requests will fail due to column type inconsistencies between data table and GSI
Solution Approach 1:
The column changing process is segmented into distinct phases: adding new columns to both data table and GSI, copying data in background, switching column names atomically, and removing old columns. This segmentation allows each phase to be executed independently without blocking user access, resolving the contradiction between column changing capability and access reliability.
Solution Approach 2:
The patent performs preliminary actions by adding new columns (e.g., 'b' column) to both the data table and GSI before the actual column changing occurs. Data is pre-copied to these new columns in the background. When user access is needed, the system can immediately switch to using the new columns without interruption, ensuring both adaptability and reliability.
2Adaptability or versatility
If data copying is performed to new table for column changing, then column attribute modification can be achieved, but execution time becomes excessively long
Solution Approach 1:
The patent maintains continuity of useful action by performing data copying in the background continuously without interrupting user operations. The copying process runs asynchronously, utilizing idle system resources, while user read/write operations continue uninterrupted on the original columns. This resolves the contradiction by achieving column changing capability without time loss.
Solution Approach 2:
Instead of creating entirely new tables for column changing, the patent copies only the necessary column data to newly added columns within the existing table structure. This selective copying approach significantly reduces the time and resources required compared to full table copying, while still achieving the desired column attribute modification.
3Stability of the object's composition
If table locking is applied for column changing operation, then data consistency can be maintained, but normal service execution is blocked
Solution Approach 1:
The patent introduces an intermediary mechanism - a column name mapping layer - that sits between user operations and physical storage. When column changing is needed, the mapping is updated to point to new columns without locking the table. This intermediary approach maintains data consistency through the mapping relationship while allowing continuous service execution, resolving the contradiction between stability and productivity.
Solution Approach 2:
The patent changes the parameter being modified from the physical column structure to the logical column name mapping. By updating the mapping parameters rather than the physical structure, the system maintains data consistency through the mapping relationship while avoiding table locks, thus preserving service execution efficiency during column changing operations.
4Adaptability or versatility
If third-party tools are used for column changing, then column modification can be performed, but additional triggers or Binlog mechanisms are required increasing system complexity
Solution Approach 1:
The patent implements self-service by providing native column changing functionality within the distributed database system itself. The database automatically handles column addition, data copying, mapping updates, and column removal without requiring external third-party tools, triggers, or Binlog mechanisms. This eliminates additional system dependencies while maintaining full column changing capability.
Data Source
AI summary
A database system and a column changing method for a database are provided, where a column attribute of a first column is changed to a change target value. Second columns are added into a physical data table and a physical GSI table, and into respective structure definitions of a logical data table and a logical GSI table, and are hidden from a user. The second columns in the logical data and GSI tables are respectively associated with the second columns in the physical data and GSI tables, and a value of the attribute of the second column in the logical table is the change target value. Configuration is performed in the respective structure definitions, to replace a first column with the second column in user access logic of the database. The first columns are deleted from the physical data and GSI tables, and from the respective structure definitions.