Database Table Rotation for Lock-Free Schema Upgrades
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database table rotation methods face challenges in modifying the schema of related tables without locking them, leading to data loss and inefficiencies, especially when dealing with large volumes of data.
Innovation Solution
Modify the schema of a particular table only when it is about to become active by truncating its data and applying the changes during rotation, ensuring continuous data writing and reducing computational expense.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Stability of the object's composition
If the schema of all shadow tables is modified simultaneously, then the master table schema is kept consistent across all tables, but the operation becomes computationally expensive and time-consuming, especially for tables with large numbers of rows
Solution Approach 1:
The patent divides the shadow tables into separate entities, each with its own schema modification process. Instead of modifying all shadow tables simultaneously, the system modifies schemas individually for each shadow table based on its specific needs, allowing parallel processing and reducing overall modification time while maintaining consistency through the master table schema reference.
Solution Approach 2:
The system performs preliminary actions by identifying which shadow tables require schema modifications before executing the changes. The master table schema is updated first, and then individual shadow tables are evaluated and modified only if necessary, avoiding unnecessary computational overhead on tables that don't require changes.
2Ease of operation
If the schema of shadow tables is modified while they are active, then the tables remain available for writing, but the tables must be locked during modification, resulting in data loss or long queuing times for incoming data
Solution Approach 1:
The patent implements a dynamic schema modification approach where the system determines whether a shadow table is currently active before attempting modification. If a table is active, the system waits until it becomes inactive (rotates out) before applying schema changes. This dynamic timing ensures that modifications occur only when safe, preventing data loss while maintaining table availability for the majority of operational time.
Solution Approach 2:
The system uses periodic table rotation as an opportunity to perform schema modifications. Since shadow tables are rotated periodically anyway, the system leverages these natural rotation events to apply schema changes to tables that are becoming inactive, ensuring modifications occur at appropriate intervals without disrupting ongoing operations.
3Stability of the object's composition
If the schema of a shadow table is modified when it contains a large amount of data, then the table structure is updated, but the operation becomes computationally expensive and time-consuming
Solution Approach 1:
The system performs preliminary truncation of shadow tables before applying schema modifications. By clearing the data first and then modifying the schema on an empty table, the system avoids the computationally expensive operation of modifying schemas on populated tables. After the schema change is applied efficiently, the table is ready to receive new data without having undergone a lengthy modification process.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A database server may include a master table schema (602) that defines a database table's configuration and an arrangement for corresponding shadow tables (604, 606, 608). The shadow tables (604, 606, 608) contain data related to contiguous and non-overlapping time periods and writing to the shadow tables (604, 606, 608) occurs in a rotational fashion so that only one active table is written to at any point. The server may upgrade the master table schema. The server then may determine that a rotation event has occurred where a first shadow table (604) is active and a second shadow table (606) is associated with an oldest of the contiguous and non-overlapping time periods. In response, the server may delete data in the second table (606), determine that the schema (602) has been upgraded since the second table (606) was most recently active, upgrade the second table's schema to match the schema, and set the second table (606) to active enabling writing to the second table (606).