Database Connection Pool Exhaustion ⚡ Fix API Calls Now

Why do API calls inside transactions cause pool exhaustion?

A database transaction groups database operations into a single unit that either commits or rolls back. While it's open, the connection assigned to it stays checked out from your application's connection pool. Depending on what you do inside the transaction, you may also hold locks or keep a snapshot around until the transaction ends.

That connection is a limited resource. Your service might have 10, 20, or 50 connections per process - whatever number your database driver and deployment make reasonable. The exact limit varies, but the key point is the same: one open transaction means one checked-out connection. If you wait on a payment provider, shipping API, or identity provider inside the transaction, you're reserving that connection for the entire wait.

Here's the simplified pattern:

await db.transaction(async (trx) => {
await trx('orders').where({ id: orderId }).update({ status: 'processing' });
const payment = await paymentProvider.charge({
orderId,
amount: 5000,
});
await trx('orders').where({ id: orderId }).update({
status: 'complete',
paymentReference: payment.id,
});
});

The two database updates might take a few milliseconds. The payment request might take hundreds of milliseconds - or several seconds - because of provider load, DNS delays, TLS setup, a network fault, or retries inside your HTTP client. During all that time, the database connection remains tied to the transaction.

There's also a guarantee mismatch. A database can roll back its own changes, but it can't undo a payment that the provider has already accepted. If the provider charged successfully and your app never receives the response, your transaction may roll back while the payment still exists. In other words: a transaction doesn't make a database and an independent HTTP service atomic.

A practical rule: use a database transaction to coordinate database work, not to hold a connection open while waiting for another system. When you cross system boundaries, model that boundary explicitly and decide what happens when one side is slow or unavailable.

How does a slow API turn into an application-wide outage?

Think of a connection pool as a queue with a fixed size. When code needs a connection, it borrows one, runs its database work, and returns it. A long transaction delays that return. The database might be idle, but the application still treats those connections as "in use."

The break shows up during a latency spike.

Say you have 20 connections in the pool and the service usually handles enough traffic that you can keep a few busy at once. If external calls average around 150ms, things may feel fine. If the provider suddenly takes 4 seconds, each request holds onto a connection much longer. Requests pile up, and soon all 20 connections are checked out.

Now new database work queues behind the active transactions. That includes operations you didn't even connect to the provider - authentication lookups, health checks, reads for other features, and even the final write your system needs to finish a different request. Often the first error you see is a connection-acquire timeout, not an external API error.

This isn't just "a slow endpoint." It's a feedback loop:

1. Provider latency increases, extending transaction duration.
2. Longer transactions consume more connection capacity at once.
3. New database operations wait for a connection.
4. Latency and queueing rise across the service.
5. Retries and timeouts can add even more load when the provider is already under stress.

And the pool doesn't need to be tiny for this to happen. A bigger pool can delay saturation, but it can also just move the bottleneck to the database itself by allowing more concurrent sessions. Either way, the underlying issue is still that you're holding connections longer than you need to.

One more nuance: total request time and transaction time are not the same thing. A user request might legitimately take a second because it's waiting for a provider. That doesn't mean you have to keep a database connection checked out for that entire second. If your transaction only wraps short database operations, the same pool can serve other requests while your code waits on the network.

To catch this in production, watch pool checkout duration, waiting clients, transaction age, and sessions idle-in-transaction. Query latency alone can hide it: the database may report little active query work while application connections remain unavailable. The real question isn't just "How long did the SQL take?" It's also "How long was the connection checked out?"

How do you move network I/O outside the transaction?

Split the workflow into short database steps with an explicit state transition around the external operation. First, persist that you need to do the external work. Commit. Then make the network request without an open database transaction. Finally, write the outcome back in another short transaction if you need multiple database changes to be consistent with each other.

For example:

await db.transaction(async (trx) => {
await trx('orders').where({ id: orderId }).update({
status: 'pending_payment',
updated_at: new Date(),
});
});
let payment;
try {
payment = await paymentProvider.charge({
orderId,
amount: 5000,
idempotencyKey: `order-${orderId}`,
});
} catch (error) {
await db('orders').where({ id: orderId }).update({
status: 'payment_failed',
last_error: error.message,
updated_at: new Date(),
});
throw error;
}
await db.transaction(async (trx) => {
await trx('orders').where({ id: orderId }).update({
status: 'paid',
payment_reference: payment.id,
updated_at: new Date(),
});
});

Now the connection is used only for the quick reads/writes. The slow provider wait no longer directly holds a database connection.

The state names matter. pending_payment should tell operators and recovery code that the order is reserved locally, but the external outcome hasn't been recorded yet. Pick states that make room for the real lifecycle: "not attempted," "in progress," "confirmed," "failed," and sometimes "unknown." Also remember: a timeout isn't proof the provider rejected the request. The provider may have completed the operation while your app lost the response.

