Multiple Queries in parallel with Gino.status(sql)

[kershnerd]

• GINO version: 0.8.3 (willing to upgrade if necessary)
• Python version: 3.8
• asyncpg version:
• aiocontextvars version:
• PostgreSQL version: AWS Aurora 11.6

Description

I have 10 text sql queries (each take 1 second to execute) that I would like to submit to AWS PostgreSQL to run in parallel. Currently I am doing a db.status(sql) for each of the 10 queries and assuming it takes 1 second for each query, the result is 10 seconds to complete.

I would like to submit all queries at once so that the total time is roughly equal to the longest query time any of the queries. This seems like a simple request, but I think something in Gino is only allowing the connections one at a time possibly?

What I Did

I tried to do a multi threaded version using a futures and ThreadPoolExecutor, but this caused the queries to take 3 times a long (30 seconds). (The following code segment's get_data is the function that calls the db.status(sql):

    with concurrent.futures.ThreadPoolExecutor() as executor:
        futures = []
        for key in query.keys():
            data = query[key]
            report_type = data.get('report_type')
            futures.append(executor.submit(get_data, report_type=report_type, data=data, location_id=location_id, key=key))

        for future in concurrent.futures.as_completed(futures):
            results = await future.result()
            response_json[results.get("key")] = results

I have a version that runs all 10 SQL queries in serial (with async/awalt), but this still takes 10 seconds to run 10 queries that take 1 second a piece:

    for key in query.keys():
        data = query[key]
        report_type = data.get('report_type')
        task_array.append(asyncio.create_task(get_data(report_type, data, location_id, key)))

    for task in task_array:
        results = await task
        response_json[results.get("key")] = results

So to make a long story, how can I submit queries to PostgreSQL using Gino in parallel?

[fantix]

In your particular case, using threads is not making much sense. asyncio.create_task() is the correct method, but you may want to check the following:

1. Database locks - make sure the 10 concurrent SQLs don't block each other in the database.
2. Make sure you have enough database connections for each asyncio.Task. This depends on how you establish the connections and use them:
   1. By default, db = Gino() and await db.set_bind() would create a connection pool of up to 10 connections.
   2. asyncio.create_task() will inherit the connection from the caller's context if any.
   3. Multiple tasks cannot use the same connection at the same time.
   4. Wrap your code with async with db.acquire(): block to ensure it's executed with a dedicated connection.

[fithisux]

Is there some working code for the above?