Distributed Database Sharding for Parallel Aggregation Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database analytic tools are inefficient, costly, and require substantial configuration and training, limiting the accessibility and utility of large volumes of data stored in complex data storage systems.
Innovation Solution
Implementing sharding in a distributed database to partition data based on a sharding criterion, allowing aggregation operations to be performed at each database instance, reducing memory and network utilization, and optimizing data-query execution for aggregations with groupings.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If data is stored in a centralized database system, then data integrity and consistency are maintained, but query execution time and resource utilization increase significantly
Solution Approach 1:
The patent divides the centralized database into multiple shards distributed across different database instances. Each shard contains a subset of the data partitioned by sharding criteria, allowing queries to be executed in parallel across multiple instances rather than concentrating all processing on a single system, thereby reducing query execution time while distributing system complexity.
Solution Approach 2:
The patent introduces a new dimension of data organization by adding sharding keys and creating multi-dimensional data access paths. This allows queries to be routed to specific shards based on sharding criteria, transforming a single-point query bottleneck into a distributed parallel processing architecture that improves speed without proportionally increasing complexity.
2Measurement precision
If aggregation operations are performed on the entire distributed dataset, then comprehensive results are obtained, but memory utilization and network traffic increase significantly
Solution Approach 1:
The patent extracts the aggregation operation from the centralized coordinator and pushes it down to individual database instances that hold the shards. Each instance performs local aggregation on its subset of data, extracting only the necessary intermediate results to send to the coordinator. This dramatically reduces network traffic and memory requirements at the coordinator while maintaining accurate comprehensive aggregation results.
Solution Approach 2:
The patent implements partial aggregation at each database instance rather than requiring complete data collection and then aggregation. Each instance performs a partial aggregation on its local shard data, and these partial results are combined to achieve the complete aggregation. This partial action approach reduces the computational burden and resource utilization while still achieving the full aggregation result.
3Productivity
If data is partitioned into shards across multiple database instances, then query parallelism and resource utilization improve, but system configuration and operation complexity increase
Solution Approach 1:
The patent implements a universal sharding interface that works across different database instances and query types. The sharding mechanism is designed to be transparent to applications and can handle various query patterns (aggregations, groupings, filters) through a unified approach. This multi-functionality allows the system to achieve query parallelism while maintaining ease of operation through a consistent, standardized interface.
Solution Approach 2:
The patent introduces a query coordinator as an intermediary that manages the complexity of shard distribution and result aggregation. The coordinator receives queries, determines which shards are relevant based on sharding criteria, routes queries to appropriate instances, and combines results. This intermediary abstracts the underlying sharding complexity from applications while enabling query parallelism across distributed instances.
4Adaptability or versatility
If existing database analytic tools are used, then data analysis capabilities are provided, but cost and training requirements increase substantially
Solution Approach 1:
The patent enables the distributed database system to perform aggregation and analysis operations natively through its architecture, eliminating the need for external analytic tools. The system self-services by distributing queries across shards, performing local aggregations, and combining results automatically. This built-in capability provides data analysis functionality without requiring separate expensive tools or extensive user training.
Data Source
AI summary
A distributed database that includes multiple database instances receives a data-query that includes an aggregation clause on a first column of a table. The table is partitioned into shards according to a sharding criterion based on the first column such that all rows having the same value for the first column are included in the same shard. The shards are distributed to the multiple database instances. Respective intermediate results are received from at least some of the database instances. Each intermediate result received from a respective database instance that includes a respective shard aggregates values of the first column in the respective shard. The respective intermediate results are combined to obtain a final result of the data-query. The final result is then output.