# Tracking and managing your Postgres connections

DevFeed: [Tracking and managing your Postgres connections](<https://devfeed.tech/articles/tracking-and-managing-your-postgres-connections-41200.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2017/09/18/postgres-connection-management/>)

Author: Map

Published: 2017-09-18T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [Databases](<https://devfeed.tech/topics/databases.md>), [Monitoring](<https://devfeed.tech/topics/monitoring.md>), [Query (disambiguation)](<https://devfeed.tech/topics/query.md>), [Transactions](<https://devfeed.tech/topics/transactions.md>), [Statement](<https://devfeed.tech/topics/statement.md>), [scaling](<https://devfeed.tech/topics/scaling.md>)

Tags: [database](<https://devfeed.tech/tags/database.md>), [monitoring](<https://devfeed.tech/tags/monitoring.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [query](<https://devfeed.tech/tags/query.md>), [scaling](<https://devfeed.tech/tags/scaling.md>), [statement](<https://devfeed.tech/tags/statement.md>), [transactions](<https://devfeed.tech/tags/transactions.md>)

## AI overview

A tutorial on tracking and managing Postgres connections. It explains how to interpret connection states from pg_stat_activity, monitor them over time, use pgBouncer for idle connections, and apply statement and transaction timeouts to cancel long-running work.

## Source excerpt

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 manu