Materialized View Replication With Row-Level Entitlement Filtering
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Replicating entire databases across a networked environment is costly and induces significant latency due to the size of the databases, and existing methods do not allow users to selectively replicate only relevant portions.
Innovation Solution
Implementing sub-database replication through schema and table filtering, data sharing, and row-level filtering to replicate only necessary data to different regions, using a replication controller that applies user-defined inclusion and exclusion lists, entitlements tables, and materialized views.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If entire databases are replicated across networked environments, then data availability and reliability are improved, but replication cost and latency increase significantly
Solution Approach 1:
The patent divides the database into multiple selectable data portions (tables, views, or individual rows) that can be independently replicated. The replication controller receives a request specifying which portions to replicate and selectively copies only those portions to the target database instance, rather than replicating the entire database. This segmentation approach reduces the volume of data transferred and stored, thereby lowering replication costs while maintaining data availability for the specific portions that are actually needed.
2Reliability
If entire databases are replicated across networked environments, then data availability is improved, but replication latency increases significantly
Solution Approach 1:
By segmenting the database into selectable portions and only replicating the necessary portions, the patent reduces the total data volume that must be transferred across the network. This directly decreases the time required for replication operations, thereby reducing replication latency while still ensuring data availability for the specific segments that are replicated.
Solution Approach 2:
The patent implements partial replication by replicating only the necessary portions of the database rather than the entire database. The replication controller determines which portions (tables, views, or rows) need to be replicated based on user specifications or predefined policies, and only those portions are copied to the target instance. This partial action approach eliminates unnecessary data transfer and processing, significantly reducing replication time and latency.
3Loss of energy
If selective replication of only necessary data is implemented, then replication cost and latency are reduced, but system complexity increases
Solution Approach 1:
The patent introduces a replication controller as an intermediary component that manages the selective replication process. The controller receives replication requests, determines which data portions need to be replicated based on user specifications or predefined policies, and coordinates the actual replication operation. This intermediary approach abstracts the complexity from the user and simplifies the overall system architecture, as the replication controller handles the decision-making and coordination tasks centrally.
Solution Approach 2:
The patent implements self-service replication by allowing users to specify which data portions they want replicated through simple requests, while the replication controller automatically determines the replication details and executes the process. Users can define replication criteria (such as specific tables, views, or row conditions) without needing to understand the underlying replication mechanics. The controller autonomously manages the replication process, including determining source and target locations, timing, and data transfer, thereby reducing the operational complexity for users while maintaining cost efficiency.
Data Source
AI summary
A system for sub-table replication within a materialized view of a source data table. A materialized view of a source data table is generated based on a query, the source data table having a plurality of data table rows associated with a plurality of client identifiers and including an entitlements column and data content, the entitlements column including a plurality of provider account identifiers and a plurality of consumer account identifiers. An update to an entitlements table of the source data table is detected where the entitlements table includes a plurality of entitlements table rows and where the entitlements table rows are associated with the client identifiers, the consumer account identifiers, and the provider account identifiers. An update to the source data table based on the first update to the entitlements table is detected, and an incremental replication of the materialized view of the source data table is performed.