Database Sharding with Time-Partitioned Tables for Query Latency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face challenges in maintaining acceptable performance levels, particularly in query latency and data purging, as they grow in size and complexity, with existing methods often leading to increased costs and disruption when expanding or managing data volume and transaction rates.
Innovation Solution
A computer-implemented database system that utilizes a node group with time-partitioned tables and sharding, allowing for efficient data management by distributing user data across multiple nodes, optimizing query performance, and enabling quick purging of oldest data by dropping entire shards based on age, thereby maintaining high insert rates and query response times.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If data is stored in a single large table, then data volume capacity is increased, but query latency increases and system performance deteriorates
Solution Approach 1:
The database system segments the single large table into multiple time-partitioned tables, where each table contains data for a specific time range. This segmentation allows queries to be executed on smaller, more manageable data subsets, reducing query latency while maintaining the ability to store large volumes of data across multiple partitions.
Solution Approach 2:
The system introduces a time dimension to the data organization structure by creating time-partitioned tables. Each partition is further divided into shards distributed across multiple nodes, adding spatial distribution as another dimension. This multi-dimensional organization enables efficient querying by limiting search scope to relevant time partitions and shards.
2Quantity of substance
If data purging is performed by deleting individual records, then data retention requirements are met, but system performance and productivity are reduced
Solution Approach 1:
The system segments data into time-partitioned tables and further segments each partition into shards. This segmentation enables purging operations to target entire shards or partitions at once rather than individual records, dramatically improving purging efficiency while maintaining precise control over data retention periods through the time-based partitioning structure.
Solution Approach 2:
The system performs preliminary organization of data into time-based partitions and shards before purging is needed. This preliminary structuring allows for rapid purging operations by simply dropping entire shards or partitions that have exceeded the retention period, rather than having to identify and delete individual records during the purging process.
3Productivity
If database system expands to handle high event rates, then data processing capacity is increased, but system complexity and operational difficulty increase
Solution Approach 1:
The system segments the database into time-partitioned tables and shards distributed across multiple nodes. This segmentation allows the system to scale horizontally by adding more nodes to handle higher event rates, with each node managing a subset of partitions and shards. The modular structure maintains operational simplicity while increasing processing capacity.
Solution Approach 2:
The system adds time-based partitioning and spatial sharding as additional dimensions to the data storage architecture. This multi-dimensional organization enables the system to handle high event rates by distributing data across multiple nodes in the shard dimension and time partitions, while queries can be optimized by leveraging the time dimension to limit search scope.
4Loss of time
If time-partitioned tables with sharding are implemented, then query performance and data management efficiency are improved, but system structure and operational procedures become more complex
Solution Approach 1:
The system segments data into time-partitioned tables and shards, creating a hierarchical structure that improves query performance by limiting the search space to relevant partitions and shards. While the physical structure is more complex, the segmentation enables efficient query execution and data management operations.
Solution Approach 2:
The system introduces time-based partitioning and spatial sharding as additional organizational dimensions. This multi-dimensional structure enables optimized query performance by allowing the system to navigate efficiently through time partitions and shards, reducing the effective search space while maintaining a structured and manageable system architecture.
Data Source
AI summary
A computer-implemented database system, method, and computer program product are provided. the database system comprises a plurality of nodes, each node including a data storage device having memory and a server; and a database system controller coupled to each of the plurality of nodes and having a processor and software program code for directing the database system to perform the following function of defining, for a user, a node group including at least one of the plurality of nodes, upon which the user's data is to be stored in a user database; wherein the user database is defined in terms of time-partitioned tables residing on the nodes of the node group, and wherein each time-partitioned table is further defined in terms of shards of the user's data, the shards corresponding with respective ones of the time partitions. The user data is managed in terms of the shards on respective nodes of the node group, and the time-partitioned tables include a chronologically oldest time-partitioned table and a current time-partitioned table into which a new user data entry will be written. The system further performs the functions of determining whether a predetermined criterion has been met; and, responsive to the detecting that the predetermined criterion has been met, purging all shards of the chronologically oldest time-partitioned table.