If the workflow must survive a process crash between committing state and sending the request, use a transactional outbox. In the same database transaction that writes the order's pending state, insert an outbox row describing the payment request. A worker reads unsent rows, calls the provider outside a transaction, and then records the result and marks the message processed. The extra durable row is what keeps the work available after a restart.

An outbox doesn't magically give you exactly-once delivery. A worker can send the request and crash before recording success, so the message gets sent again. That's why you should use provider-supported idempotency keys and make your local state transitions safe to repeat. For higher-risk workflows, that extra worker and operational machinery is usually worth it. For lower-risk cases, a simpler synchronous call plus reconciliation may be good enough.

How do you handle failures after decoupling?

Once the network call is outside the transaction, failures no longer trigger an automatic rollback of the earlier commit. That's not a design flaw - it's just distributed reality showing its work. Your job is to define what each failure means and what action is safe afterward.

Start by classifying failure types:

• Definite rejection: The provider returns a business error (invalid payment method, for example). Mark the operation failed or request corrected input.
• Transient failure: The provider is temporarily unavailable or returns a retryable response. Retry with bounded exponential backoff and jitter.
• Ambiguous outcome: The request times out or the connection drops after submission. Check the provider's operation status or retry with the same idempotency key; don't assume it failed.
• Local failure after remote success: The provider accepts the request, but the application crashes before recording the response. Reconciliation or an idempotent retry needs to repair local state.

A useful retry system has a maximum attempt count, a delay policy, an error classification, and a way to inspect work that has been exhausted. Retrying every failure immediately is not resilience - it can amplify an outage and create a thundering herd when the provider comes back. Jitter helps spread out retries, and persistent failures should go to an operational queue or alert.

Use a stable idempotency key derived from the business operation (order ID plus operation type). Don't invent a new key for every retry. Also confirm the provider's idempotency behavior and retention window - those details vary. If the provider lets you query an operation by reference, store that reference and use it during reconciliation.

A background worker can periodically scan records stuck in pending_payment past a threshold. It should figure out whether the provider completed the operation, retry safely when you can't find a known outcome, or flag the case for manual review when you truly can't determine what happened. Pick the threshold based on normal provider latency and your retry policy - not some arbitrary "universal" value.

Compensating actions matter when you need to reverse an earlier local change, but they are not "rollbacks." A refund, reservation release, or cancellation is a separate operation with its own failure modes. Track progress for those too, and make them retryable. For consequential workflows, treat each transition as a durable state change with clear ownership, rather than expecting one request handler to finish everything perfectly.

Which timeouts and metrics should you configure?

Moving network I/O out of transactions removes the biggest cause of avoidable connection holding, but you still need guardrails. Timeouts limit how long failures can keep resources tied up, and metrics tell you whether the system is behaving as designed.

Configure timeouts at the boundaries:

• HTTP request timeout: Set a finite deadline for the external call, including response handling. In Node.js, use your HTTP client's timeout or an AbortController. A timeout should produce a classified error, not an unbounded wait.
• Connection-acquire timeout: Limit how long a request waits to borrow a database connection. Make it observable and actionable instead of letting the request hang until an upstream timeout.
• Database statement timeout: Limit runaway statements where your database supports it. This isn't a "transaction duration" control, so set it separately.
• Idle-in-transaction timeout: If available, set a limit like PostgreSQL's idle_in_transaction_session_timeout. It's a backstop for sessions left idle inside a transaction - not permission to put external calls there.

Also pay attention to timeout ordering. If you're doing a synchronous request, your HTTP deadline needs to leave enough time to persist the outcome and return a useful response before the overall request deadline. Connection-acquire and statement limits should be tight enough to surface contention, but not so tight they cause predictable failure during normal bursts. Base the values on your own latency distributions and service objectives.

Track pool usage and workflow health together. Helpful signals include active/idle/waiting pool connections; connection checkout duration; acquire timeouts; oldest open transaction; idle-in-transaction sessions; external API latency and timeout rate; and the age/count of pending outbox messages or workflow records. Break these down by service or operation when you can - pool-wide numbers can hide a single feature monopolizing connections.

Finally, set alerts that point to the cause. For example, a growing queue of waiting database clients plus elevated external API latency points elsewhere than a database-wide query slowdown. An old pending operation might also indicate a stuck worker even when the pool looks fine. Logs should include a stable order or operation identifier, and they should never record payment credentials or other sensitive data.

The goal isn't to tune everything to the smallest possible value. The goal is simple: set an upper bound on resource occupancy and make failures visible before they spread into application-wide queues.