Slow API: Waiting for a Database Connection

This is the third article in Slow API, a series of practical investigations into API performance. Part 1 improved a slow SQL query. Part 2 removed N+1 queries. This time we use Go to investigate a delay that happens before the SQL starts.

The orders endpoint reads a page of orders, fetches delivery statuses, and returns the response. It executes one small SELECT. Under concurrent traffic, requests still take much longer than either the query or the delivery call.

The handler has wrapped the whole operation in a transaction. That boundary includes waiting for an external service.

We will reproduce the problem with Go's database/sql and PostgreSQL, inspect pool statistics, and compare increasing the connection limit with ending the transaction earlier. The delivery service is an explicitly simulated 200 ms operation; the database queries are real.

Start with the pool and the handler

In Go, a shared *sql.DB manages a connection pool. Our startup code opens it through the pgx database/sql adapter and sets a five-connection limit:

db, err := sql.Open("pgx", dsn)
if err != nil {
    return err
}
db.SetMaxOpenConns(5)
db.SetMaxIdleConns(5)
if err := db.PingContext(ctx); err != nil {
    return err
}

The driver is registered with import _ "github.com/jackc/pgx/v5/stdlib". Keep this db for the application's lifetime and close it during shutdown.

SetMaxOpenConns(5) caps the connections the pool can open. When all five are occupied, another operation must wait. SetMaxIdleConns(5) allows those connections to remain available for reuse afterward; it does not reserve them for particular requests. See Go's connection management guide.

The fixture has 20,000 orders across two tenants and an index on (tenant_id, id DESC). The handler reads 50 orders with this query:

SELECT id, total_cents
FROM lab_pool_orders
WHERE tenant_id = $1
ORDER BY id DESC
LIMIT 50;

Here is the original transaction boundary:

func listOrders(ctx context.Context, db *sql.DB, tenantID int64) ([]Order, error) {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{ReadOnly: true})
    if err != nil {
        return nil, err
    }
    defer tx.Rollback()

    orders, err := readOrders(ctx, tx, tenantID)
    if err != nil {
        return nil, err
    }

    if err := delivery(ctx, orders); err != nil {
        return nil, err
    }
    if err := tx.Commit(); err != nil {
        return nil, err
    }
    return orders, nil
}

readOrders executes the SELECT, scans every row into an []Order, checks rows.Err(), and closes the result. delivery fills the delivery status fields through one batched external operation.

The deferred rollback handles early returns. After a successful commit, that deferred call has no further effect on the transaction. This follows the pattern in Go's transaction guide.

But defer runs when the function returns. It does not release a connection while delivery is waiting.

Follow what owns the connection

A transaction begun through sql.DB owns one connection until commit or rollback. Closing its query result does not finish the transaction.

In this handler, the lifetime looks like:

BeginTx → Read and close rows → Wait for delivery → Commit
          |----------- transaction remains open --------|

The SELECT can finish quickly while its transaction continues to occupy a pool slot. Five requests reach the delivery call holding all five connections. The remaining requests wait inside BeginTx.

This is specific to the explicit transaction. A plain db.QueryContext outside a transaction returns its connection once its result is closed; for this single result set, fully iterating it also closes the rows automatically. Keeping a defer rows.Close() in the caller does not by itself prolong a fully consumed result. The Rows documentation explains that lifecycle.

Our one-SELECT example does not require an explicit transaction at all. We retain it in the comparison to isolate the effect of moving its end. An endpoint with multiple database operations may need a transaction, but its external calls still deserve a separate look.

Measure the queue as well as the query

Start with db.Stats():

The two wait fields are counters, so compare their changes over the same observation interval:

before := db.Stats()
// Run the measured workload.
after := db.Stats()

waits := after.WaitCount - before.WaitCount
waiting := after.WaitDuration - before.WaitDuration
log.Printf("connection waits=%d, total wait=%s", waits, waiting)

For a completed workload, waiting / waits gives the average wait among acquisitions that waited. It is neither a percentile nor an average across all requests. See DBStats.

We also time individual requests. The lab records:

