Achilleas>So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time, the total number of connections would have to be raised to 1000.
Pools can shrink, so you do not have to raise total number of connections to 1000 unless you truly expect 1000 concurrent connections.
That is, you could configure 200 pools with minCapacity=0, maxCapacity=5.
Of course you would need thread-pool limits as well to avoid sending more than 5 concurrent requests on behalf of a given user id.
Technically speaking, there's JDBC standard way of calling "set_user" via java.sql.Connection#setClientInfo("ClientUser", java.lang.String), however I'm not sure how well it is supported/used.
PS Are you aware of ApplicationName? That is java.sql.Connection#setClientInfo("ApplicationName", java.lang.String)
Vladimir