When working with PostgreSQL in real-world production systems, especially high-concurrency applications like Odoo, managing database connections becomes extremely important. If connections are not handled properly, the database can become completely unavailable under heavy load.
PostgreSQL solves this problem by providing multiple layers of connection control. These layers ensure that even when the system is under stress, critical users like administrators or backend services can still access the database.
There are mainly three important parameters in PostgreSQL configuration related to connection management:
- Max_connections
- Superuser_reserved_connections
- reserved_connections
Understanding how these three work together is the key to mastering connection handling in PostgreSQL.
Setting Up a Test Environment
To understand this concept practically, we first create a new PostgreSQL cluster.
sudo pg_createcluster 18 main
After creating the cluster, check its status:
pg_lsclusters
Output:
Ver Cluster Port Status Owner Data directory Log file
18 main 5434 online postgres /var/lib/postgresql/18/main
Now connect to this cluster:
sudo su postgres
psql -p 5434
This gives us a fresh environment where we can experiment safely.
Default Connection Configuration
Let us first inspect the default configuration values:
show max_connections;
show reserved_connections;
show superuser_reserved_connections;
Output:
max_connections = 100
reserved_connections = 0
superuser_reserved_connections = 3
From this, we can understand that by default PostgreSQL allows 100 total connections, and out of these, 3 are reserved for superusers. At this stage, there are no reserved connections for special roles.
Modifying Configuration Values
Now we change these values to clearly observe how PostgreSQL behaves.
First, find the configuration file:
show config_file;
Result:
/etc/postgresql/18/main/postgresql.conf
Open the file:
sudo nano /etc/postgresql/18/main/postgresql.conf
Update the values:
max_connections = 7
reserved_connections = 2
superuser_reserved_connections = 3
These values are intentionally kept small so that we can easily hit the limits and observe the behavior.
Creating Users
Next, we create users to test different scenarios:
create user test_user with password 'cool';
create user test_user_1 with password 'cool';
alter user test_user_1 with superuser;
Check the roles:
\du
Now we can set the
Output:
Role name | Attributes
-------------+------------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
test_user |
test_user_1 | Superuser
Now we have one normal user and one superuser for testing.
Testing max_connections Limit
Now we attempt multiple connections using the normal user:
psql -U test_user -d postgres -p 5434
After opening two connections, when trying the third connection, PostgreSQL throws the following error:
FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute
What is happening here?
We configured:
- max_connections = 7
- superuser_reserved_connections = 3
- reserved_connections = 2
This means:
- Only 2 connections are available for normal users
- 3 connections are strictly reserved for superusers
- 2 connections are reserved for special roles
So once the 2 normal slots are used, PostgreSQL blocks further normal users to protect higher-priority access.
Testing Superuser Reserved Connections
Now connect using a superuser:
We can log in as postgres user we already have or the super user we created
psql -U postgres -d postgres -p 5434
Even after normal slots are full, PostgreSQL still allows superuser connections.
We can open up to 3 additional sessions because of:
superuser_reserved_connections = 3
After those are also consumed, PostgreSQL returns:
FATAL: remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role
This clearly shows that PostgreSQL enforces priority levels among connections.
Understanding pg_use_reserved_connections Role
Now, let us explore the special role responsible for reserved connections:
select * from pg_roles where rolname = 'pg_use_reserved_connections';
Output:
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+------
pg_use_reserved_connections | f | t | f | f | f | f | -1 | ******** | | f | | 4550
(1 row)
This is an internal PostgreSQL role. It cannot log in directly, but it can be granted to users to give them access to reserved connection slots.
Testing Reserved Connections Behavior
At this stage:
- Normal users are blocked
- Superuser slots may be full
Now PostgreSQL protects the remaining reserved connections for users who have a specific privilege.
If a normal user tries to connect:
psql -U test_user -d postgres -p 5434
It fails with:
FATAL: remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role
Granting Access to Reserved Connections
Now, create a new user and grant the required role:
create user test_login_user with password 'cool';
grant pg_use_reserved_connections to test_login_user;
Now try connecting:
psql -U test_login_user -d postgres -p 5434
This connection succeeds because the user now has permission to use reserved slots.
But if we try again with a normal user:
psql -U test_user -d postgres -p 5434
It still fails with the same error.
This clearly demonstrates how PostgreSQL enforces controlled access to reserved connections.
The most important takeaway is that PostgreSQL does not treat all connections equally. It divides them into priority levels.
The max_connections parameter defines the total number of connections the database can accept.
The superuser_reserved_connections parameter ensures that database administrators can always access the system even when it is under heavy load.
The reserved_connections parameter introduces an additional layer that allows specific non-superuser roles to still connect when normal slots are exhausted.
The pg_use_reserved_connections role acts as a bridge to access these reserved slots without granting full superuser privileges.
Why This Matters in Real Systems
In applications like Odoo, the system handles a large number of small and frequent transactions. Under peak load, it is very common to hit the connection limit.
If all connections are consumed by application users, the database can become completely inaccessible. This is a serious issue because even administrators cannot log in to fix the problem.
By properly configuring:
- reserved_connections for backend services
- superuser_reserved_connections for administrators
We can ensure that critical operations remain possible even during overload situations.
PostgreSQL uses a layered connection control system instead of a single flat limit.
It separates connections into normal users, privileged reserved users, and superusers. This design ensures that high-priority operations are never blocked by regular workload.
Because of this architecture, PostgreSQL remains stable and manageable even under extreme concurrency conditions.