Currently API requests typically make one or more spurious database connections during handling, that are then closed immediately. For larger loads, the creation/closing of DB connections is needless overhead.
It appears that psycopg provides async connection pooling:
https://www.psycopg.org/psycopg3/docs/advanced/pool.html#basic-connection-pool-usage
By using this we could reduce unnecessary strain on the database while also capping the number of connections (mitigating possible crashes due to too may connections). The tradeoff is that during heavy load, await statements could make some requests take a long time, waiting until a connection is available. But that is probably the correct behavior.
For this issue let's research if psycopg.AsyncConnectionPool can work for us.
Currently API requests typically make one or more spurious database connections during handling, that are then closed immediately. For larger loads, the creation/closing of DB connections is needless overhead.
It appears that
psycopgprovides async connection pooling:https://www.psycopg.org/psycopg3/docs/advanced/pool.html#basic-connection-pool-usage
By using this we could reduce unnecessary strain on the database while also capping the number of connections (mitigating possible crashes due to too may connections). The tradeoff is that during heavy load,
awaitstatements could make some requests take a long time, waiting until a connection is available. But that is probably the correct behavior.For this issue let's research if
psycopg.AsyncConnectionPoolcan work for us.