Our PostgreSQL DBA contacted me however and told me that the service was leaving open and idle queries in the database which he says is not acceptable. He sent me a report as an example, see below.
POSTGRES_TXN_IDLE WARNING: DB "postgres" (host:******) longest idle in
txn: 56384s (15 hours 39 minutes 44 seconds) PID:32278
database:****** username:********* address:***.****.***
My understanding is that this is normal behavior for ArcGIS server map service connections but I would like to verify if this is the correct understanding or not (we have AGS 10.3.1 and PostgreSQL 9.3.x). So is this normal behavior or not?
The only viable solution that I can think of to meet the DBAs demand is to set the minimum number of service instances to zero so that idle services (and their connections to the DB) are closed after x minutes of inactivity. This however would result in a bit of a performance hit for the first user that comes along, and no instances are running, and he has to wait for at least one instance of the service to start up.
UPDATE 20160321: Found a post by another user which seems to confirm this behavior on PostgreSQL connections. He had some interesting findings and our DBA seems to agree with him that this is not a good way to make connections to a database. Here is the post, check it out.