Managing connections in Postgres is a topic that seems to come up several times a week in conversations. I’ve written some about scaling your connections and the right approach when you truly need a high level of connections, which is to use a connection pooler like pgBouncer. But what do you do before that point and how can you better track what is going on with your connections in Postgres?

Postgres under the covers has a lot of metadata about both historical and current activity against a system. Within Postgres you can run the following query which will give you a few results:

SELECT count(*), 
       state 
FROM pg_stat_activity 
GROUP BY 2;
 count |             state
-------+-------------------------------
     7 | active
    69 | idle
    26 | idle in transaction
    11 | idle in transaction (aborted)
(4 rows)

Time: 30.337 ms

Each of these is useful in determining what you should do to better manage your connection count. All of these numbers can be useful to record every say 30 seconds and chart on your own internal monitoring. Lets break down each:

• active - This is currently running queries, in a sense this is truly how many connections you may require at a time
• idle - This is where you have opened a connection to the DB (most frameworks do this and maintain a pool of them), but nothing is happening. This is the one area that a connection pooler like pgBouncer can most help.
• idle in transaction - This is where your app has run a BEGIN but it’s now waiting somewhere in a transaction and not doing work.

For idle as mentioned above it’s one that you do want to monitor and if you see a high number here it’s worth investing in setting up a pgBouncer.

For idle in transaction this one is a bit more interesting. Here what you likely want to do when first investigating is get an idea of how old those are. You can do this by querying pg_stat_activity and filtering for where the state is idle in transaction and checking how old those queries are. For ones that have been running too long you may want to manually kill them.

If you find that you have some stale transactions hanging around this could be for days, hours, or even just a few minutes you may want to set a default to kill those transactions.

To help with this Postgres has a nice feature of a 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 with an alter database dbnamehere set statement_timeout = 60000; which is 60 seconds. To do so during a given session simply run set statment_timeout = 6000000;.

For idle in transaction that have been running too long there is its own setting setting that you can set in a similar fashion idle_in_transaction_session_timeout (on Postgres 9.6 and up). Setting both statement_timeout and idle_in_transaction_session_timeout will help with cancelling long running queries and transactions.

Keeping your connection limits in check should lead to a much healthier performing database and thus app.

If you’re looking for a deeper resource on Postgres I recommend the book The Art of PostgreSQL. It is by a personal friend that has aimed to create the definitive guide to Postgres, from a developer perspective. If you use code CRAIG15 you’ll receive 15% off as well.