Combined OLTP and Warehouse Database Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face challenges in balancing frequent updates in Online Transaction Processing (OLTP) systems with the need for efficient querying in data warehouses, often leading to substantial interruptions due to locking mechanisms.
Innovation Solution
A database system that includes an OLTP unit and a warehouse database with a short-term and long-term area, where data with high update rates is duplicated in both systems, and data with low update rates is moved from the OLTP to the long-term area, allowing for efficient updates and querying without frequent locking.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of information
If data is frequently updated in the data warehouse to maintain real-time information, then the data freshness is improved, but the service interruptions increase due to locking mechanisms
Solution Approach 1:
The patent segments the database system into two distinct parts: an OLTP database for handling frequent updates and a data warehouse for complex queries. This segmentation allows each component to operate independently with its own update frequency, eliminating the need to lock the entire data warehouse during updates.
Solution Approach 2:
The patent introduces an intermediary mechanism (application server or ETL process) that periodically transfers data from the OLTP database to the data warehouse. This intermediary layer decouples the update operations from the query operations, allowing the data warehouse to remain locked-free while still receiving updated information.
2Reliability
If the data warehouse is locked for updates to prevent errors, then data consistency is improved, but the service availability deteriorates
Solution Approach 1:
The patent implements dynamic data replication where the data warehouse is periodically updated from the OLTP database without requiring locks. The system adapts to different update frequencies and allows the data warehouse to serve queries continuously while background processes handle data synchronization.
Solution Approach 2:
The system performs preliminary data preparation and validation in the OLTP database before transferring to the data warehouse. This preliminary action ensures data consistency is maintained during the transfer process without requiring locks on the data warehouse during query operations.
3Speed
If all data is stored in the data warehouse for efficient querying, then query performance is improved, but the update frequency requirement increases
Solution Approach 1:
The patent applies local quality by storing different types of data in different locations based on their access patterns. Frequently updated data remains in the OLTP database, while data suitable for analysis is replicated to the data warehouse. This allows queries to access optimized data in the warehouse without requiring all data to be updated at the same frequency.
Data Source
AI summary
A system, including both an OLTP database and a warehouse database, that is configured to manage the coordination between the OLTP database and the warehouse database. An aspect relates to a combined OLTP and warehouse database which manages duplicate copies, in both the OLTP database and the warehouse database, of data expected to have a relatively high update and query rate. Data which has a low update and query rate is removed from the OLTP database, such that it remains only in the warehouse database. Updates of the duplicate copy data are performed in the OLTP. Periodically, for example every several minutes, data recently changed in the OLTP database is copied to the warehouse database. Queries are optionally primarily handled by the warehouse database, and are supplemented, when necessary, by data from the OLTP database.