Flask-SQLAlchemy close connection

Question

I am using PostgreSQL and Flas-SQLAlchemy extension for Flask.

# app.py

app = Flask(__name__)
app.config['SQLALCHEMY_POOL_SIZE'] = 20
db = SQLAlchemy(app)

# views.py

user = User(***)
db.session.add(user)
db.session.commit()

Note that I am not closing the connection as suggested by documentation:

You have to commit the session, but you don’t have to remove it at the end of the request, Flask-SQLAlchemy does that for you.

However, when I run the following PostgreSQL query I can see some IDLE connections:

SELECT * FROM pg_stat_activity;

Does it mean that I have a problem with Flask-SQLAlchemy not closing connections? I am worried about it because recently I got remaining connection slots are reserved for non-replication superuser connections error.

Answer 1

SQLAlchemy sets up a pool of connections that will remain opened for performance reasons. The PostgreSQL has a max_connections config option. If you are exceeding that value, you need to either lower your pool count or raise the max connection count. Given that the default max is 100, and you've set your pool to 20, what's more likely is that there are other applications with open connections to the same database. max_connections is a global setting, so it must account for all applications connecting to the database server.