The query is fast, but every request waits for a connection

The SQL dashboard shows fast queries and modest database load, yet the API times out as traffic rises. The missing interval occurs before any statement starts. Request threads are waiting for a pooled connection, and the query chart cannot display work that has not reached the database.

Measure the queue that query timing leaves out

A database request has several clocks. It can wait in an application executor, wait for a pool lease, spend time executing or waiting on a database lock, process returned rows, and hold the lease during unrelated work. End-to-end latency contains all of them. A query timer usually starts only after the application has acquired a connection, so it can stay healthy while users wait elsewhere.

This article names HikariCP because pool behavior is implementation-specific. According to the project's configuration documentation, maximumPoolSize caps the total pool size, including idle and in-use connections. When the pool has reached that size and no idle connection is available, getConnection waits for up to connectionTimeout. HikariCP throws a SQLException if no connection becomes available during that period. Those facts should not be copied blindly to a different pool.

Acquisition wait is the interval immediately around dataSource.getConnection(). Connection hold time begins after acquisition and ends when the application closes the logical connection. Query time covers statement execution and result consumption according to the instrumentation boundary. If result mapping occurs while the ResultSet is open, include it consistently. Otherwise teams compare unlike timings and call the difference noise.

A lease can be long even when SQL is fast. Code may acquire at the start of a web request, read one row, call an external shipping service, render a template, and close at the end. The connection is unavailable to another request throughout the network call and rendering. Moving acquisition next to the database work can release scarce capacity without changing the SQL at all.

Leaks create a different hold pattern. An exception, early return, cancellation, or forgotten close path never returns the logical connection. Capacity then declines until later callers queue or time out. Java's Connection interface is AutoCloseable, so try-with-resources is the normal way to guarantee close on successful and exceptional exits. In a pool, close returns the logical lease through the pool's connection wrapper rather than asking application code to manage the physical session.

Pool snapshots help, but they are not a stopwatch. HikariPoolMXBean exposes active, idle, total, and threads-awaiting-connection counts. Its documentation warns that these values are extremely transient and that separately read values may not add up because the pool can change between calls. Collect them as a time series and correlate them with request and database timings. Do not fail a test because active plus idle from two separate reads differs once from total.

The planning boundary is larger than one process. If several application instances each allow the same maximumPoolSize, their potential database sessions add together. Background workers, migration tools, monitoring, other services, and operational access also need capacity. PostgreSQL's max_connections documentation notes reserved and superuser-reserved slots and increased resource allocation when the setting rises. The application budget must sit below the administratively usable total rather than consume the emergency reserve.

Reproduce three causes that look the same from the browser

The first cause is ordinary queueing at the configured maximum. All connections are legitimately busy. New callers wait, then proceed when a lease returns or fail when connectionTimeout expires. Active stays at the configured maximum, idle stays at zero, and waiting callers appear during the interval. Query duration may remain normal.

The second cause is a leaked or unnecessarily held lease. The pool evidence can look identical at the moment of failure, but the timeline differs. Legitimate work releases connections as requests finish. A leak leaves active leases behind after their owning work has ended. An external call inside the lease creates long holds that align with the external span rather than database execution.

The third cause is failure to create or validate physical connections. The pool may remain below maximumPoolSize while callers wait because the database rejects connections, DNS or TLS fails, credentials are wrong, or the server has no available slots. Calling that local saturation and increasing the maximum makes the diagnosis worse. Inspect pool total, creation errors, database connection logs, and server slots.

A slow or blocked query is the most important near-miss. Acquisition can be quick, but the statement waits on a database lock or performs expensive work. Active connections rise because queries retain them, and later callers eventually queue. Pool saturation is then a consequence, not the first bottleneck. Query spans and database wait events show the difference.

Application executor starvation is another near-miss. A request may sit before it ever calls getConnection. Pool waiting remains zero, and connections can even be idle. Add timestamps at request admission, task start, connection request, connection acquisition, statement completion, and response completion. One end-to-end number cannot assign ownership.

One repeatable application case puts a controllable shipping stub after a short order lookup. In the broken version, each request acquires a connection, reads the order, and waits at the stub's gate without closing the lease. Release enough requests to occupy the configured pool, keep the stub gate closed, and start one more request. The expected evidence is that the database statements have finished while Hikari still reports active leases and a waiting borrower. Opening the gate lets the owners finish and the queue recover.