BeginTx time includes connection acquisition and the driver work to begin the transaction. It is not a pure queue timer. Transaction span excludes that begin phase, so it is slightly shorter than the full time the connection is occupied. The read and delivery intervals are contained within the original transaction span; do not add overlapping timings.

Every pool connection is established before measurement. The timed path therefore excludes initial connection setup.

Inspect the database during the wait

The lab pauses the original handler inside its delivery callback. Once five requests reach that point, its pool reports InUse=5 and Idle=0.

A separate observer connection runs:

SELECT state, wait_event_type, wait_event, count(*) AS connections
FROM pg_stat_activity
WHERE application_name = 'slow-api-pool-go-hold_5'
GROUP BY state, wait_event_type, wait_event;

We observe:

PostgreSQL has completed the queries and is waiting for the application. The open transactions still occupy the connections. The state alone cannot identify which application operation is slow, but here we know the handler is paused inside delivery. PostgreSQL documents these states and events in pg_stat_activity.

The other requests waiting in Go's pool do not each have their own PostgreSQL backend yet. A database-only view misses that queue.

This diagnostic uses a gate in the delivery callback to make the state observable. It is separate from the timed benchmark.

Reproduce the delay under concurrent requests

For the timing comparison, 20 goroutines start together. Each reads the same 50 orders and waits on a 200 ms timer representing one delivery call. The next group starts after every request in the previous group finishes.

For each variant, we prewarm all connections, discard one warmup group, then measure 15 groups: 300 requests per variant. We rotate variant order across three rounds.

The environment is Go 1.26.6 on macOS arm64, pgx v5.7.6 through database/sql, and PostgreSQL 17.10 in Docker. Each request has a three-second context deadline. All measured requests complete successfully.

The original five-connection pool produces:

The read itself is a small part of the delay. Connections remain occupied for approximately the delivery wait, and later requests queue for a turn.

Five connections occupied for about 0.2 seconds each suggest roughly 5 / 0.2 = 25 requests per second for this workload. The measured 24.1 is consistent with that estimate plus database and application overhead. This is a check on our diagnosis, not a universal pool-sizing formula.

Compare two changes

First, we increase both SetMaxOpenConns and SetMaxIdleConns from 5 to 20.

That gives every concurrent request a connection. The pool records no queued acquisitions, and handler p95 falls to 220.3 ms. The larger pool helps at this concurrency, but transaction span remains about 207 ms: connections are still occupied during the delivery wait.

Next, we restore the five-connection limit and move the commit before delivery:

func listOrders(ctx context.Context, db *sql.DB, tenantID int64) ([]Order, error) {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{ReadOnly: true})
    if err != nil {
        return nil, err
    }
    defer tx.Rollback()

    orders, err := readOrders(ctx, tx, tenantID)
    if err != nil {
        return nil, err
    }
    if err := tx.Commit(); err != nil {
        return nil, err
    }

    if err := delivery(ctx, orders); err != nil {
        return nil, err
    }
    return orders, nil
}

The orders are already plain Go values. Once the read-only transaction ends, the connection can serve another request while this request waits for delivery.

The shorter transaction reaches approximately the same latency as the larger pool, using the original five-connection limit. The remaining 200 ms delivery wait is expected.

Notice that BeginTx p95 is 11.4 ms even when there are no pool waits. That interval includes beginning the transaction and scheduling under concurrency, not just acquiring a slot. The pool counters help distinguish those costs.

The second diagnostic pauses all 20 corrected handlers during delivery. It finds InUse=0, Idle=5, and five PostgreSQL backends in idle state rather than idle in transaction. Their connections are available for more database work.

Read the wait counters together

The counter deltas over 300 measured requests show why WaitCount alone is insufficient:

The original and corrected five-connection versions have the same number of waits. In each synchronized group, 15 of the 20 callers must wait for one of five slots.

What changes is how quickly those slots become available. A brief queue is very different from waiting through several external calls. Total waiting time sums across concurrent callers, so it can exceed the workload's wall-clock duration.

