Database Proxy Query Rewriting for Transparent Partitioning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Developing modern software applications that scale effectively with databases is complicated due to the need for matching application demands with database capacity while maintaining performance at a reasonable cost and complexity, often leading to over-provisioning and inefficiencies, especially when unforeseen peaks or changes in data size or usage patterns occur.
Innovation Solution
Implementing transparent database table partitioning using proxy-based query rewriting, where tables can be distributed across multiple database instances, allowing for dynamic resource allocation and intelligent query handling without requiring updates to application code, enabling efficient use of resources and improved caching and processing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If database capacity is over-provisioned to handle peak utilization, then system reliability is improved, but device complexity and cost increase
Solution Approach 1:
The patent segments the database into multiple tables distributed across different database instances. This allows the system to handle peak loads by utilizing multiple smaller instances rather than requiring one large over-provisioned instance, thereby maintaining reliability while reducing overall complexity and cost.
Solution Approach 2:
The patent implements dynamic table routing where the proxy can dynamically determine which database instance to route queries to based on current system state and demand. This dynamic allocation allows the system to adapt to varying loads without permanent over-provisioning, reducing complexity while maintaining reliability.
2Adaptability or versatility
If database tables are distributed across multiple instances, then adaptability is improved, but device complexity increases
Solution Approach 1:
The patent introduces a proxy as an intermediary layer between applications and distributed database instances. The proxy handles the complexity of table distribution, routing, and query rewriting, thereby providing adaptability to applications without exposing the underlying complexity.
Solution Approach 2:
The patent creates virtual copies of database tables across multiple physical instances. This allows the system to adapt to different query patterns and loads by distributing table copies appropriately, while the proxy manages the complexity of coordinating these copies.
3Ease of operation
If query rewriting is implemented in proxy, then ease of operation is improved, but device complexity increases
Solution Approach 1:
The patent implements automated query rewriting in the proxy that operates transparently without requiring manual configuration or intervention. The system automatically analyzes incoming queries, rewrites them as needed for the distributed architecture, and routes them appropriately, providing ease of operation while encapsulating complexity in the proxy layer.
4Productivity
If database resources are dynamically allocated, then productivity is improved, but device complexity increases
Solution Approach 1:
The patent implements dynamic resource allocation where database tables can be moved between instances and query routing can be adjusted based on current system state. This dynamic capability improves productivity by optimizing resource utilization, while the proxy manages the complexity of coordinating these dynamic changes.
Data Source
AI summary
Techniques for transparent database table partitioning using proxy-based query rewriting are described. Multiple tables of a single database can be split up and hosted by multiple database instances. A database proxy receives database statements to be executed by the database and can send the statement to the corresponding database instance. For statements referencing multiple tables that are not co-hosted by a same database instance, the proxy rewrites the statements to generate multiple different statements to be sent to individual database instances hosting the referenced tables, and generates a final result based on responses from the database instances.