A common misconception about PG needs correcting first: `max_connections` is not better when set higher. Each PostgreSQL connection is a separate process. Even two or three hundred active connections can tank throughput purely due to context switching. It is not uncommon to see slower performance after raising it to 2000. For the application side, HikariCP’s `maximumPoolSize` can be roughly estimated as cores*2 + disk count, which is usually sufficient. When multiple services share the same database, keep the sum of pool sizes across all services below 80% of `max_connections`, reserving headroom for superusers and monitoring.
For idle connection reclamation, use `idle_session_timeout` (available in PG 14+) or the pool’s `idleTimeout`, set uniformly to 5–10 minutes. Connections stuck in idle-in-transaction state are far more harmful than hitting the connection limit; pair this with `statement_timeout` as a safeguard to prevent such connections from lingering overnight.
If deploying PgBouncer, be aware of restrictions on prepared statements under transaction pooling mode. Use either session pooling mode or disable prepared statements on the driver side.