Database View Query Restriction Using Data Aging Partitions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional approaches to accessing data from databases with data aging functionality rely on user input to determine whether historical data is considered, leading to inefficient queries that result in unnecessary data load and processing costs due to the user's lack of knowledge about data aging logic, causing performance penalties and incomplete data retrieval.
Innovation Solution
Implement a system that automatically determines the need for accessing historical data by leveraging data aging functionality, transferring historical data from fast in-memory storage to slower media and using application-specific aging logic to restrict access to historical data during retrieval, ensuring optimal data access by leveraging business logic to set appropriate query restrictions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If user specifies whether historical data is considered in data queries, then user control over data retrieval is improved, but query efficiency deteriorates due to unnecessary data load and processing
Solution Approach 1:
The system performs self-service by automatically determining whether historical data should be considered in queries. The database system itself analyzes the query parameters and data aging information to make the decision, eliminating the need for user input while optimizing query performance through intelligent data selection.
Solution Approach 2:
The system uses feedback from data aging information and query parameters to dynamically adjust data retrieval strategies. By monitoring the aging date of data and comparing it with query requirements, the system provides feedback-driven optimization that prevents unnecessary historical data loading while ensuring complete result sets.
2Speed
If all data is stored in fast in-memory storage, then data access speed is improved, but storage cost and memory footprint increase
Solution Approach 1:
The database is segmented into different storage levels based on data aging. Current data (recent aging dates) is stored in fast in-memory storage for quick access, while historical data (older aging dates) is stored in slower, cheaper storage media. This segmentation allows the system to optimize both speed and storage cost by placing each data type in the appropriate storage medium.
Solution Approach 2:
Different storage qualities are applied to different data based on their aging characteristics. Recent data receives high-quality fast storage, while historical data is placed in lower-quality slower storage. This local quality differentiation ensures that fast storage is used only where necessary for current operations, reducing overall memory footprint while maintaining access speed for relevant data.
3Loss of information
If historical data is accessed without restrictions, then data completeness is improved, but processing time and costs increase
Solution Approach 1:
The system performs preliminary actions by pre-calculating and storing data aging information (aging dates) for all data records. When a query is received, the system uses this pre-computed aging information to quickly determine which historical data partitions need to be accessed, avoiding the need to scan or load unnecessary historical data while ensuring all relevant data is retrieved for complete results.
4Quantity of substance
If data is segregated by aging date into multiple partitions, then storage optimization is improved, but query complexity increases
Solution Approach 1:
The system introduces an intermediary mechanism (the data aging information and automatic determination logic) that simplifies queries across partitioned data. Instead of requiring users to understand or manually manage the complexity of multiple partitions, the intermediary automatically determines which partitions are relevant based on aging dates, transparently handling the complexity while maintaining storage optimization benefits.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A database includes a plurality of partitions with respective partition dates. A query is received for data from a view of the database, the query containing a filtering parameter for retrieval of the data. A definition of the view is analyzed to determine a table of the database that contributes to the view and an aging object associated with the table. A restriction rule associated with the aging object is identified, for example, based on an annotation in the definition of the view. A restriction date for the aging object is determined based on the restriction rule. A partition, from the plurality of partitions, is selected based on the partition date of the partition being equal to or later than the restriction date. The queried data is retrieved from the selected partition according to the filtering parameter and a response to the query is generated based on the retrieved data.