Zero-Downtime Relational Database Schema Migration via Intermediate Version
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for upgrading cloud services reliant on relational databases face downtime issues due to non-backwards compatible database schema changes, particularly when migrating from columns like VARCHAR to CLOB, as they require parallel operation of old and new application versions, which is not feasible with current blue-green deployment strategies.
Innovation Solution
The solution involves creating an intermediate application version that behaves differently based on a new supportive column in the relational database, allowing for zero-downtime migration by adding a new column, preserving old data, and gradually transitioning to the new column definitions, thereby enabling continuous operation without requiring special database features.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If database schema changes are performed (e.g., migrating from VARCHAR to CLOB), then the new application version can support enhanced functionality, but service downtime occurs because old and new versions cannot operate in parallel
Solution Approach 1:
The patent segments the application deployment into three distinct versions: old version, intermediate version, and new version. The intermediate version is further segmented to handle different data sources based on a flag column, allowing gradual migration from old data format to new data format without requiring simultaneous operation of incompatible versions.
Solution Approach 2:
The intermediate version acts as an intermediary between the old and new application versions. It contains conditional logic that reads from either the old column or new column based on a flag value, enabling smooth transition and avoiding the need for direct compatibility between old and new versions.
2Reliability
If blue-green deployment strategy is used, then service continuity can be maintained, but it is not feasible when database schema changes require non-backwards compatible updates
Solution Approach 1:
The intermediate version introduces dynamic behavior by conditionally routing data access based on a flag column value. This dynamic switching capability allows the system to adapt its data access pattern in real-time, enabling it to work with both old and new schema formats without requiring fixed, static compatibility.
3Stability of the object's composition
If old application version continues to operate with old database schema, then data integrity is maintained, but upgrade to new version cannot proceed without downtime
Solution Approach 1:
The system performs preliminary actions by creating the new column and intermediate version before fully committing to the new schema. The flag column is prepared in advance to control the transition, allowing the system to migrate data and switch versions without interrupting service, thus maintaining data integrity while enabling seamless upgrade.
Data Source
AI summary
A method includes providing a relational database and at least one schema describing tables of the relational database with columns of the relational database, providing an application executing read/write or delete jobs in the relational database where the application is running in a cloud environment, upgrading the application running in the cloud environment to a new version of the application. The application uses pods to perform the steps of the upgrading. The method includes replacing an old version of the application using an intermediate version of the application, where the intermediate version of the application behaves differently depending on values of definitions in a new supportive column created in the relational database, and replacing the intermediate version of the application with pods using the new version of the application.