Projection Datasets for Low-Latency Queries in Big Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face challenges in balancing low-latency complex querying and low-latency editing, particularly when dealing with large volumes of data, as they often require trade-offs between query performance and write performance, and the use of versioned datasets increases complexity and storage requirements.
Innovation Solution
A database system with a decoupled compute and storage layer, utilizing a canonical dataset and multiple projection datasets, which are asynchronously updated and optimized for different query types, allowing for efficient querying and editing by generating and maintaining projection datasets that are subsets of the canonical dataset, and using a query rewriter to select the best dataset for each query.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If versioned datasets are used to improve query speed, then query performance is improved, but write speed deteriorates and system complexity increases
Solution Approach 1:
The database system is segmented into multiple independent components: a canonical dataset for storing original data, multiple projection datasets for different query perspectives, a compute layer for processing, and a storage layer for data. This segmentation allows each component to be optimized independently, enabling fast queries through pre-computed projections while maintaining simple write operations to the canonical dataset only.
Solution Approach 2:
Projection datasets are pre-computed from the canonical dataset and stored in advance. These projections contain pre-processed data organized in different sort orders and groupings that optimize specific query patterns. When queries arrive, the system can directly use these pre-computed projections without performing complex processing at query time, thus achieving low latency while keeping the system architecture relatively simple.
2Speed
If multiple data copies are stored to reduce query latency, then query performance is improved, but storage requirements increase tremendously
Solution Approach 1:
Different projection datasets store different subsets of data with different organizational characteristics tailored to specific query needs. Each projection contains only the data and organization necessary for its intended query patterns, rather than storing complete copies of all data. This allows the system to achieve fast query performance for specific query types while minimizing total storage requirements compared to storing all possible data views.
3Speed
If OLAP systems use versioned datasets for rapid complex query execution, then query performance is improved, but write performance deteriorates
Solution Approach 1:
The canonical dataset acts as an intermediary between write operations and projection datasets. Write operations only need to update the canonical dataset, which then serves as the source for asynchronously updating multiple projection datasets. This intermediary approach decouples write operations from the complexity of maintaining multiple versioned datasets, enabling fast writes to the canonical dataset while still providing rapid query execution through the pre-computed projections.
Solution Approach 2:
Projection datasets are pre-computed and stored in advance based on anticipated query patterns. This preliminary computation allows the system to handle complex queries rapidly by directly querying these pre-processed datasets without performing complex joins and aggregations at query time. Write operations remain simple because they only need to update the canonical dataset, with projection updates happening asynchronously in the background.
Data Source
AI summary
A database system comprised of a decoupled compute layer and storage layer is implemented to store, build, and maintain a canonical dataset, a temporary buffer, and projection datasets. The canonical dataset is a set of batch updated data. The data is appended in chunks to the canonical dataset such that the canonical dataset becomes a historical dataset over time. The buffer is a write ahead log that contains the most recent chunks of data and provides atomicity and durability for the database system. The projection datasets are indexes of the canonical dataset and/or the buffer that may have single or multiple column sort-orders and/or particular data formats. The writes to the canonical dataset, projection datasets, and buffer may be asynchronous and therefore the database system is advantageously less resource constrained.