Database Replication Using Mixed Row and Column Storage Formats
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 without increasing storage costs, by compressing columns into compression units (CUs) and inserting them into heap files, while maintaining sequence alignment between formats.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If both row store and column store formats are used for primary and replica databases, then query workload adaptability is improved, but storage cost increases
Solution Approach 1:
The patent applies copying by creating a replica database that mirrors the primary database structure and data. The replica is initialized by copying data from the primary database, and subsequent changes are replicated through log-based mechanisms. This copying approach enables the system to maintain consistent data across multiple nodes while supporting different storage formats (row store for primary, column store for replica) to optimize for different query workloads without requiring separate storage systems.
Solution Approach 2:
The patent segments the database into distinct components: a primary database using row store format for transactional operations and a replica database using column store format for analytical queries. The segmentation is further divided into data segments that can be independently managed, with log-based replication ensuring consistency. This segmentation allows each database to be optimized for its specific workload while maintaining overall system adaptability.
2Quantity of substance
If the same storage format is used for primary and replica databases, then storage cost is reduced, but query workload flexibility is limited
Solution Approach 1:
The patent applies local quality by allowing different storage formats at different locations (primary vs. replica database) based on their specific functional requirements. The primary database uses row store format optimized for point queries and transactions, while the replica uses column store format optimized for analytical queries. This local differentiation enables each component to have the optimal storage format for its workload while maintaining overall system consistency through log-based replication.
3Productivity
If data is stored in row store format at primary and column store format at replica, then query optimization is improved, but data synchronization complexity increases
Solution Approach 1:
The patent introduces an intermediary mechanism in the form of a log-based replication system that mediates between the primary database (row store) and replica database (column store). The log captures changes from the primary database and translates them into appropriate operations for the replica, handling the complexity of format conversion and data synchronization automatically. This intermediary layer abstracts the complexity from the user while enabling optimized storage formats at both locations.
Data Source
Figure 1A~1C
Figure 2
Figure 3~4
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.