Postgresql – Postgres IDLE State affect Max_Connection value set in Postgresql.conf

max-connectionspostgresql

I am using Postgres 9.5 and we have a max_connection set to 200 in our database postgresql.conf file.

We have 2 apps server running java apps which are load balanced. When we run our tests via JMETER, I run pg_stat_activity and I can se there is alot of IDLE connections with most of the queries called COMMIT.

Do I need to be concerned with the IDLE connections and will this affect any new tests I run. Could this be an issue with my Java apps not auto_commiting?

My assumption is that the connections will be re-used but concerned if we run more tests new connections will spin up and the max_connection of 200 users will be hit.

Any help is much apprecaited.

Best Answer

When the state is idle, then the query shown in pg_stat_acitivy is the last query that was run before it went idle. Since that was apparently COMMIT (and since the state is idle, not 'idle in transaction'), then no, you don't need to worry about autocommit not working.

My assumption is that the connections will be re-used but concerned if we run more tests new connections will spin up and the max_connection of 200 users will be hit.

If your java connection poolers are set up correctly, they should be reused. If you run out of max_connections, you will hear about it in the log files. Why preemptively worry about it? Are you running your tests against your production database?

Related Question