Search code examples
postgresqldata-connections

Where to define the maximum number of connections in Postgres?

I want to limit the number of users per database in a multi tenant environment. But there are three levels of max connections and I should be grateful for any advice.

Level 1 Entire Server

By editing the config for Postgresql I can set the Max connection for the all databases on a server

postgresql.conf = max_connections = 100

Level 2 Per database

I can select and set the database connection limit per database:

SELECT datconnlimit FROM pg_database

Level 3 Per role

I can select and set the role connection limit per "user":

SELECT rolconnlimit FROM pg_roles

My questions are

  1. If the max_connections in postgresql.conf is 100, will it be max connections of all databases regardless database and role settings? e.g. 100 databases can only have 1 connection each simultaneously?

  2. Where is the best place to limit max connections. At the database level or at the role level?

  3. Anything other to be considered?

TIA for any advice or clue!

Solution

    1. max_connections minus superuser_reserved_connections is the maximum for the sum of all non-superuser connections to all databases in the cluster.

    2. If you want to limit the number of users per database, setting the limit on the database seems like the obvious choice, right?

    3. If you end up setting max_connections to a high value, consider using a connection pool instead.