Run the same fixture after moving the remote call outside try-with-resources. Requests can still wait on the shipping stub, so end-to-end latency remains intentionally high while the gate is closed. The pool should now show the database leases returned and no acquisition queue caused by that gate. This A/B fixture distinguishes an external-service performance issue from an external-service issue that also starves the database pool. It does not need invented latency values because the stub's closed and open states provide the oracle.

A leak fixture needs a different trigger. Acquire a connection, execute a successful statement, then throw through the exact exception or cancellation branch involved in the incident. Stop new work and observe recovery. Ordinary saturation drains after owners complete. A leak leaves capacity checked out or leaves a transaction active after the owning request is gone. Confirm a later borrower can use the whole configured capacity, since seeing one active count decrease does not prove every path returned its lease.

Instance imbalance can mimic inadequate global capacity. One application instance may receive sticky or uneven traffic while another has idle connections. A cluster-wide average then shows spare capacity even though users routed to the hot instance time out. Tag pool metrics by instance and pool name, align them with load-balancer routing, and reproduce the distribution. Raising every instance's pool to fix one hot member spends database budget on members that were already idle.

Start with code that reports the boundaries rather than one database duration. This Java method returns a result plus acquisition, query, and hold timings. The numbers are measured by the running code; the article does not present any fabricated sample results.

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public final class TimedOrderLookup {
  public record Result(
      String status,
      long acquireNanos,
      long queryNanos,
      long holdNanos
  ) {}

  private final DataSource dataSource;

  public TimedOrderLookup(DataSource dataSource) {
    this.dataSource = dataSource;
  }

  public Result findStatus(long orderId) throws SQLException {
    long acquireStarted = System.nanoTime();
    long acquired;
    long queryNanos;
    String status;

    try (Connection connection = dataSource.getConnection()) {
      acquired = System.nanoTime();

      try (PreparedStatement statement = connection.prepareStatement(
          "SELECT status FROM orders WHERE id = ?")) {
        statement.setLong(1, orderId);
        long queryStarted = System.nanoTime();

        try (ResultSet rows = statement.executeQuery()) {
          if (!rows.next()) {
            throw new SQLException("Order not found: " + orderId);
          }
          status = rows.getString("status");
        }

        long queryFinished = System.nanoTime();
        queryNanos = queryFinished - queryStarted;
      }
    }

    long released = System.nanoTime();
    return new Result(
        status,
        acquired - acquireStarted,
        queryNanos,
        released - acquired
    );
  }
}

Hold time here ends after try-with-resources has closed the statement and returned the logical connection. If later code performs substantial mapping while still inside the outer try block, the reported hold includes it. Pick one definition, document it, and use the same boundary in production telemetry and test analysis.

Use a controlled pool boundary instead of invented load figures

A small integration test can prove HikariCP's configured behavior without pretending to measure production capacity. Give the pool two connections, hold both, and attempt a third acquisition. The third caller should receive SQLException after the configured connectionTimeout because no lease can return. The test needs a real disposable database; a mocked DataSource cannot exercise pool queueing.

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.SQLException;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertThrows;

final class PoolBoundaryTest {
  @Test
  void thirdBorrowerTimesOutWhileTwoLeasesAreHeld() throws Exception {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl(System.getenv("TEST_DATABASE_URL"));
    config.setUsername(System.getenv("TEST_DATABASE_USER"));
    config.setPassword(System.getenv("TEST_DATABASE_PASSWORD"));
    config.setMaximumPoolSize(2);
    config.setMinimumIdle(2);
    config.setConnectionTimeout(300);
    config.setValidationTimeout(250);
    config.setPoolName("pool-boundary-test");

    try (HikariDataSource dataSource = new HikariDataSource(config);
         Connection first = dataSource.getConnection();
         Connection second = dataSource.getConnection()) {

      assertEquals(
          2,
          dataSource.getHikariPoolMXBean().getActiveConnections()
      );
      assertThrows(SQLException.class, () -> {
        try (Connection ignored = dataSource.getConnection()) {
          throw new AssertionError("Third lease should not be available");
        }
      });
    }
  }
}

