Should PostgreSQL connections be pooled in a Python web app, or create a new connection per request?
Pooling seems to be totally impossible in context of Flask, FastAPI and everything relying on wsgi/asgi dedicated servers with multiple workers.
Reason for this behaviour is simple: you have no control about the pooling and master thread/process.
A pooling instance is only usable for a single thread serving a set of clients - so for just one worker. Any other worker will get it's own pool and therefore there cannot be any sharing of established connections.
Logically it's also impossible, because you cannot share these object states across threads/processes in multi core env with python (2.x - 3.8).
I'm building a web app in Python (using Flask). I do not intend to use SQLAlchemy or similar ORM system, rather I'm going to use Psycopg2 directly.
Should I open a new database connection (and subsequently close it) for each new request? Or should I use something to pool these connections?