Returning response in FastAPI takes a long time and blocks everything

Question

I got problem with my api FastAPI, I got a big request that return me 700k rows. This request take 50 sec to be treat. But, the return response take 2mins and completely block the server who can't handle other request during those 2 mins.

And I don't Know how to handle this ... Here is my code :

@app.get("/request")
async def request_db(data):
    dict_of_result = await run_in_threadpool(get_data_from_pgsql, data)
    # After 50 sec the code above is done with even others requests coming working

    # But this return below block the server for 2min !
    return dict_of_result

I can't add limit or pagination system that request is for specefic purpose. Thank you for help

"completely block the server " what server are you using? A proper webserver should be able to handle multiple requests (leaving aside the wisdom of trying to return 700K+ rows of data). — snakecharmerb, Jun 10 '22 at 16:00
Run multiple workers with your webserver (`-w` usually), and depending on how `get_data_from_pgsql` is implemented, make sure that it handles its IO async as well. — MatsLindh, Jun 10 '22 at 19:13
Have a look at related answers [here](https://stackoverflow.com/a/73694164/17865804) and [here](https://stackoverflow.com/a/73580096/17865804) as well. — Chris, Dec 10 '22 at 11:12

score 1 · Answer 1 · answered Jun 10 '22 at 16:14

You should not make a 700k row database request from FastAPI or any other web server.

I would update this application logic / query to offload the processing to the database or to an external worker and only make a query for the result.

AsyncIO prevents the application from blocking while waiting for IO, not processing what must be a huge amount of IO. This is especially worse in Python where you are single process bound by the GIL (Global Interpreter Lock).

EFreak · Accepted Answer · 2022-12-23T18:54:40.230

This is a bit late. But here's some info for other readers. There are 2 problems here.

Running the query returning a giant result. Seems like this is not the problem here
Returning the result.

The problem is serializing a giant dataframe/dict all at once and in memory. This is what streaming is for and ideally should start at the db level where you can stream out the data as you are processing it.


@app.get("/request")
async def request_db(data):
    dict_of_result = await run_in_threadpool(get_data_from_pgsql, data)
    # After 50 sec the code above is done with even others requests coming working
    def chunk_emitter():
        # How to split() will depend on the data since this is a dict
        for chunk in split(dict_of_result, CHUNK_SIZE):
            yield chunk

    headers = {'Content-Disposition': 'attachment'}
    return StreamingResponse(chunk_emitter(), headers=headers, media_type='application/json')

More examples here: How to download a large file using FastAPI?.

Returning response in FastAPI takes a long time and blocks everything

2 Answers2