I have been working as a Oracle DBA, and recently we started using PostgreSQL. We use PgBouncer, but as the app grew, we’re still ending up with a high number of DB sessions (around 650). PgBouncer helps with pooling, but it can’t prevent a large number of parallel active sessions from reaching the database.
The app team doesn’t fully understand PostgreSQL’s limitations when it comes to connection/session scaling, especially compared to Oracle, where we didn’t face this kind of restriction before. Because of that, they are pushing to increase 'max_connections' (already 800!) and CPU count on VMs, but we’re already seeing high CPU usage at this level.
The workload consists of a very large number of short-running queries executed in parallel active sessions.
My view is that the focus should be on optimizing the middle layer (connection handling, pooling strategy, and concurrency control), rather than increasing database limits and resources further.
What do you think?