how to limit the number of connections based on a context var

[gjeusel]

Hello there,

I would like to limit the number of simultaneously used connections per request on my pool in my ASGI app.
Ideally, I would avoid opening and closing a connection pool at each request for performances reason.
To do so, I guess I should be using a context vars.

Would you be so kind as to point me in the right direction to implement such a logic ?

Here is a little script to help off the discussion:

import asyncio
import uuid
from contextvars import ContextVar

import asyncpg

request_id: ContextVar[str] = ContextVar("request_id", default="default")
max_conns_per_request = 10


async def read(pool: asyncpg.Pool) -> int:
    async with pool.acquire() as conn:
        print(f"Acquiring conn {pool.get_size()} / {pool.get_max_size()} ...")
        return await conn.fetchval("SELECT 1")


async def _handle_request(pool: asyncpg.Pool) -> None:
    request_id.set(str(uuid.uuid1()))

    # TODO: some logic to bind the max number of allowed // connections for this request_id

    reads = [read(pool) for _ in range(20)]
    await asyncio.gather(*reads)

    # It should not go above 10 / 40 :
    print(f"Acquired {pool.get_size()} / {pool.get_max_size()} conns for one request.")


def main() -> None:
    loop = asyncio.get_event_loop()

    db_uri = "postgresql://postgres:postgres@localhost:5432/postgres"
    pool = loop.run_until_complete(asyncpg.create_pool(db_uri, min_size=0, max_size=40))
    loop.run_until_complete(_handle_request(pool))


if __name__ == "__main__":
    main()

[eirnym]

I can't imagine the situation such this can happen in normal application. I'd say it's a problem with architecture if it happens.

If this is the only solution I'd do this one of this depending on specific situation, possibility of architecture change and other parameters:

• use a one connection per a request, passing it into read function. This can do or fail depending on implementation. Could be dangerous :)
• processing it in batches per connection
• in long run I'd offload this function to a separate server pool to make main process scalable behind a load balancer like nginx.

[gjeusel]

Thanks for your answer !

I guess I'll go for instantiating a second pool dedicated for this connection heavy query.
It might not restrict the number of connections used by a specific request, but still it will not impact the other types of queries.