Where to define the maximum number of connections in Postgres?

Question

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

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?
Where is the best place to limit max connections. At the database level or at the role level?
Anything other to be considered?

TIA for any advice or clue!

1) is clear from the docs (absolute limit for the whole server). And the rest depends on your use case. — dhke
– dhke, Commented Aug 22, 2016 at 13:34
dhke: "And the rest depends on your use case". Depends on what? Any hint? — sibert
– sibert, Commented Aug 22, 2016 at 15:20
Are there actually multiple DB users? (e.g. big web application with single DB user), Do you have multiple databases? Are those databases used by different users? In (common) case with single user, single DB, max_connections, datconnlimit, and rolconnlimit are all the same, governed by the lowest one. — dhke
– dhke, Commented Aug 22, 2016 at 15:29
Yes, there is multiple databases per server and normally one user per database. So does this mean that I can set rolconnlimit to 10 the role and datconnlimit to -1 on the database. And the max connections is handled by the role? Then the postgres admin could connect to the database without problem? — sibert
– sibert, Commented Aug 22, 2016 at 17:38
Note that there's also superuser_reserved_connections. datconnlimit and rolconnlimit are not enforced for superusers. — dhke
– dhke, Commented Aug 22, 2016 at 18:17

Laurenz Albe · Accepted Answer · 2016-08-22 18:43:55Z

6

max_connections minus superuser_reserved_connections is the maximum for the sum of all non-superuser connections to all databases in the cluster.
If you want to limit the number of users per database, setting the limit on the database seems like the obvious choice, right?
If you end up setting max_connections to a high value, consider using a connection pool instead.

answered Aug 22, 2016 at 18:43

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sibert Over a year ago

"If you want to limit the number of users per database, setting the limit on the database seems like the obvious choice, right?" So the role rolconnlimit has to be set higher or -1 than the datconnlimit to ensure access?

Laurenz Albe Over a year ago

If you don't want to limit the number of connections per user, leave rolconnlimit at -1.

Collectives™ on Stack Overflow

Where to define the maximum number of connections in Postgres?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related