Hans-Juergen Schoenig: Connection Management in PostgreSQL – reserved_connections

In PostgreSQL 16, a new feature has been added: reserved_connections. What is the idea behind it? Back in the old days, DBA’s and software developers tended to work as a superuser (= postgres user). However, this is not ideal for security reasons. Therefore the PostgreSQL community has worked hard to reduce the necessity of connecting as a superuser. Over the years, features have been added to reduce the need of using this highly privileged way of connecting to the system, and reserved connections are another method. In this short blog post, you will be introduced to the reserved_connections setting and its additional roles in PostgreSQL 16. Adjusting connection parameters in PostgreSQL In PostgreSQL 16, we can see 3 major parameters controlling connections in general: postgres=# SHOW max_connections; max_connections ----------------- 100 (1 row) postgres=# SHOW superuser_reserved_connections; superuser_reserved_connections -------------------------------- 3 (1 row) postgres=# SHOW reserved_connections; reserved_connections ---------------------- 0 (1 row) The max_connections setting controls the number of overall connections allowed. This is a hard limit and changing this variable needs a restart. Out of this pool of connections, superuser_reserved_connections will only be available to the superuser. In our example, this means that we can create 97 “normal” connections and 3 superuser ones (or 100 superuser ones, which is not recommended at all). Why is that important? The idea is that there are always spare connections available in order to perform maintenance and other tasks. PostgreSQL 16 will provide us with a new, additional setting: reserved_connections. For many years, the idea has been circulated that it’s best to limit the use of superusers. Therefore, a new role called pg_use_reserved_connections has been introduced. If a normal (non-superuser) is assigned to this role, it’s possible to access the pool of reserved connections. In other words: We create a[...]