Database Connection Pool Exhaustion: Real Fixes That Work

Quick answer for advanced users

Run SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction'; on Postgres, or SHOW PROCESSLIST; on MySQL. Kill all idle-in-transaction connections older than 30 seconds with SELECT pg_terminate_backend(pid) or KILL CONNECTION id. Then increase pool size in your app config and add connection timeout settings.

What's actually happening here?

I remember the first time I saw this on a production Rails app at 2 AM. Users were getting 500 errors, and the logs screamed "could not obtain connection from pool within 30 seconds". The pool was set to 25 connections, but every single one was stuck waiting — not doing anything useful. They were all idle in transaction. The database itself was fine. The app was fine. But the pool was a ghost town of dead connections.

Connection pool exhaustion happens when your app opens more connections than the pool can hold, and old connections aren't returned fast enough. The usual culprit? A code path that opens a connection, starts a transaction, then hangs — maybe because of a slow query, a lock contention, or a bug where you forget to close the connection. Or you simply have more concurrent users than your pool can handle. The database doesn't care. It just says "no more connections for you".

Step-by-step fixes

1. Identify stuck connections

First, log into your database server. Use the command tailored to your database:

PostgreSQL:

SELECT pid, usename, application_name, client_addr, state, query, NOW() - query_start AS runtime, NOW() - state_change AS state_time
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_time DESC;

MySQL / MariaDB:

SHOW FULL PROCESSLIST;
-- Look for Sleep or Query states with time > 30 seconds

Look for connections in idle in transaction state that have been there for more than 30 seconds. Those are your vampires.

2. Kill the stuck connections

Once you have the PIDs (Postgres) or connection IDs (MySQL), kill them:

-- PostgreSQL: replace 12345 with actual pid
SELECT pg_terminate_backend(12345);

-- MySQL: replace 6789 with actual id
KILL CONNECTION 6789;

You can script this. On Postgres, I've used this one-liner:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND NOW() - state_change > INTERVAL '30 seconds';

On MySQL, watch out — KILL QUERY only kills the current query but leaves the connection alive. Use KILL CONNECTION to drop the whole session.

3. Restart your application

After clearing the dead connections, restart your app server (Tomcat, Puma, Gunicorn, whatever you're using). This reconnects your pool to the database with fresh connections. Don't restart the database itself — that's overkill and will kick everyone off.

4. Check your pool configuration

Now look at your app's connection pool settings. I'll use Java's HikariCP as an example, but the principles apply everywhere.

# Bad: no timeouts, huge pool
spring.datasource.hikari.maximum-pool-size=200
spring.datasource.hikari.idle-timeout=0
spring.datasource.hikari.connection-timeout=0

# Good: small pool with timeouts
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.max-lifetime=1800000

The trick: don't set max pool size above 20-30 per app instance. More connections don't mean faster queries. They just mean more contention. The database has a hard limit too (max_connections) — if you hit that, you're dead in the water.

Alternative fixes when the main one fails

If killing connections and restarting doesn't work, something deeper is wrong.

Check for a connection leak in code

Look for code that opens a connection but never closes it — even on exceptions. In Java, use try-with-resources. In Python, use context managers. A simple pattern:

# Python example: always close
connection = None
try:
    connection = pool.getconn()
    # do work
finally:
    if connection:
        pool.putconn(connection)

If you're using an ORM like Hibernate or ActiveRecord, check for @Transactional annotations or transaction.atomic() blocks that might be holding transactions open across HTTP requests. That's a classic — a service method annotated with @Transactional that calls an external API? The transaction stays open until the API returns. If the API hangs, the connection stays stuck.

Use a connection pool monitor

Enable metrics on your pool. HikariCP exposes JMX beans. Spring Boot Actuator can show pool metrics. Set up alerts when active-connections approaches max-pool-size — that's your early warning.

Increase database max_connections temporarily

If you absolutely need to, raise the connection limit on the database side. But this is a band-aid — it just delays the real fix.

-- PostgreSQL: edit postgresql.conf
max_connections = 200
-- then restart or reload
SELECT pg_reload_conf();

-- MySQL: set global
SET GLOBAL max_connections = 200;

Don't do this as a permanent fix. It masks the underlying problem.

How to prevent this from happening again

I've been running databases for years, and the single best prevention is setting proper timeouts everywhere.

1. Connection timeout in your pool: 30 seconds max. If you can't get a connection in 30 seconds, fail fast and let your users retry.
2. Idle timeout on connections: 5 minutes. A connection sitting around doing nothing for 10 minutes is a waste.
3. Max lifetime for a connection: 30 minutes. Rotate them out before the database or network equipment kills them silently.
4. Statement timeout in the database: Postgres statement_timeout = 30s. MySQL max_execution_time = 30000. Kill slow queries before they block the pool.
5. Transaction timeout in your ORM: Hibernate hibernate.transaction.flush_before_completion and javax.persistence.query.timeout. Rails ActiveRecord::Base.connection.execute("SET statement_timeout = 30000").

Also, monitor pg_stat_activity or SHOW PROCESSLIST weekly. Set up a cron job that alerts you if there are more than 5 idle-in-transaction connections. I've done that for every production system I've run since 2018, and it's saved my bacon more than once.

One more thing: if you're using connection pooling software like PgBouncer or ProxySQL, make sure it's set to transaction mode, not session mode. Session mode holds connections open for the whole app session, which defeats the purpose of pooling.

This error is annoying, but it's also one of the most fixable. You've got this.