Database Connection Pooling for Low-Latency Sharded Bulk Queries

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Performing bulk read queries on large databases, especially sharded and multi-tenant databases, is computationally intensive and time-consuming due to increased latency and resource overhead, with existing techniques using multiple processor cores leading to inefficient query execution and prolonged processing times.

Innovation Solution

An asynchronous method utilizing an adaptive queuing technique with priority heaps to manage connections, allowing simultaneous execution of queries across multiple shards, reducing waiting times and computational burden, while maintaining database availability for tenants.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If multiple processor cores are used to execute queries in parallel, then query throughput is improved, but system complexity and resource overhead increase

Engineering Contradiction:

Improvequery throughputVSAvoidsystem complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The system segments the database into multiple shards and distributes query execution across multiple connections, each handling a subset of shards. This allows parallel processing while keeping each connection's workload manageable, resolving the contradiction between throughput and complexity.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The connection pool dynamically adjusts the number of active connections and their distribution across shards based on current system load and query patterns. This dynamic adaptation enables high throughput when needed while reducing complexity during low-load periods.

Inventive Principle:

Principle #15Dynamics

2Quantity of substance

If the database is sharded across multiple servers, then data capacity and availability are improved, but query latency increases due to the need to search multiple shards

Engineering Contradiction:

Improvedata capacityVSAvoidquery latency

Core Design Contradiction:

Quantity of substanceVSLoss of time

Solution Approach 1:

The system pre-establishes multiple connections to different shards and maintains them in a ready state within the connection pool. When queries need to access multiple shards, pre-warmed connections are immediately available, eliminating connection establishment overhead and reducing latency.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The connection pool maintains persistent connections to shards throughout the query execution process, avoiding repeated connection establishment and teardown. This continuity keeps connections warm and ready, reducing the time penalty associated with accessing distributed shards.

Inventive Principle:

Principle #20Continuity of useful action

3Reliability

If the database remains accessible to other tenants during query execution, then service availability is maintained, but query execution time increases due to shared resource contention

Engineering Contradiction:

Improveservice availabilityVSAvoidquery execution time

Core Design Contradiction:

ReliabilityVSDuration of action of moving object

Solution Approach 1:

The system segments the database access into dedicated connection pools for different operations. Bulk query operations use specific connections that can be managed independently, allowing other tenants to continue using the database through different connections without significant interference.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The connection pool acts as an intermediary layer between query operations and the underlying database shards. It manages resource allocation and can prioritize or isolate bulk query operations from regular tenant operations, maintaining overall availability while managing execution time.

Inventive Principle:

Principle #24Intermediary (Mediator)

4Productivity

If a large number of connections are maintained in the connection pool, then query parallelism is improved, but memory consumption and resource overhead increase

Engineering Contradiction:

Improvequery parallelismVSAvoidmemory consumption

Core Design Contradiction:

ProductivityVSQuantity of substance

Solution Approach 1:

The connection pool dynamically adjusts its size and composition based on current system conditions, query patterns, and available resources. This allows the system to maintain high parallelism when resources are abundant while reducing memory consumption during resource-constrained periods.

Inventive Principle:

Principle #15Dynamics

Solution Approach 2:

The system changes parameters such as connection pool size, timeout values, and connection lifecycle management based on system state. These parameter adjustments enable the system to optimize the balance between parallelism and memory consumption for different operating conditions.

Inventive Principle:

Principle #35Parameter changes

Data Source

PatentUS12608388B2Methods and systems for performing database operations

Publication Date: 2026.04.21 XERO

AI summary

Methods for performing a query operation on a database comprise selecting a connection to the database from a plurality of connections to the database, the connections being arranged within a data structure and each having a predetermined number of query slots; waiting for a query slot to be available on the selected connection; in response to determining that the predetermined number of query slots include at least one available query slot, accessing one of the at least one available query slots; reordering the plurality of connections within the data structure; causing the query operation to execute at least partially using the accessed query slot; and releasing the accessed query slot.