Time-Series Database Schema Versioning for Instant Table Structure Changes
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for managing time-series database table structures are inefficient, particularly when adding new parameters, as they either waste storage space or require inefficient updates to existing data when the structure changes.
Innovation Solution
A method that allows instantaneous changes to the table structure by using a schema version and column ID system, enabling parallel use of new and old schema definitions, with incremental schema versioning and automatic column ID management, and efficient data writing and querying processes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If a traditional relational database is used where each parameter is one column, then multiple parameters share one timestamp saving storage space, but adding a new parameter requires adding a new column which is extremely inefficient for tables with large number of recorded entries
Solution Approach 1:
The patent segments the schema modification process into two independent parts: metadata updates (schema definition) and data updates (existing records). By allowing schema changes without requiring data row updates, it separates the structural modification from the data modification, enabling instant schema changes even for tables with large numbers of entries.
Solution Approach 2:
The patent introduces a new dimension to the data model by adding a schema version identifier to each data row. This allows the system to track which schema version each data row was created under, enabling backward compatibility and efficient schema evolution without requiring updates to existing data rows.
2Adaptability or versatility
If each collected parameter is stored as one independent time series, then flexibility is improved, but each series needs to save its own timestamp causing storage space waste
Solution Approach 1:
The patent merges multiple parameters that share the same timestamp into a single data row with multiple columns, rather than storing each parameter as a separate time series. This consolidation reduces storage space by eliminating redundant timestamp repetitions while maintaining the flexibility to handle different parameter types and schemas.
3Reliability
If schema modification requires updating all existing data, then data consistency is maintained, but the operation becomes extremely inefficient for large tables
Solution Approach 1:
The patent performs preliminary actions by adding a schema version identifier to the data model structure before actual schema modifications are needed. This pre-established versioning mechanism allows the system to handle schema changes efficiently by simply updating the schema definition and version tracking, rather than requiring comprehensive data row updates when schema changes occur.
Solution Approach 2:
The schema version identifier acts as an intermediary between the schema definition and the data rows. It mediates the relationship by allowing data rows to be associated with specific schema versions without requiring direct structural changes to the data rows themselves, thus maintaining data consistency while enabling efficient schema evolution.
Data Source
AI summary
The invention discloses a processing method for process changing time-series database table structure, wherein comprising following steps: an application side generates a data table containing a table name ID, a schema version, and a column ID; when a data column is increased, the application side modifies a schema of the data table, increases the schema version, assigns a new column ID number to a newly increased column incrementally, and assigns a default value to the newly increased column; according to a data insertion request of the application side, a data node receives data of a schema version carrying a data table from the application side and writes the received data, wherein writing the received data comprises: storing the schema version of the data table carried by data from the application side by the data node, and writing the received data, and writing the received data; when the data node receives new data from the application side, comparing the schema version of the data table carried by the new data with a stored schema version of the same data table; and the data node writes data according to the comparison result. According to the method, the operation of changing the structure of a table can be completed instantly, the historical data does not need to be changed, new and old table structure definitions can be used in parallel, and the flexibility can be improved.