Reserve connections for the pg_use_reserved_connections group in PostgreSQL 16

Nathan Bossart implemented a brand-new patch that provides a way to reserve connection slots for non-superusers.

The patch was reviewed by Tushar Ahuja and Robert Haas. Committed by Robert Haas. The commit message is:

This provides a way to reserve connection slots for non-superusers.
The slots reserved via the new GUC are available only to users who
have the new predefined role pg_use_reserved_connections.
superuser_reserved_connections remains as a final reserve in case
reserved_connections has been exhausted.

Patch by Nathan Bossart. Reviewed by Tushar Ahuja and by me.

Discussion: http://postgr.es/m/20230119194601.GA4105788@nathanxps13

Let’s try reserve connections in our work!

Let’s edit the postgresql.conf file and set these values:

...
max_connections = 2                     # (change requires restart)
reserved_connections = 1                # (change requires restart)
superuser_reserved_connections = 0      # (change requires restart)
...

I set superuser_reserved_connections to zero so that it does not interfere with our testing.

Now, let’s create a simple mortal:

postgres=# create user pasha password '12345';
CREATE ROLE
postgres=# \q

Now we connect as a user “pasha”:

$ psql -U pasha -d postgres
psql (16devel)
Type "help" for help
postgres slot down
One slot down! One to go!

From another terminal we will repeat the above command:

$ psql -U pasha -d postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: 
FATAL:  remaining connection slots are reserved for roles with privileges of pg_use_reserved_connections

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:

postgres=# grant pg_use_reserved_connections to pasha; 
GRANT ROLE

And now you can establish the second session:

$psql -U pasha -d postgres
psql (16devel)
Type "help" for help.

postgres=>

Reserve connections? Why not regular ones?

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.

Finally…

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.