Heh, I love this topic so much I spent weeks writing a post on it Understanding Connections & Pools
The bad news is there’s no global correct answer, it depends on your app and load, but the good news is that’s there’s some rules of thumb you can use.
Each connection takes about 15MB in your instance’s RAM, give or take a few MB. The RAM also holds a lot of your dataset, preferably the entire thing or at least all the indexes. I love PgHero: GitHub - ankane/pghero: A performance dashboard for Postgres because it surfaces this info really well, and it’s easily deployable on Fly using Docker. So this tells you how many connections you can reasonably support on your PG instances:
(RAM - data/index size - baseline OS/PG) / 15.
When it comes to PGBouncer, the number above is what you’d want to configure as
max_db_connections — crucially, if you have multiple instances of PGBouncer, you’ll need to make sure the
max_db_connections across all of them is less than or equal to the total your database can support.
You can install pgBouncer as a container in its own Fly app, there are few Docker distributions, but I would just choose the Bitnami one: Docker Hub
If you run this you can then configure your connection pool on the app to be pretty high: pgBouncer will hand out cheap fake connections like candy, and map them on to a real 15MB connection only when the app is actually trying to run a query. Depending on the nature of the connection pool and the way the application checks out connections, you can often make your app servers think they have 10x to 100x more connections that you actually have.
Watch out for the
pool_mode though: this will only work on a more aggressive pool mode than the default. The
session mode maps 1 client connection on to 1 real connection, even if the client is not doing anything. This give your almost no gains, but is necessary if you’re doing things like prepared statements or advisory locks without tracking your connections on the client side.
If you can be careful about / disable your prepared statements and advisory locks and other session level operations, you can switch the mode to
transaction, which is much better because even if your clients checkout a connection, they’ll get a cheap fake that’s mapped on to the real connection only when a transaction starts, and for the duration of that transaction.
Or you can go all the way to
statement level, if you know exactly what you’re doing with this, and works great for reporting systems etc.
You can also adjust all this to your read replicas — if you run a separate set of pgBouncers for the read replicas only, you can then multiply your total max connections by the number of read replicas you have, and you can probably do
statement mode on the replica group.
So yeah, once you have pgBouncer set up and configured, you can raise your
connection_limit on Prisma pretty high - maybe have it equal to the max number of requests you expect to handle concurrently on that instance.