PostgreSQL connections are process based not threaded like some other databases.  Like everything else there are pros and cons to this method.  One of the cons is that each PostgreSQL connection consumes more resources when compared to threaded connections.  If you have a need to have a lot of connections to your database you may want to look at a connection pooler like pgBouncer.

What do your session look like?

PostgreSQL has views that allow you to see the details surrounding your sessions.  Run the following PostgreSQL query to see how many sessions you have and what they are doing.

Here is the result from one of my production 9.5 database clusters and the definition for each status.

  • active – This is a currently running query. The number of sessions that are active represent the number of connection you ultimately need at that time.
  • idle – This is an open connection that not currently doing anything in the database.  You will generally see these for a session that was active, are not finished, and are waiting for the next request from your client or application.  Too many of these and you may want to look into using pgBouncer.
  • idle in transaction – This session is where your application has issued a BEGIN many have done some work but is not just sitting idle and keeping a transaction open.  Too many of these can be problematic and should be investigated.  pgBouncer will not help here since these are in a transaction.

The good, bad, and the ugly

In my example above you can see, there is only one active session (mine) and there are 331 idle sessions and 61 idle in transaction sessions.  The number of idle and idle in transaction is a concern.  The application that uses this database is quite old with a great deal of technical debt.  Don’t let your applications do this to your database.  If you are running PostgreSQL 10 or later you may also see sessions with a NULL state.  In that case you may want to use the following query.

Here are the result for a different production cluster.

The sessions with a NULL state are all background PostgreSQL sessions.  Also, notice 1 of my active sessions is a walsender.  Your application will be backend_type = client backend.  On this database, the numbers are much more reasonable for a database that is not during a great deal of work currently.

Preventing the bad connections.

It’s critical to keep your PostgreSQL sessions in check.  Fortunately, there are a couple of settings that will keep your users and applications behaving well.  The first is statement_timeout.  A statement timeout will automatically kill queries that run longer than the allotted time.  You can set this at both a global level and for a specific session. To do this at the database level you’d run this:

This will not allow a query to be active longer than 60 seconds.  This is generally a very long time for an OLTP database, but may be far too short of a duration for an OLAP or analytics data store.  To deal with idle in transaction that has been running too long you can set idle_in_transaction_session_timeout (on Postgres 9.6 and later).  This setting is similar to statement_timeout, but act on any session that is idle in transaction.