Pitfalls which may cause the number of active connections to exceed the PostgreSQL client license count

[Pat France]

For understanding why the PostgreSQL client license count may be exceeded, it is important to understand what an active connection is from the perspective of the PostgreSQL server:

• A client application that opens a connection but does not disconnect
• A client application that opens a connection but fails to disconnect when it terminates. In this case, the connection remains active until the idle session timeout configured for the PostgreSQL server elapses
• A client application that is hung or terminates due to a runtime error and hence never disconnects. In both cases, the aforementioned idle timeout determines when the connection is freed by the server

The above list outlines situations in which misbehaving or crashed clients can make the active connection count exceed the legal limit, although the number of concurrent users has never reached this maximum. To make sure your applications behave correctly and do not hold on to or leak connections and hence exceed the client license count, make ensure that

• Your application disconnects at the earliest possible time
• All connections are disconnected before the process terminates
• The PostgreSQL idle_session_timeout configuration setting is set to a meaningful value in postgresql.conf.
  Note: this setting is 0 by default, which means sessions are kept alive indefinitely!

Note that the number of active sessions is listed on the dashboard of the PgAdmin tool and can be queried on-the-fly via the following SQL SELECT statement:

select count(*) used from pg_stat_activity where state='active'

References:

• Information on the idle_session_timeout setting: https://www.postgresql.org/docs/16/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT