We're currently looking into how we could scale a system in which we have many users, each with their own postgres schema, and their own DB user credentials to ensure isolation. This however means that each user will effectively need their own connection to the database, and since the number of concurrent open connections to a postgres server is fairly limited due to the memory overhead for each connection on the server we are looking into potential solutions. pgBouncer would have been nice to use and multiplex multiple incoming connections to a fixed connection pool to the actual database, however we were unable to confirm that the user isolation based on user credentials and database schema can be mapped to that scenario.
The user sessions, and therefore the connection lifetime, have a duration of a couple of minutes, but are not heavily used (single DB transaction with associated burst of queries every couple of seconds), and the queries are not latency sensitive, so we could technically disconnect between transactions and reconnect on demand, with a proxy such as pgBouncer keeping the connection state. Speaking of state, the application does not make use of named prepared statements, so the server side state is likely not large anyway, but being a cloud hosted database we also cannot easily modify the max_connections config on the server.
Is there a way to map this scenario to pgBouncer or a similar proxy?
