SQL: keep the connection pool smaller than you think

-- What is actually connected, and what it is doing:
SELECT state, COUNT(*) FROM pg_stat_activity GROUP BY state;

-- The ceiling:
SHOW max_connections;

-- HikariCP, a common starting point:
--   maximumPoolSize = (core_count * 2) + effective_spindle_count
--   4 cores, SSD  ->  about 10

state               | count
idle                |   180
active              |     8
idle in transaction |    12      <- these hold locks and are the real problem

-- 'idle in transaction' means the application opened a transaction and went
-- away. Those connections block VACUUM and can hold row locks indefinitely.
-- idle_in_transaction_session_timeout closes them.