Real-time Analytical Database Using Change Logs for Transactional Data
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional systems are inefficient in executing analytics queries on transactional data stores, leading to slow generation of analytics reports and adverse impact on transactional operations, as they require frequent data transfer and expensive hardware for processing.
Innovation Solution
A real-time analytical database system that receives change logs from a transactional system, updates indexes, and processes queries efficiently by using key-value pairs to store and retrieve data, allowing for fast analytics query execution without impacting transactional operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If data is transferred from transactional data store to analytics data store using ETL operation, then analytics queries can be executed efficiently, but the data freshness is lost and analytics reports cannot present the latest data
Solution Approach 1:
The patent introduces a change log as an intermediary mechanism that captures data modifications in the transactional data store and transmits them to the analytics data store. This mediator enables real-time data synchronization without requiring full ETL operations, thus maintaining data freshness while preserving analytics query performance.
Solution Approach 2:
The system performs preliminary actions by pre-processing and capturing change logs at the source transactional data store before analytics queries are executed. This preliminary capture of data modifications allows the analytics data store to receive only the necessary updates, enabling fast query execution on fresh data without waiting for periodic ETL operations.
2Speed
If the same system is used for both transactional operations and analytical queries with faster hardware, then query processing speed is improved, but the database size is limited by memory capacity
Solution Approach 1:
The patent segments the system into two distinct components: a transactional data store for handling operational data and an analytics data store for processing analytical queries. This segmentation allows each component to be optimized independently, enabling the analytics system to handle large-scale queries without being constrained by the memory limitations of the transactional system.
Solution Approach 2:
The change log acts as an intermediary that bridges the transactional and analytics systems, allowing the analytics data store to receive incremental updates without requiring the entire dataset to be loaded into memory. This enables processing of large databases that exceed available memory capacity.
3Measurement precision
If analytics queries are executed on the transactional data store, then the latest data is available, but the performance of transactional operations is adversely affected
Solution Approach 1:
The patent extracts the analytics processing function from the transactional data store by creating a separate analytics data store. This extraction removes the harmful factor of analytics query execution from the transactional system, allowing transactional operations to maintain high performance while the analytics system independently accesses fresh data through change logs.
Solution Approach 2:
The change log serves as an intermediary that provides the analytics data store with access to the latest data from the transactional system without requiring direct querying of the transactional store. This mediator mechanism maintains data currency for analytics while preserving transactional operation performance.
Data Source
AI summary
A real-time analytical database system allows queries on data obtained from a transactional system that is frequently updated by the transactional system. The real-time analytical database system receives change logs from the transactional system. The change logs describe operations performed by the transactional system. The real-time analytical database system stores a plurality of indexes in a key-value store, each index comprising key-value pairs. A key of a key-value pair is obtained by combining field data describing a field of a document. The real-time analytical database system determines modifications to the key-value pairs of the key-value store corresponding to the operations of the transactional system as represented by the received change logs. The real-time analytical database system receives and processes queries using the plurality of indexes. The real-time analytical database system builds key-expressions based on the criteria of queries and uses the indexes to find key-value pairs matching the key-expression.