Flask and SQLAlchemy causes a lot of IDLE in transaction connections in PostgreSQL

I've seen this situation occur when you run Flask in Debug mode. If your code throws an exception and the debugger kicks in, the transaction will never get "rolled back" or "removed". As a result, the session that was used on the request that failed never gets returned to the pool.

The solution is to disable debug mode.

EDIT:

There's another circumstance where I've seen this happen. If you have code that runs autonomously (i.e. not part of an HTTP transaction – like an independent thread started and spawned off at launch of the Flask app), it will usually involve a sleep. If you access the session before the sleep, then you'll end up with a hung transaction like this during the sleep.

Another possibility is you are accessing a session from the create app function. If you do so, make sure to .remove() it. Otherwise, that session could remain hung on the main thread in a gevent app.

from sqlalchemy.pool import NullPool

use the NullPoll as poolclass solved the problem for me. Not sure why.

EDIT(Mar 23 2021):

Despite I got downvote, If using uWSGI, this probably the only choice. Check the sqlachemy official doc

https://docs.sqlalchemy.org/en/14/core/pooling.html#using-connection-pools-with-multiprocessing-or-os-fork