The issue is language ecosystems that don't use client side connection pooling because they're single threaded (node, Python). So scaling up the number of web server threads means scaling the number of Postgres processes, which are expensive.
Yes but they can’t be shared. If you size your pool to hold 10 connections (because asyncio can handle that and more), then deploy with uvicorn —-workers 4 (which should match the number of cores on your app server), and then deploy to 3 app servers (for redundancy) then you’ve now got 120 open connections to Postgres. Run anything more than a trivial query and you’re easily at gigabytes of ram.
120 connections is likely fine. If it's not, you could do only 5 connections per worker. This is more of a problem if you have uneven load on the workers though.
I’m not claiming it’s not fine, but it is a surprising consideration for a relatively small deployment. You have to start planning around Postgres’ architecture for anything larger, hence the solution in PgBouncer.
Comments
The issue is language ecosystems that don't use client side connection pooling because they're single threaded (node, Python). So scaling up the number of web server threads means scaling the number of Postgres processes, which are expensive.
Python has threads and connection pools work fine on async workers as well.
Yes but they can’t be shared. If you size your pool to hold 10 connections (because asyncio can handle that and more), then deploy with uvicorn —-workers 4 (which should match the number of cores on your app server), and then deploy to 3 app servers (for redundancy) then you’ve now got 120 open connections to Postgres. Run anything more than a trivial query and you’re easily at gigabytes of ram.
120 connections is likely fine. If it's not, you could do only 5 connections per worker. This is more of a problem if you have uneven load on the workers though.
I’m not claiming it’s not fine, but it is a surprising consideration for a relatively small deployment. You have to start planning around Postgres’ architecture for anything larger, hence the solution in PgBouncer.
This is more app instances than 99% of deployments. Most apps and websites run a single server.