deployment suggestions to handle many simultaneous requests

[hrodmn]

I am running stac-fastapi with pgstac in a serverless Azure Function App. Most of the time this is working really well, but sometimes when too many requests get thrown at the API it chokes with database connection errors (e.g. 16 parallel processes that each send occasional GET requests).

The PostgreSQL database is running on Azure, too, and the max_connections parameter is set to 859. In the PostgreSQL logs I see this error: remaining connection slots are reserved for non-replication superuser connections.

I haven't seen anything published about deployment best practices so I wonder if anyone here has any suggestions! Is it a bad idea to run stac-fastapi as a serverless application? Is there a better way to set it up to take better advantage of pooled database connections?

Thanks in advance for any suggestions!

example API error log

Exception: ConnectionResetError: 
Stack:   File "/azure-functions-host/workers/python/3.8/LINUX/X64/azure_functions_worker/dispatcher.py", line 469, in _handle__invocation_request
    call_result = await self._run_async_func(
  File "/azure-functions-host/workers/python/3.8/LINUX/X64/azure_functions_worker/dispatcher.py", line 748, in _run_async_func
    return await ExtensionManager.get_async_invocation_wrapper(
  File "/azure-functions-host/workers/python/3.8/LINUX/X64/azure_functions_worker/extension.py", line 147, in get_async_invocation_wrapper
    result = await function(**args)
  File "/home/site/wwwroot/stacAPI/__init__.py", line 33, in main
    return await handle_asgi_request(req, context)
  File "/home/site/wwwroot/stacAPI/__init__.py", line 27, in handle_asgi_request
    asgi_response = await AsgiResponse.from_app(app, scope, req.get_body())
  File "/azure-functions-host/workers/python/3.8/LINUX/X64/azure/functions/_http_asgi.py", line 80, in from_app
    await app(scope, res._receive, res._send)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/fastapi/applications.py", line 270, in __call__
    await super().__call__(scope, receive, send)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/starlette/applications.py", line 124, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/starlette/middleware/errors.py", line 184, in __call__
    raise exc
  File "/home/site/wwwroot/.python_packages/lib/site-packages/starlette/middleware/errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/stac_fastapi/api/middleware.py", line 76, in __call__
    await self.app(scope, receive, send)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/starlette/middleware/cors.py", line 84, in __call__
    await self.app(scope, receive, send)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/brotli_asgi/__init__.py", line 81, in __call__
    await br_responder(scope, receive, send)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/brotli_asgi/__init__.py", line 126, in __call__
    await self.app(scope, receive, self.send_with_brotli)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/starlette/middleware/exceptions.py", line 75, in __call__
    raise exc
  File "/home/site/wwwroot/.python_packages/lib/site-packages/starlette/middleware/exceptions.py", line 64, in __call__
    await self.app(scope, receive, sender)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
    raise e
  File "/home/site/wwwroot/.python_packages/lib/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
    await self.app(scope, receive, send)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/starlette/routing.py", line 680, in __call__
    await route.handle(scope, receive, send)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/starlette/routing.py", line 275, in handle
    await self.app(scope, receive, send)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/starlette/routing.py", line 65, in app
    response = await func(request)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/fastapi/routing.py", line 235, in app
    raw_response = await run_endpoint_function(
  File "/home/site/wwwroot/.python_packages/lib/site-packages/fastapi/routing.py", line 161, in run_endpoint_function
    return await dependant.call(**values)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/stac_fastapi/api/routes.py", line 57, in _endpoint
    await func(request=request, **request_data.kwargs()), response_class
  File "/home/site/wwwroot/.python_packages/lib/site-packages/stac_fastapi/types/core.py", line 556, in landing_page
    collections = await self.all_collections(request=kwargs["request"])
  File "/home/site/wwwroot/.python_packages/lib/site-packages/stac_fastapi/pgstac/core.py", line 47, in all_collections
    async with pool.acquire() as conn:
  File "/home/site/wwwroot/.python_packages/lib/site-packages/asyncpg/pool.py", line 998, in __aenter__
    self.connection = await self.pool._acquire(self.timeout)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/asyncpg/pool.py", line 838, in _acquire
    return await _acquire_impl()
  File "/home/site/wwwroot/.python_packages/lib/site-packages/asyncpg/pool.py", line 823, in _acquire_impl
    proxy = await ch.acquire()  # type: PoolConnectionProxy
  File "/home/site/wwwroot/.python_packages/lib/site-packages/asyncpg/pool.py", line 137, in acquire
    await self.connect()
  File "/home/site/wwwroot/.python_packages/lib/site-packages/asyncpg/pool.py", line 129, in connect
    self._con = await self._pool._get_new_connection()
  File "/home/site/wwwroot/.python_packages/lib/site-packages/asyncpg/pool.py", line 521, in _get_new_connection
    con = await connect_utils._connect_addr(
  File "/home/site/wwwroot/.python_packages/lib/site-packages/asyncpg/connect_utils.py", line 773, in _connect_addr
    return await __connect_addr(params, timeout, True, *args)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/asyncpg/connect_utils.py", line 825, in __connect_addr
    tr, pr = await compat.wait_for(connector, timeout=timeout)
  File "/home/site/wwwroot/.python_packages/lib/site-packages/asyncpg/compat.py", line 56, in wait_for
    return await asyncio.wait_for(fut, timeout)
  File "/usr/local/lib/python3.8/asyncio/tasks.py", line 494, in wait_for
    return fut.result()
  File "/home/site/wwwroot/.python_packages/lib/site-packages/asyncpg/connect_utils.py", line 700, in _create_ssl_connection
    new_tr = await loop.start_tls(
  File "/usr/local/lib/python3.8/asyncio/base_events.py", line 1200, in start_tls
    await waiter

[mmcfarland]

Hi @hrodmn -- are you running on Azure Postgres for Flexible Server? If so, you can enable pgBouncer and try to limit the number of actual database connections. Even if you're not running Flexible Server, setting up your own pgBouncer is probably the first thing to try.

[hrodmn]

Thanks for the suggestion, @mmcfarland!

[hrodmn]

I wound up deploying my STAC services on AWS using eoAPI. The database/function arrangement is very robust - if you need to stand up a STAC API I highly recommend it.