Role-Based Index Optimization for Database Replication
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database replication methods face challenges in reducing unnecessary indexes on master and subscriber database nodes, leading to increased load, memory consumption, and complex switch-over processes, while also transmitting redundant logs and maintaining different schemas.
Innovation Solution
The method involves associating indexes with role information, allowing the master database node to check and update indexes based on role matching, and synchronizing only relevant indexes between nodes, thereby reducing unnecessary indexes and logs, and maintaining schema consistency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If all indexes are maintained on both master and subscriber database nodes, then query performance is improved, but memory consumption and system load increase
Solution Approach 1:
The patent applies local quality by making indexes role-specific rather than universal. Each index is associated with particular roles (master node, subscriber node, or both), allowing the system to optimize memory usage by maintaining only the necessary indexes at each node while preserving query performance for role-appropriate operations
2Reliability
If redundant indexes are maintained on subscriber database nodes, then replication consistency is improved, but unnecessary logs are transmitted increasing network load
Solution Approach 1:
The patent extracts only the necessary index information for replication by identifying which indexes are role-specific to subscriber nodes. The log writer process selectively generates logs only for indexes that need to be replicated, eliminating redundant log transmission while maintaining replication consistency for relevant data
3Productivity
If schema differences are allowed between master and subscriber nodes, then index optimization is improved, but switch-over complexity increases
Solution Approach 1:
The patent introduces dynamic role-based index management where indexes are associated with roles rather than fixed node types. This allows the system to adaptively determine which indexes to maintain based on current role assignments, optimizing index performance while maintaining schema compatibility for switch-over through role-based consistency
4Productivity
If role-based index filtering is implemented, then unnecessary operations on master node are reduced, but index management complexity increases
Solution Approach 1:
The patent implements self-service through automated role-based index management. The system automatically associates indexes with roles, filters logs based on role matching, and manages index maintenance without requiring manual administrative intervention, thereby reducing unnecessary operations while keeping management complexity minimal through automation
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A method for optimizing index, a master database node and a subscriber database node. The method for optimizing index includes: checking, by a master database node, if role information of an index matches role information of the master database node, when the index is added into the master database node; updating database information by using the index when the role information of the index matches the role information of the master database node. Therefore, unnecessary indexes in the master database node and the subscriber database node are reduced while keeping the schema the same. Furthermore, unnecessary logs transmitted from the master database node to the subscriber database node are reduced.