Returning response in FastAPI takes a long time and blocks everything [duplicate]

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

Answer 1

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

1. Running the query returning a giant result. Seems like this is not the problem here
2. 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?.