Before interpreting this as a production result, check the experiment's boundaries. It calls the handler directly, includes row scanning, transaction cleanup, and JSON serialization, and excludes HTTP handling and response transfer. The delivery delay is simulated outside PostgreSQL. Throughput is successful requests divided by measured group duration; p95 uses nearest rank across the 300 samples. This burst workload is not an open-loop capacity test.

The lab verifies identical JSON across variants, tenant filtering, amounts, ordering, empty results, and connection return on a delivery failure. It also checks that an ordinary read outside a transaction releases its connection, and that a context deadline cancels an acquisition waiting on a full pool.

Would PgBouncer help?

PgBouncer is useful when many application processes have their own pools. In transaction pooling mode, it can share PostgreSQL connections across clients between transactions. It releases a server connection only after the transaction ends. See PgBouncer's pooling modes.

Our original handler keeps its transaction open while waiting for delivery. PgBouncer cannot lend that transaction's PostgreSQL connection to another client during the wait.

There are also two distinct places to queue:

Request → Go sql.DB pool → PgBouncer → PostgreSQL

If all five Go pool connections are occupied, the next request waits before it can reach PgBouncer. Adding a proxy does not shorten that ownership.

After fixing transaction boundaries, PgBouncer can help share a database connection budget across replicas and worker processes. For example, four independent Go pools capped at 20 can collectively open up to 80 client connections. A transaction pooler can allow those clients to share fewer server connections when their transactions are short. Session pooling holds a server connection for a client's session and does not provide the same transaction-level sharing.

The measurements in this article connect directly to PostgreSQL; the PgBouncer explanation follows its documented behavior, not an additional benchmark.

Keep transaction and timeout semantics intact

Moving a commit changes when database work becomes final. Our endpoint only reads orders and adds an independently obtained delivery status. Its external service never participates in the PostgreSQL transaction, and there are no writes to undo if that service fails.

For a workflow with writes or locks, first decide which operations must remain atomic. Do not move a commit across a business operation simply because it improves latency. For this exact one-SELECT endpoint, removing the explicit transaction is also a reasonable implementation.

Go's context deadline has a different role from pool sizing. The context passed to BeginTx remains associated with the transaction; cancellation can cause it to roll back. A deadline therefore cannot be treated as an acquisition-only timer. Propagate the request context to the external call as well. See BeginTx and canceling database operations.

A larger pool may be appropriate when useful database concurrency needs it and PostgreSQL has capacity. Here, the measurements identify unnecessary transaction duration first.

What this investigation tells us

The endpoint was slow because requests waited for connections held by other requests during delivery calls. Moving the commit reduced p95 from 831.8 ms to 222.3 ms while keeping the same five-connection limit. Increasing the pool to 20 produced similar latency, but left each transaction open for about 207 ms.

That gives us a practical order for investigating a saturated pool:

• Locate the waiting time. Fast SQL does not tell us how long a request waited before executing it. Measure acquisition and transaction duration alongside query and endpoint timings.
• Inspect what happens while a connection is occupied. Finish database work before unrelated network calls when the consistency requirements allow it. Preserve the operations that must commit together.
• Read wait count and duration together. Our fix left the number of waits unchanged, but reduced their average duration from 414.6 ms to 10.8 ms. A wait counter alone would miss the improvement.
• Size pools after understanding the work. More connections can support useful concurrency, provided PostgreSQL has capacity. Count the budget across processes and replicas. PgBouncer can share server connections between transactions; it cannot release one that an open transaction still owns.

For this handler, we would ship the shorter transaction and keep the five-connection limit for a representative load test. We would check response correctness, errors, pool waiting, and endpoint latency together. The lab establishes why the change helps; production traffic determines whether that pool size is sufficient.

The Slow API series

1. Investigating a Slow Query — Use EXPLAIN ANALYZE, improve an index, and replace deep OFFSET pagination.
2. Fast Queries, Slow Endpoint — Find N+1 queries and compare SQLAlchemy relationship-loading strategies.
3. Waiting for a Database Connection (this article) — Measure Go connection pool waits, shorten transactions, and understand where PgBouncer helps.
4. Waiting for a Database Lock — Find the blocking transaction and shorten lock waits without breaking purchase correctness.