28

I have a Postgres 9.5 database for a web-based Java application. Sometimes, the number of sessions suddenly spikes up due to a long running query, and these sessions are not cleared immediately. When I check pg_stat_activity, query column shows COMMIT or ROLLBACK with a stage of idle. This is causing Postgres to hit high thresholds of max_connections, which can possibly cause Production outage.

query | ROLLBACK state | idle count | 167

query | COMMIT state | idle count | 280

  1. What are these COMMIT/ROLLBACK idle sessions?
  2. How can I get these idle sessions to clear immediately?
  3. How can I reduce the number of these idle sessions from being created?

Thanks

Murali
  • 351
  • 1
  • 6
  • 10
  • Possible duplicate of [Long lasting 'COMMIT' queries with 'idle' state in pg\_stat\_activity](https://stackoverflow.com/questions/44156050/long-lasting-commit-queries-with-idle-state-in-pg-stat-activity) – Laurenz Albe May 31 '17 at 06:32

1 Answers1

47

These are connections waiting for another query. They aren't doing anything. That's why they're idle.

Your app uses a connection pooler to avoid having to disconnect and reconnect all the time. When it has more connections than active queries, some will be idle, and the query shown will be the last completed query.

This is all entirely normal, and nothing to worry about. There is nothing you need to change or fix.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1
    Thanks @Craig Ringer. How can I find exactly what these new sessions were running? pg_stat_activity.query does not provide that information. Are there any other options I can check? – Murali Jun 07 '17 at 21:42
  • 1
    @Murali It only keeps the most recent query. You'd need to use `log_statement = 'all'` and add a `log_statement_prefix` including a session-id or xid, so you can reassemble the whole transaction's activity. – Craig Ringer Jun 08 '17 at 07:23
  • yes but these idle in transactions can created locks on the database, preventing other users editing other tables in the database. Is there a way to auto kill these idle transactions after a certain amount of inactivity time? – Theo F Oct 14 '20 at 22:17
  • @CraigRinger It does not have some effect in the memory? I mean, idle connections does not consume memory? – Marcos Echagüe Aug 19 '22 at 16:58
  • @MarcosEchagüe Yes, idle connections consume memory, but not usually a lot of memory. – Craig Ringer Aug 24 '22 at 05:07