Connection pool exhausted and every WMS returns 503

• org.apache.commons.dbcp2.ConnectionPoolTimeoutException: Timeout waiting for connection from pool.
• Every layer answers HTTP 503 with Retry-After: 120.
• pg_stat_activity shows the connection count at max_connections, with sessions staying active far longer than 30 seconds.

1. Group pg_stat_activity by usename and state to see who holds the connections.
2. Find sessions where state is active and now() minus query_start exceeds 30 seconds.
3. Check the datastore pool settings: maxActive, maxIdle and maxWait.
4. Compare the sum of all workspace pools against the database max_connections.
5. Distinguish leakage from an undersized pool: idle connections at zero with permanent waiting means leakage.

Raise database max_connections to 200 and size pools to match, such as 20 per datastore across three datastores with maxWait 10000 so requests fail fast instead of queueing forever. Close every Connection in a finally block and add a GiST index on roads.geom so a query stops holding a connection for tens of seconds.

ERROR [geoserver.wms] -
org.geoserver.platform.ServiceException: Rendering process failed.
Caused by: org.apache.commons.dbcp2.ConnectionPoolTimeoutException:
  Timeout waiting for connection from pool
    at org.apache.commons.dbcp2.Datasource.waitForConnection(Datasource.java:1305)

HTTP/1.1 503 Service Unavailable
Retry-After: 120

SELECT usename, state, count(*)
FROM pg_stat_activity
GROUP BY usename, state
ORDER BY count(*) DESC;

SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '30 seconds';

• Setting maxWait to -1, which makes requests wait indefinitely and turns one slow query into a total outage.
• Giving every layer its own datastore, so pool count grows linearly with layer count and blows the database limit.