Database Replication with Time-Travel Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current data replication methods in digital computer systems are inefficient, particularly when synchronizing data between source and target database systems with differing processing resources, as they often require additional storage, CPU, and memory resources to support temporal queries and history tables, and may struggle with latency issues.
Innovation Solution
A method that creates a target table and a history table at the target database system with time attributes to manage changes from the source table, allowing for efficient data synchronization and offloading time-based queries, thereby reducing the resource burden on the source system and enabling seamless time-travel queries without additional storage or processing overhead.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If data replication is performed with temporal query support, then time-travel query capability is improved, but storage resources and processing overhead increase
Solution Approach 1:
The patent segments the target database system into two distinct storage components: a current version table holding only the latest data state, and a history table storing only the changes (insertions, updates, deletions) over time. This segmentation allows the system to support time-travel queries without duplicating entire historical states, significantly reducing storage requirements while maintaining full temporal query capability.
Solution Approach 2:
The patent implements a nested structure where the history table contains change records that reference the current version table. Each history record stores only the delta (change) rather than the complete state, allowing historical reconstructions by applying changes sequentially to the current state. This nested approach enables efficient time-travel queries without storing complete historical snapshots.
2Adaptability or versatility
If history tables are maintained for temporal queries, then time-travel query capability is improved, but CPU and processing resources increase
Solution Approach 1:
Instead of storing complete historical states and filtering them during queries, the patent inverts the approach by storing only changes in the history table and applying them on-demand during time-travel queries. This inversion reduces both storage requirements and processing overhead, as the system only processes the minimal necessary changes to reconstruct historical states rather than scanning through complete historical snapshots.
Solution Approach 2:
The patent changes the parameter representation from storing complete data states to storing only change deltas. Each history record contains minimal information (change type, timestamp, affected columns) rather than full row data, significantly reducing the processing required for temporal queries while maintaining the ability to reconstruct any historical state.
3Reliability
If data synchronization is performed between distributed systems, then data consistency is improved, but latency increases
Solution Approach 1:
The patent implements asynchronous data replication where changes are copied from the source database to the target database without requiring synchronous acknowledgment. The target system receives and applies changes independently, allowing the source system to continue operating without waiting for synchronization confirmation. This copying approach maintains data consistency over time while minimizing synchronization latency and preserving source system performance.
Data Source
AI summary
Creating at a target database system, a target table and a history table each having a set of attributes of a source table in addition to at least one time attribute, wherein the time attribute of a record indicates the time when the record is valid. Receiving a request for applying a change of the source table to the target table. The request comprises a time value generated by the source database system indicating a time of the change. The change to the target table and the time value may be assigned to the time attribute of the current version and previous version of the record. The previous version of the record may be stored in the history table. The target table and the history table may be used to process a redirected query comprising a selection criterion on the time attribute.