Can you run multiple Flask SQLAlchemy queries in parallel?

Question

In my current setup, if I do five 100ms queries, they take 500ms total. Is there a way I can run them in parallel so it only takes 100ms?

I'm running Flask behind nginx/uwsgi, but can change any of that.

Specifically, I'd like to be able to turn code from this:

result_1 = db.session.query(...).all()
result_2 = db.session.query(...).all()
result_3 = db.session.query(...).all()

To something like this:

result_1, result_2, result_3 = run_in_parallel([
  db.session.query(...).all(),
  db.session.query(...).all(),
  db.session.query(...).all(),
])

Is there a way to do that with Flask and SQLAlchemy?

Answer 1

Parallelism in general

In general, if you want to run tasks in parallel you can use threads or processes. In python, threads are great for tasks that are I/O bound (meaning the time they take is spent waiting on another resource - waiting for your database, or for the disk, or for a remote webserver), and processes are great for tasks that are CPU bound (math and other computationally intensive tasks).

concurrent.futures

In your case, threads are ideal. Python has a threading module that you can look into, but there's a fair bit to unpack: safely using threads usually means limiting the number of threads that can be run by using a pool of threads and a queue for tasks. For that reason I much prefer concurrent.futures library, which provides wrappers around threading to give you an easy to use interface and to handle a lot of the complexity for you.

When using concurrent.futures, you create an executor, and then you submit tasks to it along with a list of arguments. Instead of calling a function like this:

# get 4 to the power of 5
result = pow(4, 5)
print(result)

You submit the function, and its arguments:

You would normally use concurrent.futures a bit like this:

from concurrent.futures import ThreadPoolExecutor
executor = ThreadPoolExecutor()
future = executor.submit(pow, 4, 5)
print(future.result())

Notice how we don't call the function by using pow(), we submit the function object pow which the executor will call inside a thread.

To make it easier to use the concurrent.futures library with Flask, you can use flask-executor which works like any other Flask extension. It also handles the edge cases where your background tasks require access to Flask's context locals (like the app, session, g or request objects) inside a background task. Full disclosure: I wrote and maintain this library.

(Fun fact: concurrent.futures wraps both threading and multiprocessing, using the same API - so if you find yourself needing multiprocessing for CPU bound tasks in future, you can use the same library in the same way to achieve your goal)

Putting it all together

Here's what using flask-executor to run SQLAlchemy tasks in parallel looks like:

from flask_executor import Executor
# ... define your `app` and `db` objects

executor = Executor(app)   

# run the same query three times in parallel and collect all the results
futures = []
for i in range(3):
    # note the lack of () after ".all", as we're passing the function object, not calling it ourselves
    future = executor.submit(db.session.query(MyModel).all) 
    futures.append(future)

for future in futures:
    print(future.result())

Boom, you have now run multiple Flask SQLAlchemy queries in parallel.