Streaming Database Pre-Aggregation for Faster Analytical Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing systems face challenges in efficiently executing analytical queries on large volumes of streaming data due to high computational intensity and slow query execution, leading to expensive storage requirements and prolonged processing times.
Innovation Solution
A database system that generates a pre-aggregation table based on periodic data ingestion and completion queries to efficiently process rollup queries, splitting computations into ingest and completion phases to reduce redundant processing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If analytical queries process all data received during a significant time interval, then query results are accurate and complete, but computation time and processing resources increase dramatically
Solution Approach 1:
The system performs preliminary aggregation operations continuously in the background as data streams in, maintaining pre-computed aggregate results in memory. When a query is submitted, the system retrieves and adjusts these pre-computed results rather than calculating from scratch, dramatically reducing query execution time while maintaining accuracy.
Solution Approach 2:
The system divides the large dataset into manageable time-based partitions and maintains separate aggregate structures for each partition. This allows queries to be answered by combining results from relevant partitions rather than processing the entire dataset, reducing computation time while preserving result accuracy.
2Speed
If the system stores very large amount of data in storage system with fast access, then query execution is faster, but system cost increases
Solution Approach 1:
The system extracts frequently accessed aggregate data from persistent storage and maintains it in volatile memory (cache). This extraction of hot data to faster storage reduces query execution time without requiring the entire dataset to be stored in fast storage, optimizing the balance between speed and storage cost.
Solution Approach 2:
The system dynamically adjusts the amount of data kept in cache based on query patterns and available memory. By changing the parameter of cache size adaptively, the system optimizes query performance while avoiding unnecessary high-cost storage allocation for data that isn't frequently accessed.
3Reliability
If the system processes large amount of data for analytical queries, then query results are comprehensive, but computational resources and processing time increase significantly
Solution Approach 1:
The system pre-computes aggregate statistics (sums, counts, averages) as data arrives and stores these intermediate results. When a comprehensive query is executed, the system leverages these pre-computed values to quickly assemble complete results without re-processing the entire raw dataset, reducing computational resource usage while maintaining result completeness.
Data Source
AI summary
A database system receives a database language query specifying a grouping clause and one or more aggregate result values. The database system generates a pre-aggregation table based on the query. The database system generates an ingest query configured to generate data for populating the pre-aggregation table and a completion query configured to processes data stored in the pre-aggregation table. The database system periodically receives a set of input records and executes the ingest query to generate a set of pre-aggregation records. The set of pre-aggregation records are combined with the pre-aggregation table. The database system executes the completion query on the data stored in the pre-aggregation table to generate a result set and sends the generated result set to the requestor of the results.