Database Replication Storage Format Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database replication systems face challenges in managing primary and replica databases using different storage formats, as they either require choosing between row store (RS) and column store (CS) formats, which are suitable for different query workloads, or incur increased storage costs by using both formats simultaneously.
Innovation Solution
Implementing a method that stores data in a sequence of rows in a first storage format at the primary database and in a second storage format at the replica database, allowing for the use of both RS and CS formats separately for each, with synchronization and alignment of sequence numbers to maintain data consistency and efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If both row store and column store formats are used for primary and replica databases, then query performance for mixed workloads is improved, but storage cost increases
Solution Approach 1:
The system segments the storage format requirement by separating the primary database (using row store format for point queries) from the replica database (using column store format for analytical queries). This allows each database to use the optimal format for its specific workload without requiring both formats simultaneously, thereby reducing storage cost while maintaining query performance.
Solution Approach 2:
Different storage formats are applied locally to different databases based on their specific workload requirements. The primary database uses row store format optimized for point queries, while the replica database uses column store format optimized for analytical queries. This local optimization allows each system to achieve best performance without the need for dual-format storage everywhere.
2Quantity of substance
If the same storage format is used for primary and replica databases, then storage cost is reduced, but query performance for mixed workloads deteriorates
Solution Approach 1:
The system segments the storage format assignment by allowing the primary and replica databases to use different formats optimized for their respective workloads. This segmentation enables cost reduction (by not using both formats simultaneously) while maintaining performance (by using the right format for the right workload).
Solution Approach 2:
The system changes the storage format parameter independently for primary and replica databases. Instead of constraining both to use the same format, the system allows the primary to use row store format and the replica to use column store format, optimizing each for its specific query patterns while reducing overall storage cost.
3Quantity of substance
If different storage formats are used for primary and replica databases, then storage cost is reduced, but data synchronization complexity increases
Solution Approach 1:
The database management system acts as an intermediary that handles the complexity of synchronization between databases with different storage formats. The DBMS manages the translation and coordination between row store and column store formats during replication, abstracting the complexity from the user and enabling cost reduction without requiring manual intervention to manage synchronization issues.
Data Source
AI summary
System and method embodiments are provided for using different storage formats for a primary database and its replicas in a database managed replication (DMR) system. As such, the advantages of both formats can be combined with suitable design complexity and implementation. In an embodiment, data is arranged in a sequence of rows and stored in a first storage format at the primary database. The data arranged in the sequence of rows is also stored in a second storage format at the replica database. The sequence of rows is determined according to the first storage format or the second storage format. The first storage format is a row store (RS) and the second storage format is a column store (CS), or vice versa. In an embodiment, the sequence of rows is determined to improve compression efficiency at the CS.