The 300 millisecond connection timeout is not a recommended production value. It is a deliberate test setting above HikariCP's documented minimum, chosen to keep this isolated test short. The 250 millisecond validation timeout is also explicit because HikariCP requires validationTimeout to be less than connectionTimeout; leaving the 5000 millisecond default would make this configuration invalid. The assertion does not claim acquisition lasted exactly 300 milliseconds because scheduling and connection housekeeping add variation. It proves the observable timeout branch and active lease count.

Add a release case beside it. Submit a borrower on another thread, observe that Hikari reports a waiting thread, close one held connection, and verify that the borrower obtains and closes its lease. Poll the MBean with a bounded condition rather than sleeping for an assumed duration. This proves recovery, which a timeout-only test cannot.

Capacity testing asks a different question: what workload can this deployment sustain inside agreed latency, error, correctness, and database-resource budgets? Use a controlled environment, representative query mix, realistic transaction boundaries, configured instance count, and actual database limits. Vary one factor at a time, such as pool maximum or hold behavior, and label every reported number as a measured result from that run.

Do not use the pool-sizing formula in the HikariCP wiki as a universal expected value. The project presents it as a starting point and explicitly says sizing is deployment-specific. Hardware, storage, cache behavior, query mix, locks, other workloads, and connection proxies change the result. Test around a safe candidate while watching throughput, latency, database CPU, I/O, locks, and connection usage.

Correctness stays in the load oracle. Timeouts can occur after a transaction commits or before a caller receives its result. Cancellation can leave work running. Retried writes can duplicate side effects. Alongside latency and pool metrics, verify order counts, idempotency records, balances, or other domain invariants relevant to the workload. A fast but incorrect run is not a capacity success.

Shape the workload around connection ownership rather than endpoint names alone. Two endpoints can execute the same SQL and have different hold times because one streams results or performs remote work before close. Include short reads, writes, long transactions, cancellations, and background jobs in the proportions measured for the target environment. If the proportions are hypothetical, label them as test assumptions and do not present the result as production capacity.

Exercise the approach to saturation and the recovery after load stops. A configuration can survive a brief burst yet fail to release cancelled work, refill invalid connections, or clear queued requests afterward. Define a completion condition such as all workload futures resolved, active leases returned to the expected baseline, no waiting borrowers, and correctness checks complete. Measure how the system reaches that condition during the run rather than sleeping for a convenient interval.

Pool partitioning is sometimes proposed for long jobs and interactive requests. Separate pools can stop one workload from consuming every interactive lease, but they also divide the database budget and add two configurations, metric sets, and failure modes. Test contention both within and across the pools. The database still sees their combined sessions, so isolation in the application does not create new server capacity.

Find the owner of the held connection

Correlate request, connection, and database evidence with one identifier. At request admission, record the request or trace ID. Around getConnection, record acquisition start and success or exception. Around each statement, record query identity and duration without raw sensitive parameters. At close, record hold duration. The database view should let operators connect the application session to its current query or transaction where the driver and policy support that metadata.

The strongest saturation shape is a sustained period in which active equals the pool maximum, idle is zero, waiting is above zero, and acquisition latency grows while statement time remains within its normal measured range. One isolated snapshot is weaker because MXBean values are transient. Align the series with request failures and the configured connectionTimeout.

A leak investigation follows ownership. Capture stack or trace information when a lease is obtained, then confirm the corresponding close. HikariCP's leakDetectionThreshold can log a possible leak when a connection remains out of the pool beyond the configured threshold. It is a diagnostic signal, not a verdict. A legitimate long transaction can cross the threshold, and a short-lived leak may return before anyone notices.

Read the acquisition exception in its calling context. A stack that reaches HikariDataSource.getConnection before any PreparedStatement execution points to pool acquisition. A statement exception or database lock wait after a successful acquisition points later in the path. Preserve the configured pool name, maximumPoolSize, connectionTimeout, active and waiting series, request ID, and whether the request deadline fired first. Avoid matching one full exception string because pool names and version wording can vary.

Timeout layering can hide the expected Hikari branch. If an HTTP request deadline or test timeout expires before connectionTimeout, the caller may be cancelled without ever receiving the pool's timeout SQLException. That does not mean getConnection was healthy. Compare the acquisition-start event with the outer deadline and waiting gauge. Set test-specific limits so the layer under investigation has time to produce its observable result, while retaining realistic production deadlines in the capacity scenario.

