How to efficiently use connection pools in high-latency APIs when using Flask-SQLAlchemy?

Question

I'm currently running a server using Flask + Flask-SQLAlchemy.

flask-sqlalchemy==2.5.1 sqlalchemy==1.4.49

I recently encountered a QueuePool Timeout error in some APIs of this server. After analyzing the cause, there was a bottleneck in the external API request executed after DB search, and at this time, there was an issue in which the connection could not be returned due to the bottleneck occurring in multiple requests.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["SQLALCHEMY_POOL_SIZE"] = 4
app.config["SQLALCHEMY_MAX_OVERFLOW"] = 4
app.config["SQLALCHEMY_ENGINE_OPTIONS"] = {
    "pool_use_lifo": True,
    "pool_pre_ping": True,
    "pool_recycle": 30,
}

db = SQLAlchemy()
db.app_init(app)


foo = Foo(db.session)
...
class Foo:
    def __init__(self, session):
        self.session = session

    @blueprint.route("/foo", methods=["POST"])
    def api(self):
        user = (
            self.session
                .query(UserModel)
                .filter_by(id=id)
                .first()
        )
        
        # ...
        time.sleep(20) # <-- API call
        # ...

        profile = (
            self.session
                .query(Profile)
                .filter_by(id=id)
                .first()
        )
        try:
            profile.url = "..."
            self.session.commit()
        except:
            self.session.rollback()

# Requests that exceed the connection pool size
curl -X POST http://localhost:3000/foo
...

As in the code above, if an external API request is executed for a long time, a DB connection cannot be returned. And eventually I get a QueuePool Timeout error.

To solve this problem, I am trying to close the session after the select query.

    ...

    def api(self):
        user = (
            self.session
                .query(UserModel)
                .filter_by(id=id)
                .first()
        )
        self.session.close() # <-- like this

        # ...
        time.sleep(20) # <-- API call
        # ...

        profile = (
            self.session
                .query(Profile)
                .filter_by(id=id)
                .first()
        )
        try:
            profile.url = "..."
            self.session.commit()
        except:
            self.session.rollback()

However, I'm not sure if this workaround is the intended behavior of Flask-SQLAlchemy. Has anyone experienced the same issue as me? Is there anyone who can suggest another way?

Answer 1

The docs say this should work because the session doesn't reconnect automatically since 1.4+ and can still be used again: https://docs.sqlalchemy.org/en/20/orm/session_basics.html#closing

Although in your example the user you fetch before close cannot be used with the session after close. You would have to fetch it again after the close or maybe do something more complicated by expunging it and then merging it back in (seems like overkill and is pretty advanced).

In general, this is not the typical use of the session. Web requests are not meant to take 20 seconds. You can quickly run out of resources (db connections obviously but also memory/files/etc.) if you have many requests piled up but never finishing. This is a pretty common problem though. Maybe you could move the api call operation into a cronjob or into a job queue?