Aggregation View Cache for Partitioned Database Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Persistently storing aggregates of historical data in database applications is inefficient, requiring complex logic and reducing flexibility, while computing them on the fly can lead to repeated loading of historical partitions from disk, causing resource inefficiency.
Innovation Solution
Implementing a caching system that generates and stores results of aggregation views on partitioned tables, associating transaction identifiers to cache entries, and invalidating cache entries when partitions are updated, allowing for efficient retrieval and regeneration of query results.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If aggregates of historical data are persistently stored in the database, then query performance is improved, but memory efficiency deteriorates and implementation complexity increases
Solution Approach 1:
The system pre-computes aggregation views when data is inserted or updated in partitioned tables, and stores these pre-computed results in a cache. This preliminary action eliminates the need for repeated aggregation computations during query execution, improving query performance while avoiding the need to persistently store all historical aggregate data in memory.
Solution Approach 2:
The patent introduces an aggregation view cache as an intermediary layer between the partitioned table data and the query interface. This cache stores pre-computed aggregation results temporarily, mediating between the raw historical data and query requirements, thereby improving query speed without requiring permanent storage of aggregate data in the database.
2Loss of energy
If aggregates of historical data are computed on the fly, then memory efficiency is improved, but resource efficiency deteriorates due to repeated data loading
Solution Approach 1:
Instead of computing aggregates on the fly during queries, the system performs the aggregation computation in advance when data is inserted or updated. The pre-computed aggregation views are stored in a cache, eliminating repeated data loading and aggregation computations during query execution, thereby improving resource efficiency while maintaining memory efficiency.
Solution Approach 2:
The system maintains continuous aggregation computation by automatically updating the aggregation view cache whenever data is inserted or updated in the partitioned table. This continuous maintenance of aggregation views ensures that the cache remains valid and useful, avoiding the need to recompute aggregates from scratch during queries.
3Speed
If aggregation views are cached, then query performance is improved, but cache invalidation complexity increases
Solution Approach 1:
The system implements a feedback mechanism where the database engine automatically detects data modifications in partitioned tables and invalidates corresponding aggregation view cache entries. This feedback loop ensures cache consistency without requiring complex manual invalidation logic, as the system automatically responds to data changes by updating or invalidating relevant cache entries.
Solution Approach 2:
The aggregation view cache system is self-managing in terms of invalidation. When data is inserted or updated in partitioned tables, the system automatically invalidates the affected aggregation view cache entries without requiring external intervention or complex invalidation logic. The cache invalidation process is embedded in the data modification workflow, making the system self-service oriented.
4Measurement precision
If historical partitions are loaded from disk for each query, then data accuracy is maintained, but resource efficiency deteriorates
Solution Approach 1:
The system pre-loads and pre-computes aggregation views from historical partitions when data is inserted or updated, storing these pre-computed results in a cache. This preliminary action eliminates the need to load historical partitions from disk for each query, maintaining data accuracy while significantly improving resource efficiency by avoiding repeated disk I/O operations.
Data Source
AI summary
In a general aspect, a computer-implemented method for executing a query on a table of a database, where the table has multiple partitions, can include receiving a query requesting a view on the table. The view on the table can be based on data included in a partition of the multiple partitions of the table. The method can also include determining a cached result to the query is not available in the database and generating a result to the query from, at least, the data of the partition of the multiple partitions. After the generating, the method can include building a cache including the result to the query, associating a transaction identifier for the query with the result to the query result in the cache; and returning the result to the query.