Getting a count of active connections in PostgreSQL

While working on my latest project in Rust with Diesel I ran into an issue when sharing a Postgres instance between two applications. The second application (in my case, Ory Kratos) was unable to establish connections to the database, telling me that there were no available connections. At first I thought maybe my Postgres database was configured with a very low max_connections limit, or perhaps my Rocket/Diesel application was not closing connections properly. While debugging this problem I ran into a number of useful PostgreSQL queries to see the current connection state.

Querying for max_connections setting

The first useful query is to determine just what the number of maximum connections the database will allow is. I believe there are a number of ways to do this but I liked this SQL query best:

postgres=# select setting::int max_conn from pg_settings where name=$$max_connections$$;
 max_conn 
----------
      150
(1 row)

Okay, so I have 150 (at the time it was 100) max connections, how in the world am I using all of them? Let’s find out just what is using all of our connections.

Connection Count by Database

In my case each application runs its own Database (it’s really a bad idea to share a database between applications!) so it’s easy to tell how many connections each application is using. In this case I use the pg_stat_activity table with a group by on database name (datname).

postgres=# SELECT datname, COUNT(datid) FROM pg_stat_activity GROUP BY datname;
  datname   | count 
------------+-------
 kratos     |     1
 footprints |    96
 postgres   |     1
            |     0
(4 rows)

Uh, whoa. My application (footprints) is using 96(?!) connections? What gives? I restarted the application and it immediately went back to 96 open connections. So it’s clearly not a connection closing problem. By default 3 connections are reserved for super users (so you can actually log in to debug a database with all connections used), leaving only one connection available after footprints starts.

Well it turns out that the way that the R2D2 connection pooler works by default is to open 4 * num_worker connections. By default the num_workers is set to num_cpus which in my case is 24. So with 24 * 4 open connections my maximum 100 connections is almost immediately used up with just that one app! I set the connection count to a more reasonable(for development) number which freed up the number of connections for my other application. I simply set the pool_size configuration in my Rocket.toml file to 20. And if I check pg_stat_activity:

postgres=# SELECT datname, COUNT(datid) FROM pg_stat_activity GROUP BY datname;
  datname   | count 
------------+-------
 kratos     |    16
 footprints |    20
 postgres   |     1
            |     0
(4 rows)

There! Now kratos is able to open the number of connections it wants and footprints will manage with a connection pool of 20.

Other useful Postgres Queries

Another useful query I found on this stackoverflow answer was useful to see more configuration:

postgres=# select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal 
from 
  (select count(*) used from pg_stat_activity) t1,
  (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,
  (select setting::int max_conn from pg_settings where name=$$max_connections$$) t3
;
 max_conn | used | res_for_super | res_for_normal 
----------+------+---------------+----------------
      150 |   52 |             3 |             95
(1 row)

Which gives us some insight into the number of remaining available connections.

Querying against the pg_stat_activity table without constraints is also quite useful, as it will give you a list of current connections, their most recent query, and when the query started. Here is an abbreviated query (there are a lot of columns):

postgres=# select datname, usename, state, query from pg_stat_activity where datname is not null limit 5;
  datname   |  usename   | state |                                                         query                                                          
------------+------------+-------+------------------------------------------------------------------------------------------------------------------------
 kratos     | kratos     | idle  | SELECT networks.created_at, networks.id, networks.updated_at FROM networks AS networks ORDER BY created_at ASC LIMIT 1
 footprints | footprints | idle  | SELECT 1
 footprints | footprints | idle  | SELECT 1
 footprints | footprints | idle  | SET CLIENT_ENCODING TO 'UTF8'
 kratos     | kratos     | idle  | SELECT networks.created_at, networks.id, networks.updated_at FROM networks AS networks ORDER BY created_at ASC LIMIT 1
(5 rows)

I can see that most of my connections are in the idle state (no queries currently being run) and that the latest query my application ran is SELECT 1 which is part of my liveness probe for Kubernetes.

I found this handful of simple Postgres queries very helpful in diagnosing what was going on with my applications. Do you know of other queries that are helpful when debugging Postgres problems? Let me know in the comments!