Build an incident evidence packet around relationships rather than a pasted dashboard. Record when acquisition began, whether it succeeded, which instance and pool handled it, and whether the pool had idle capacity. Add configured leases, long-hold owners where tracing permits, SQL identity, database locks, and close time. Include connection-creation errors and PostgreSQL slot use when total connections remain below the local maximum.

No single gauge proves ownership. Active at maximum with zero waiting can be healthy full utilization. Waiting above zero during a tiny transient can be harmless if the user budget is met. Low database CPU does not clear the database because lock waits, I/O, or a restrictive server connection limit can exist without high CPU. The conclusion comes from aligned acquire, hold, query, request, and server evidence.

Reproduce the exact exit path. Throw the exception that previously skipped close. Cancel the request at the same phase. Return early from the empty-result branch. After each case, wait for the application work to end and assert that active falls and a new borrower succeeds. A hard-coded assertion against a fixture that never acquires a connection proves nothing.

External calls inside a lease are visible when the hold span contains a quiet gap between short database spans and that gap aligns with an HTTP client span. The fix is to load the required state, close the connection, call the remote service, and open a new transaction only if later database work is needed. The following complete service shape keeps the shipping call outside the JDBC resource.

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public final class OrderQuoteService {
  public record Order(long id, String postalCode, BigDecimal subtotal) {}
  public record Quote(Order order, BigDecimal shipping) {}

  public interface ShippingClient {
    BigDecimal quote(String postalCode, BigDecimal subtotal);
  }

  private final DataSource dataSource;
  private final ShippingClient shippingClient;

  public OrderQuoteService(DataSource dataSource, ShippingClient shippingClient) {
    this.dataSource = dataSource;
    this.shippingClient = shippingClient;
  }

  public Quote quote(long orderId) throws SQLException {
    Order order;
    try (Connection connection = dataSource.getConnection();
         PreparedStatement statement = connection.prepareStatement(
             "SELECT id, postal_code, subtotal FROM orders WHERE id = ?")) {
      statement.setLong(1, orderId);
      try (ResultSet rows = statement.executeQuery()) {
        if (!rows.next()) {
          throw new SQLException("Order not found: " + orderId);
        }
        order = new Order(
            rows.getLong("id"),
            rows.getString("postal_code"),
            rows.getBigDecimal("subtotal")
        );
      }
    }

    BigDecimal shipping = shippingClient.quote(
        order.postalCode(),
        order.subtotal()
    );
    return new Quote(order, shipping);
  }
}

That change trades one long lease for a consistency decision. The order can change after it is read and before the shipping quote returns. If the workflow requires one atomic database transaction across later writes, simply moving the call may violate correctness. Redesign with version checks, a persisted workflow state, or an outbox where appropriate. Do not shorten hold time by discarding a business invariant.

Pool exhaustion caused by a database limit leaves different evidence. Hikari total may fail to reach maximumPoolSize, acquisition errors mention connection creation or validation, and PostgreSQL logs refused sessions or reserved-slot pressure. Multiply the configured potential across currently running instances, including overlap during deployments and autoscaling. A per-instance setting that is safe at steady state can exceed the budget while old and new instances coexist.

Roll out a fix without moving the bottleneck

Instrument before tuning. Add acquisition, query, and hold timings at the shared database boundary. Export Hikari active, idle, total, and waiting gauges with the pool name and application instance. Preserve configured maximumPoolSize and connectionTimeout as deployment metadata so a graph can be interpreted after configuration changes.

Next, classify high-hold paths. Fix leaks with try-with-resources and tests for exceptions, cancellation, and early returns. Move non-database work outside leases only after reviewing transaction requirements. Reduce result processing inside the lease where the driver and query semantics allow it. These changes usually deserve evaluation before a pool-size increase because they reduce demand rather than relocate it.

Build a connection budget with database owners. Reserve administrative and recovery capacity, account for other applications and jobs, and include maximum deployment overlap. PostgreSQL max_connections is a server ceiling, not a target for one service. Raising it changes server resource allocation and may need a restart, so it is not a casual application tuning switch.

