Paginated Database Query Engine with Connection Suspension
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In multi-tenant database services, active connections between clients and database services incur operational costs even when no data is being transferred, and clients may send redundant queries while original queries are pending, leading to inefficient resource management.
Innovation Solution
Implementing a query engine that classifies queries as short or long based on execution time, manages connections by terminating idle ones, and uses paginated responses to reduce bandwidth consumption, while blocking subsequent queries until the original query is completed.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the database service maintains an active connection to the client until the query is fulfilled, then the client can receive query results through the connection, but operational costs increase due to idle connection maintenance
Solution Approach 1:
The system dynamically adjusts connection state based on query status. When a query is submitted, the connection is placed in a suspended state rather than remaining fully active, allowing the system to maintain connection reliability while reducing operational costs during idle periods.
Solution Approach 2:
The connection state parameter is changed from active to suspended when a query is submitted. This parameter change allows the connection to preserve its logical state for receiving results while reducing resource consumption, effectively lowering operational costs without compromising reliability.
2Productivity
If the client repeatedly sends queries while the active connection is opened, then the client can continue to request data, but the database service performs redundant queries increasing processing overhead
Solution Approach 1:
The system implements feedback by returning a query identifier to the client when a query is submitted. The client uses this identifier to check query status before sending new queries, preventing redundant query submission and reducing processing overhead while maintaining query throughput.
Solution Approach 2:
The system performs preliminary action by suspending the connection and returning a query identifier immediately when a query is submitted, before the query execution completes. This allows the client to take preliminary checks using the identifier to avoid sending redundant queries during execution.
3Ease of operation
If the database service processes multiple queries from the client simultaneously, then the client can receive responsive service, but resource management efficiency decreases
Solution Approach 1:
The system segments query processing by assigning unique query identifiers to each query. This segmentation allows the service to track and manage individual query states separately, enabling efficient resource management while maintaining responsiveness to multiple client requests through structured organization.
Data Source
AI summary
A system to manage database queries including storage devices to implement a data store to store database data and computing devices to implement a query engine. The query engine is configured to receive, from a client, a database query and initiate performance of the query at the data store. The query engine is configured to compare a performance time of the query with a performance time threshold. Based on the performance time exceeding the performance time threshold: send a query identifier for the query and a token indicating that the query has not been completed; and receive, from the client, an additional query comprising the token and the query identifier. Based on a determination that the performance time does not exceed the performance time threshold, send a response to the query to the client, the response comprising data requested by the query.