I'm pretty familiar with setting up Express servers and running a connection pool on that one instance, usually just a simple VPS which connects to an outside database (AWS RDS). The connection pool on the instance would have near the max connections limit of the DB (postgresql). This is the current setup:
One VPS with a single database
Recently I've been tasked with making one of our API's highly available across multiple zones, this would include at least 2 servers connecting to a centralized database. Would I reduce the pg.Pool max connections and split it in half? Let's say max connections on the database is 100, since I will have two instances should I set them to 50 each? (Ideally less for performance reasons but I hope you get the point).
Multiple Servers, smaller connection pool in each
How would this scale if I had an auto-scaling group in the future? Just keep reducing the max connections by the number of instances? Or do I have to setup yet another VPS that handles connection pooling all by itself, and then have high availability on that one which would add even more costs?
Thank you for your help!
First, the size of the connection pool shouldn't be set to exhaust max_connections, but should be limited so that the database cannot be overloaded, even if all the connections are active.
If you have several instances of your application, you probably need to put a connection pooler between these servers and the database, because otherwise connection pooling would not be effective.
I recommend pgBouncer for that, in transaction pooling mode, if you can.
