Seeing query performance which is not satisfactory on our Python application, which runs several processes that use
SQLAlchemy core to access a PostgreSQL 9.2 database. We may have around 100 - 200 or more concurrent processes executing queries against the database.
We have no connection pooling systems like
pgbounce currently installed.
I have tested a query which can take in the order of 1-10 seconds from our application, which when run over
psql will take at most 20 ms.
We believe this issue is related to concurrent connections particularly because of the behavior described above and because we see these performance hits when accessing different tables in the system. However, we are not sure of this and could very readily accept alternate suggestions or solutions.
What is the usual way of handling concurrent connections from multiple processes against a single database server, in a way that will enable queries to execute quickly?