Hybrid Transactional Analytical Processing Data Synchronization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face challenges in efficiently configuring data storage and processing to handle both transactional and analytical workloads, leading to increased complexity and costs, particularly due to the impact of running analytical queries on transactional data and the difficulty in scaling transactional data stores for analytical queries.
Innovation Solution
A hybrid transactional and analytical processing system is implemented, which includes a transactional database and an analytical database, using a change-data-capture log to replicate transactional data and apply incremental changes, allowing for real-time analytics while minimizing data lag between the transactional database and the analytical database.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Device complexity
If a single database system is used to handle both transactional and analytical workloads, then device complexity is reduced, but processing efficiency deteriorates due to resource contention and workload interference
Solution Approach 1:
The patent divides the database system into two separate specialized databases: a transactional database for handling OLTP workloads and an analytical database for handling OLAP workloads. This segmentation allows each database to be optimized for its specific workload type, preventing resource contention and improving overall processing efficiency while maintaining manageable complexity through clear functional separation.
Solution Approach 2:
The system implements a unified architecture that provides both transactional and analytical processing capabilities through a coordinated multi-database system. The transactional and analytical databases work together with automated data synchronization, creating a universal system that handles diverse workload types efficiently without requiring separate independent systems.
2Adaptability or versatility
If transactional data is scaled up to support analytical queries, then analytical processing capability is improved, but transactional database performance deteriorates due to increased resource consumption
Solution Approach 1:
The patent extracts analytical processing requirements from the transactional database by implementing a separate analytical database. This extraction removes the burden of analytical queries from the transactional system, allowing it to maintain high performance and reliability for transactional workloads while the analytical database provides the necessary analytical capabilities.
Solution Approach 2:
The system introduces a data synchronization mechanism as an intermediary between the transactional and analytical databases. This intermediary automatically replicates and synchronizes data between the two databases, ensuring that the analytical database has access to up-to-date transactional data without impacting the performance or reliability of the transactional database.
3Productivity
If separate transactional and analytical databases are implemented, then processing efficiency is improved, but device complexity increases due to data synchronization requirements
Solution Approach 1:
The patent implements a self-service data synchronization system where the analytical database automatically receives and applies data changes from the transactional database without requiring manual intervention. The synchronization mechanism operates autonomously, monitoring and replicating data changes, which reduces the operational complexity despite the presence of multiple databases.
Solution Approach 2:
The system performs preliminary data synchronization setup and configuration automatically during system initialization. The data replication and synchronization mechanisms are pre-configured to work seamlessly between the transactional and analytical databases, reducing the complexity of ongoing data management operations.
4Loss of time
If analytical queries are run on transactional data, then real-time analytics capability is improved, but transactional database resources are consumed, increasing operational costs
Solution Approach 1:
The patent extracts analytical query processing from the transactional database by routing analytical queries to a dedicated analytical database. This extraction ensures that analytical queries do not consume transactional database resources, eliminating the trade-off between real-time analytics capability and resource consumption costs.
Data Source
AI summary
Methods for implementing insert, update, and delete events of a change-data-capture log in transactional order to a representation of a transactional table are disclosed. Insert events may be applied and committed to the representation, while delete events may be buffered and committed to a shadow table. Such buffering may allow the delete events to be applied and committed as a batch to the given representation as a later background operation, allowing for an optimized application of the change-data-capture log to the representation. Once the delete events are committed to the representation, they are removed from the shadow table. Update events may be similarly treated in which the insert event component of the update event is applied and committed to the representation while the delete event component is committed to the shadow table, before being later committed to the representation and removed from the shadow table.