Comment by oulipo2
1 day ago
How do you know when/if it's justified to add additional complexity like PgDog?
Is there a number of simultaneous connection / req per sec that's a good threshold?
Is it easy on my postgres instance to get the number of simulataneous connections, for instance if I simulate traffic, to know if I would gain anything from a connection pooler?
I would say, over 100 Postgres connections, consider getting a connection pooler. Requests per second is highly variable. Postgres can serve a lot of them, as long as you keep the number of server connections low - that's what the pooler is for.
You can use pgbench to benchmark this on local pretty easily. The TPS curve will be interesting. At first, the connection pooler will cause a decrease and as you add more and more clients (-c parameter), you should see increasing benefits.
Ultimately, you add connection poolers when you don't have any other option: you have hundreds of app containers with dozens of connections each and Postgres can't handle it anymore, so it's a necessity really.
Load balancing becomes useful when you start adding read replicas. Sharding is necessary when you're approaching the vertical limit of your cloud provider (on the biggest instance or close).
Okay, on my side I have a server for my API, using Drizzle, I guess it already uses some kind of pooling (or at least it asks me to instantiate a pg.Pool, not sure if that's a lightweight connection pooler on the server side), and only a couple of workers with a Drizzle pool each, so I guess I'm far enough from that limit
Do connection increase mostly as you increase microservices / workers, or is it something more related to how many endusers of your service (eg connections on your webserver) you have?
That's exactly right, it's both of those. More containers / services means more connections to the DB, which themselves need to be pooled. More requests to the app require more connections as well.