Database Temporal History via Snapshots and Deltas
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems require significant computing resources and time to access the state of a record at a particular moment in time, making it difficult for end-users to view historical data, as they often need to load and scan transaction logs, which is typically only available to system administrators.
Innovation Solution
A system that maintains accessible temporal history by creating snapshots of the database and storing deltas, allowing users to select a record instance at a specific point in time by reading from a snapshot and applying relevant deltas, along with optional metadata storage for contextual information.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of information
If transaction logs are loaded and scanned to view historical record state, then temporal information becomes accessible, but computing resources and time are significantly consumed
Solution Approach 1:
The system performs preliminary actions by creating snapshots of the database at specific points in time and pre-computing delta values that represent changes between snapshots. When a user requests historical data, the system retrieves the relevant snapshot and applies pre-computed deltas rather than scanning entire transaction logs, dramatically reducing retrieval time and computational resources.
2Loss of information
If transaction logs are loaded and scanned to view historical record state, then temporal information becomes accessible, but the process is complex and typically only available to system administrators
Solution Approach 1:
The system creates simplified copies of the database state through snapshots that can be easily retrieved and manipulated. Instead of requiring users to work with complex transaction logs, the system provides read-only snapshot copies that can be directly queried, making temporal data access as easy as querying current data while maintaining data integrity through the snapshot-delta mechanism.
3Loss of information
If snapshots are created periodically, then temporal history becomes more accessible, but storage requirements increase
Solution Approach 1:
The system segments the storage of historical data by dividing it into snapshots at different time points and associated delta values. Instead of storing complete database states at all times, the system stores incremental changes (deltas) between snapshots, significantly reducing storage requirements while maintaining the ability to reconstruct any historical state by applying the appropriate sequence of deltas to the base snapshot.
Data Source
AI summary
Certain aspects of the present disclosure provide techniques for updating database records while maintaining accessible temporal history. One example method generally includes receiving a request, at a database, to select an instance of a record from the database at a specific point in time and reading the instance of the record from a snapshot of the database. The method further includes loading one or more deltas associated with the record from the database and chronologically applying the one or more deltas to the instance of the record to create the instance of the record. The method further includes returning the instance of the record, determining that the request has made a percentage of recent requests exceed a threshold for requests for most-current data and creating a new snapshot of the database.