Fine-Grained Client-Side Cache Invalidation via Partition Tracking
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Maintaining the correctness and validity of query results in client-side caches within database systems is challenging due to the coarse-grained invalidation methods used, which can lead to unnecessary invalidation of cached data, especially when only a small portion of a large table is modified.
Innovation Solution
Implementing a fine-grained invalidation method by registering queries and objects based on partition and sub-partition IDs, columns, and bind variables, allowing for precise identification and invalidation of affected data in client-side caches, rather than relying on table-based tracking.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If coarse-grained table-based invalidation is used, then implementation simplicity is maintained, but cache invalidation precision deteriorates leading to unnecessary invalidations
Solution Approach 1:
The patent segments the invalidation tracking from table-level to partition-level and column-level granularity. Instead of invalidating all cached queries for a modified table, the system divides the table into partitions and further segments tracking by specific columns, allowing only the affected partitions and columns to trigger invalidations. This segmentation resolves the contradiction by maintaining implementation feasibility through structured division while dramatically improving invalidation precision.
Solution Approach 2:
The patent applies local quality by making different parts of the cache invalidation system have different granularities. The registration table stores partition IDs and column names for each cached query, enabling the system to apply fine-grained invalidation only where needed (specific partitions and columns) while keeping other parts of the cache valid. This local differentiation resolves the contradiction between simplicity and precision by applying complexity only where necessary.
2Measurement precision
If fine-grained invalidation tracking is implemented, then cache invalidation precision is improved, but system complexity increases
Solution Approach 1:
The patent segments the complex tracking task into manageable components: partition ID tracking, column name tracking, and bind variable tracking. The registration table is divided into specific columns (partition_id, column_name, bind_variable) that can be independently maintained. This segmentation reduces system complexity by breaking down the fine-grained tracking problem into structured, manageable pieces that can be handled through standard database operations.
Solution Approach 2:
The patent introduces a registration table as an intermediary structure between the cache and the invalidation mechanism. This registration table stores the mapping between cached queries and their dependent partitions, columns, and bind variables. The intermediary absorbs the complexity of fine-grained tracking, allowing the rest of the system to operate with simpler logic while still achieving precise invalidation through the registration table's structured data.
3Loss of energy
If table-based tracking is used, then overhead is reduced, but data validity maintenance deteriorates due to unnecessary invalidations
Solution Approach 1:
The patent segments the invalidation scope from entire tables to specific partitions and columns. When a DML operation modifies data, the system uses the registration table to identify only the specific partitions and columns affected, then invalidates only the corresponding cached queries. This segmentation dramatically reduces invalidation overhead by avoiding unnecessary invalidations while maintaining data validity through precise tracking of affected data regions.
Solution Approach 2:
The patent changes the parameters of invalidation tracking from coarse-grained (table-level) to fine-grained (partition ID, column_name, bind_variable). By storing and comparing these detailed parameters in the registration table, the system can make more accurate decisions about which cached data to invalidate. This parameter refinement reduces unnecessary invalidation overhead while ensuring data validity is maintained through precise matching of affected data characteristics.
Data Source
AI summary
An approach for implementing function semantic based partition-wise SQL execution and partition pruning in a data processing system is provided. The system receives a query directed to a range-partitioned table and determines if operation key(s) of the query include function(s) over the table partitioning key(s). If so, the system obtains a set of values corresponding to each partition by evaluating the function(s) on a low bound and/or a high bound table partitioning key value corresponding to the partition. The system may then compare the sets of values corresponding to different partitions and determine whether to aggregate results obtained by executing the query over the partitions based on the comparison. The system may also determine whether to prune any partitions from processing based on a set of correlations between the set of values for each partition and predicate(s) of the query including function(s) over the table partitioning key(s).