The problem #
Last week, our PostgreSQL database started running out of connections. The connection limit was 100, but after a day or so the application could no longer open a new connection. When PostgreSQL returned a too many connections error, both production and the worker stopped working.
The confusing part was that the application pool was configured with a much smaller number of connections. Looking at pg_stat_activity, however, showed connections in both idle and idle in transaction states. After each DigitalOcean App Platform deployment, some of these sessions appeared to remain behind. Over time they accumulated until they consumed the whole connection limit.
This query showed the scale of the problem. It revealed roughly 80 idle connections:
SELECT
datname,
usename,
application_name,
client_addr,
backend_type,
state,
count(*) AS connections,
min(backend_start) AS oldest_connection
FROM pg_stat_activity
GROUP BY 1, 2, 3, 4, 5, 6
ORDER BY connections DESC, oldest_connection;
To inspect individual connections and how long each had spent in its current state, I used:
SELECT
pid,
datname,
usename,
application_name,
client_addr,
state,
backend_start,
state_change,
now() - state_change AS time_in_state,
query_start,
now() - query_start AS query_age,
query
FROM pg_stat_activity
WHERE datname = 'your_database'
ORDER BY state_change ASC;
idle in transaction deserves special attention. It means that the client is no longer sending queries, but a transaction is still open. Such a session can keep locks and old row versions alive, so it is more harmful than an ordinary idle connection.
The immediate workaround #
To restore service after configuring the timeouts, I terminated stale idle application sessions. This forces the connection pool to create fresh sessions that pick up the new configuration:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'your_database'
AND usename = 'your_application_role'
AND state = 'idle'
AND now() - state_change > interval '15 minutes'
AND pid <> pg_backend_pid()
;
For another environment, use the corresponding database and role:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'your_other_database'
AND usename = 'your_other_application_role'
AND state = 'idle'
AND now() - state_change > interval '15 minutes'
AND pid <> pg_backend_pid();
Be careful with these commands. They disconnect real clients, so always narrow them to the intended database and application user.
Why the connections stayed around #
By default, PostgreSQL sets both idle_session_timeout and idle_in_transaction_session_timeout to 0, which disables them. An idle connection can therefore remain open indefinitely unless the client, a network event, or an administrator closes it.
That behavior is often reasonable, but it was not a good match for this deployment setup. The old idle sessions did not disappear quickly enough, while new application instances opened their own pool connections.
The fix: add two server-side timeouts #
I configured two PostgreSQL timeouts:
idle_in_transaction_session_timeoutcloses a session that is idle while it still has an open transaction.idle_session_timeoutcloses a session that is idle outside a transaction.
I applied them only to the application’s role and database. The transaction timeout was two minutes; the ordinary idle timeout was 15 minutes:
ALTER ROLE your_application_role IN DATABASE your_database
SET idle_in_transaction_session_timeout = '2min';
ALTER ROLE your_application_role IN DATABASE your_database
SET idle_session_timeout = '15min';
The exact duration should fit the application. Choose values that are longer than any legitimate idle period, but short enough that abandoned sessions cannot occupy the connection limit for hours. Using ALTER ROLE ... IN DATABASE keeps the policy scoped to this application rather than imposing it on every database user.
Recreate existing connections #
One important detail: database-level settings become defaults for new sessions. Existing connections do not inherit the new values.
After applying the configuration, I terminated the existing application connections and let the pool create fresh ones. From that point on, PostgreSQL applied the timeouts to each new connection.
The result was exactly what I wanted: the live connection count settled at the size defined by the application pool instead of slowly growing after every deployment.
A note about connection pools #
These timeouts are a safety net, not a replacement for correct pool and shutdown handling. The application should still close the pool gracefully during deployment and avoid leaving transactions open.
Also test idle_session_timeout with the connection pool in use. PostgreSQL warns that some pooling or middleware layers may not handle an unexpected server-side disconnect well. In this case, the pool recreated the closed connections cleanly and the timeout prevented stale sessions from accumulating.