pg_use_reserved_connections group in PostgreSQL 16Nathan Bossart implemented a brand-new patch that provides a way to reserve connection slots for non-superusers.
Table of Contents
The patch was reviewed by Tushar Ahuja and Robert Haas. Committed by Robert Haas. The commit message is:
Let's edit the postgresql.conf file and set these values:
I set superuser_reserved_connections to zero so that it does not interfere with our testing.
Now, let's create a simple mortal:
Now we connect as a user "pasha":
From another terminal we will repeat the above command:
WTF! In previous versions you are able to use all of the max_connections slots. But not anymore!
Now the time for the new role pg_use_reserved_connections:
And now you can establish the second session:
You may wonder how this new feature can be useful. After all, we already have superuser_reserved_connections, which reserves some connections for superusers. The idea of that parameter is that even if all connections are taken, a superuser can still connect and fix the problem, typically by terminating some connections. However, not all connections are equal: even if your application's connection pool is exhausted, you still would like your backup and monitoring tools to be able to connect to the database. And if you are security conscious, you won't want those tools to use a superuser to connect. The traditional way to handle this is a connection limit on the application user, but that requires you to readjust the limit whenever you change the size of the connection pool. pg_use_reserved_connections offers a convenient alternative solution to make some connections be “more equal” than others.
Connection pooling is a related topic that may interest you. Find out about pgbouncer, which is a free tool that makes connection pooling easy, right here.
Please leave your comments below. In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.
Leave a Reply