What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections? Also, how should I determine the number of max_connections and max_transaction_locks on postgresql.conf?

Our application has over 200 concurrent processes, each of which regularly reads and writes to the database. Using session-level, I would expect the number of pooled connections to be around the number of concurrent processes working on the DB at a given time, but I don't really know how to translate that to transaction-level pooling.

Thanks very much! Let me know if more information is necessary and I will gladly oblige.

  • What kind of application? Is this a web app? A thick client? If it is a thick client, how many queries are active, usually, at any given point, and how many connections are idle? Commented Mar 14, 2013 at 7:35
  • @Chris. How can I check for this information? Is there a command or SQL statement that I can run to know this? Thanks :) Commented Mar 14, 2013 at 16:20
  • @ChrisTravers Oh, and this is a distributed application. We have both a Web (WSGI) front end, and the concurrent processes are back-ends using SQLAlchemy and running asynchronously. Commented Mar 14, 2013 at 17:37
  • 1
    look at pg_stat_activity and compare those which are idle to those not. The following query will do: select SUM(case when current_query like '%IDLE%' THEN 0 ELSE 1 END) as idle, count(*) as total from pg_stat_activity; Commented Mar 20, 2013 at 10:16


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.