Run a configuration matrix in the controlled environment. For every tested pool size, record the workload definition, instance count, query mix, connection timeout, actual throughput, end-to-end latency distribution, acquisition distribution, errors, database waits, CPU, I/O, and connection use. Results belong to that environment and date. Do not publish a table of convenient illustrative numbers as though an experiment occurred.

Canary configuration changes against one controlled slice when the platform permits it. Compare measured acquisition, hold, query, error, and database-resource behavior with an unchanged slice under comparable traffic. Include the canary's extra potential sessions in the database budget before deployment. A rolling update temporarily runs old and new instances together, so budgeting only the final replica count can create the exact slot exhaustion the change was intended to prevent.

Prepare a rollback condition tied to user and database health. A larger pool should be rolled back if it increases database waits, exhausts reserved headroom, or harms other workloads even when the canary's local acquisition time improves. A shorter timeout should be reconsidered if it converts recoverable queues into unacceptable errors. Configuration rollback is fast only when the prior values, deployment overlap, and observability labels are preserved.

Increasing maximumPoolSize can reduce local waiting when the database has spare capacity. It can also increase lock contention, memory use, context switching, and pressure from other services. Reducing it can protect the database and make overload fail earlier, but it may increase queueing during short bursts. A shorter connectionTimeout bounds wait and frees request capacity sooner, at the cost of more user-visible errors during recoverable spikes.

Use separate gates for correctness and capacity. Pull requests can run the two-lease boundary, exception cleanup, cancellation, and recovery integration cases. A scheduled or pre-release environment can run the representative workload. Shared CI runners are suitable for catching logical regressions, not for enforcing tight performance percentiles.

The following job wires the boundary test to a disposable PostgreSQL service. It maps the service port to the runner, waits for database health, and supplies explicit test credentials. The Maven project is assumed to declare JUnit, HikariCP, and the PostgreSQL driver.

name: jdbc-pool-contract

on:
  pull_request:
    paths:
      - "src/main/java/**"
      - "src/test/java/**"

permissions:
  contents: read

jobs:
  pool-contract:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:17
        env:
          POSTGRES_DB: pool_test
          POSTGRES_USER: pool_user
          POSTGRES_PASSWORD: pool_password
        ports:
          - 5432:5432
        options: >-
          --health-cmd "pg_isready -U pool_user -d pool_test"
          --health-interval 5s
          --health-timeout 5s
          --health-retries 12
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-java@v4
        with:
          distribution: temurin
          java-version: 21
          cache: maven
      - run: mvn -B -Dtest=PoolBoundaryTest test
        env:
          TEST_DATABASE_URL: jdbc:postgresql://127.0.0.1:5432/pool_test
          TEST_DATABASE_USER: pool_user
          TEST_DATABASE_PASSWORD: pool_password

The hard-coded credentials are confined to the disposable service in this example. They are not production secrets. A hosted integration environment should obtain credentials through its approved secret mechanism and must not attach them to test reports.

When pool saturation is the wrong diagnosis

Do not call every acquisition timeout a leak. Legitimate transactions can occupy the full pool under a workload above its service capacity. Prove that a lease outlives its owning work or misses a close path before filing a leak defect.

Do not enlarge the pool when statement or lock waits are already the first bottleneck. More concurrent queries can deepen that queue. Fix or isolate the expensive work, review indexes and transaction scope with database specialists, and then retest the pool boundary.

Avoid copying HikariCP settings or error expectations to another implementation. Pool growth, queue discipline, validation, timeout exceptions, metrics, and idle behavior vary. Read the exact version's official documentation and test its observable contract.

Do not run destructive peak load against production without capacity approval, monitoring, abort conditions, and business coordination. A pool test can exhaust database slots for real traffic. A disposable or representative isolated environment is the normal place to discover the knee safely.

Skip tight latency assertions in shared CI. Noisy hosts, image pulls, database startup, and neighboring jobs make them unstable. Assert the timeout and cleanup branches there, then measure performance in a controlled run and label the results honestly.

Finally, do not optimize hold time by breaking transaction correctness. Some work genuinely needs one database transaction and one connection context. Name that requirement, keep the critical section no larger than necessary, and account for it in the workload model. The useful fix removes accidental ownership, not required consistency.