Snapshot Tables for Zero Downtime Cloud Database Upgrades
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for upgrading cloud-based software systems often require taking the system offline, resulting in downtime and consuming significant technical resources, which is inefficient and wasteful.
Innovation Solution
The use of snapshot tables and database triggers enables production access to pre-upgrade data records during an upgrade procedure from a first release to a second release, without the need for copying database tables, thus reducing resource consumption and minimizing downtime.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If traditional upgrade procedures are used, then the system can be upgraded to a new release, but the system must be taken offline resulting in downtime and users cannot interact with the application
Solution Approach 1:
The patent segments the database access layer into multiple schemas (first access schema and second access schema) that can operate independently. The first schema provides read-only access to pre-upgrade data through snapshot tables, while the second schema handles write operations and new release functionality. This segmentation allows the system to maintain availability during upgrades by directing different types of operations to appropriate schemas.
Solution Approach 2:
The patent creates snapshot tables and snapshot projection views before the upgrade procedure begins. These snapshot tables capture the state of data tables prior to upgrade, and the projection views are pre-configured to provide read-only access to this historical data. This preliminary action ensures that read operations can continue uninterrupted during the upgrade process.
2Reliability
If database tables are copied to maintain access during upgrades, then continuous access can be provided, but significant technical resources and memory are consumed
Solution Approach 1:
The patent uses snapshot tables that are logically separated from the main data tables but physically reside in the same database system. Rather than creating full copies of data tables, the snapshot tables store only the necessary historical data in a compressed format, significantly reducing memory consumption while still providing the required read-only access during upgrades.
Solution Approach 2:
The snapshot projection views serve multiple functions: they provide read-only access to pre-upgrade data, enable continuous read operations during upgrades, and allow the system to maintain a single shared database infrastructure. This multi-functionality eliminates the need for separate database copies for different purposes, reducing overall resource consumption.
3Ease of manufacture
If the system is taken offline for maintenance, then upgrade procedures can be executed, but technical resources remain idle between upgrade procedures
Solution Approach 1:
The patent enables continuous useful action by allowing read operations to proceed uninterrupted during upgrade procedures. The first access schema with snapshot tables handles read requests while the second access schema performs upgrade operations, ensuring that the system remains productive throughout the maintenance window rather than being completely offline.
Solution Approach 2:
The patent implements dynamic schema switching where the system can flexibly direct read operations to the first access schema (using snapshot tables) and write operations to the second access schema. This dynamic routing allows the system to adapt during upgrades, maintaining high resource utilization by keeping both schemas active and serving different operational needs simultaneously.
Data Source
AI summary
Implementations can include providing a snapshot table, replacing, in a first access schema, a first projection view with a union view, creating, in a second access schema, a snapshot projection view, a second projection view, and a set of database triggers, and during execution of an upgrade procedure that results in changes to records of the data table: recording the changes to the records of the data table in the snapshot table, for each record of the data table changed, providing the record in the snapshot table in an unchanged state through at least one database trigger and the snapshot projection view, and returning a set of records through the union view in response to a query, the set of records including a first sub-set of records of the data table and a second sub-set of records of the snapshot table.