Live Database Schema Change via Logical Name Switching
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional database systems do not allow for multiple schema changes to be made to a database table while keeping the table live, leading to interruptions in database availability and potential data inconsistency during queries.
Innovation Solution
The system performs live schema changes by mirroring data to a new table with a new schema format, allowing multiple changes to be made without downtime, ensuring data consistency, and enabling simultaneous access to the table during alterations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If schema changes are made to a database table, then the schema can be updated to reflect new requirements, but database availability is interrupted and data consistency is compromised
Solution Approach 1:
The patent divides the schema change process into multiple independent phases: creating a new table with the updated schema, copying data from the old table to the new table, validating the copied data, and finally switching the application to use the new table. This segmentation allows schema changes to occur without interrupting database availability, as the old table remains accessible during the transition.
Solution Approach 2:
The patent performs preliminary actions by creating the new table with the updated schema before actually executing the schema change. The new table is prepared in advance, and data is copied over before the switch is finalized. This preliminary preparation ensures that when the schema change is needed, the system can switch tables without interrupting service, maintaining database availability.
2Adaptability or versatility
If schema changes are made to a database table, then the schema can be updated to reflect new requirements, but data consistency during queries is compromised
Solution Approach 1:
The patent segments the schema change into distinct phases where data is copied from the old table to the new table in a controlled manner. During the copy process, the system maintains consistency by ensuring all data is properly transferred before switching. This segmentation prevents data loss and maintains consistency during queries, as the system can choose to read from either the old or new table consistently throughout the transition.
Solution Approach 2:
The patent implements feedback mechanisms to validate the copied data and ensure consistency during the schema change process. The system checks that data was successfully copied and validates integrity before finalizing the switch. This feedback loop ensures data consistency is maintained throughout the schema change operation.
3Ease of manufacture
If multiple schema changes are made sequentially, then each change can be applied individually, but the total downtime accumulates and productivity is reduced
Solution Approach 1:
The patent merges multiple schema changes into a single coordinated operation. Instead of executing schema changes sequentially with separate downtime periods, the system creates a new table, copies all data, validates the copy, and switches in a single coordinated sequence. This merging of operations eliminates cumulative downtime and maintains database availability throughout the schema evolution process.
Solution Approach 2:
The patent ensures continuity of useful action by maintaining database accessibility throughout the schema change process. The system allows queries to continue executing against the old table while the new table is being prepared and populated. This continuous operation eliminates interruptions and maintains productivity during schema evolution.
Data Source
AI summary
Techniques are disclosed in which a computer system implements schema changes for existing database objects by maintaining a logical name and a current physical name corresponding to an existing database object, where the logical name points to the current physical name. The system receives a schema change request specifying the logical name and format changes for the existing object. The system performs a schema change operation in response to the request, including: creating a new database object having a new physical name, copying and transforming data from the existing object to the new object according to the format changes, mirroring new writes directed to the logical name to both the existing and new objects, and causing the logical name to point to the new physical name instead of the current physical name after completing the copying. The system responds to subsequent queries, specifying the logical name, from the new object.