Repartitioning-Based Database Sampling for Memory-Constrained Aggregation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Executing aggregation queries on large database tables is resource intensive and time consuming due to the need to process the entire dataset, which is often too large to be held in memory at once.
Innovation Solution
A reapportioning-based sampling system that generates a database synopsis small enough to be held in memory, using a partition function to select a representative sample that provides approximate answers to aggregation queries within prescribed error bounds, by strategically partitioning and sampling the data based on query workloads and error thresholds.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If the entire database table is processed to execute aggregation queries, then query accuracy is improved, but resource consumption and execution time increase significantly
Solution Approach 1:
The database table is divided into multiple partitions based on query workload analysis. Each partition is processed separately to generate partial results, which are then aggregated to form the final query result. This segmentation allows the system to handle large datasets efficiently by processing smaller chunks in parallel, reducing both resource consumption and execution time while maintaining query accuracy.
2Speed
If the entire database table is loaded into memory, then query execution speed is improved, but memory consumption increases beyond available resources
Solution Approach 1:
The database table is divided into multiple partitions that can be loaded into memory incrementally. Each partition is processed separately, with results aggregated to form the final query result. This allows the system to execute queries efficiently using only the memory required for individual partitions rather than the entire dataset, making it feasible to process large datasets that exceed available memory resources.
3Quantity of substance
If a simple random sample is used to create a synopsis, then memory usage is reduced, but query accuracy deteriorates
Solution Approach 1:
The sampling process is made adaptive to local data characteristics and query workloads. Different partitions are sampled with different rates based on their importance to the query workload, as determined by historical query analysis. This ensures that partitions containing frequently queried data are represented more accurately in the synopsis, maintaining query accuracy while minimizing overall synopsis size.
Solution Approach 2:
The system performs preliminary analysis of the query workload before creating the synopsis. By analyzing historical query patterns and identifying which partitions are most relevant to typical queries, the system can pre-determine the optimal sampling strategy for each partition. This preliminary action ensures that the synopsis is optimized for the actual query workload, improving accuracy without increasing size.
Data Source
AI summary
The present invention provides a re-partitioning-based sampling system and method which provides for generating a synopsis from large database tables such that an aggregation query performed on the synopsis provides an approximate answer to the aggregation query which is in prescribed error bounds relative to a query on the full database. The system includes a partition function generator, a synopsis vector calculator, and a synopsis constructor. The synopsis constructed by the system is sufficiently small to be held in memory to allow quick and resource efficient satisficing of aggregation queries.