I really hope it can replace PgBouncer. We've spent way too much time getting it running in ECS, mostly due to us needing a separate PgBouncer service for the primary and replica RDS instances. And then a third ECS service running HAProxy in TCP mode to load balance between the PgBouncer services, to provide a read-only endpoint that balances between the primary and replica. Add issues like: having to patch in HAProxy TCP keep-alive
parameter support (it only allows enabling/disabling it, leaving params up to the OS, which you can't control on ECS Fargate, _and_ the AWS NLB having a 350-second TCP flow idle timeout - good luck with long-running queries); AWS NLBs having horrible timing issues when handling target group changes... it was not a great experience.
We also set up pgbouncer, but after testing we realized that running it in a container added extra latency so went with ec2 instances behind ASG.
For load blanacing we used ELBv1, we also tried to use NLB, but 350 second timeout, and broken behavior when after timeout it just silently closes the connections was a no-no. ELBv1 doesn't silently close connections, and idle timeout can be set to 1h. After that we configured pgbouncer to close idle connections after 55 minutes. ELBv1 also doesn't have the timing issues with health checks.
IMO NLB is maybe performant, but that's the only good thing about it, in every other category it is crap and for this use case ELBv1 isn't a bottleneck.
If you will plan to revise your setup, be aware that pgbouncer is single threaded so throwing multicore machines at it won't do much good. If CPU ends up being a bottleneck it is better to use a large single core instance and then adding more instances. The only problem with that is that as you're adding more instances you're increasing number of connections to the database, but you could have additional process that could monitor ASG and set the number of instances and dynamically adjust number of server-side connections (doesn't require restarting pgbouncer).
BTW: Since version 1.12.0 you can also run multiple pgbouncer instances listening on the same port so that can help make a better use of extra cores if you chose multicore instances.
I, too, am curious how it'll fare compared to DIY PgBouncer. Related, I am building something related that solves a superset of these classes of struggles. I'd love to pick your brain, if you're interested! Do you mind shooting me a message? (email in profile)
If I were to do it again, I'd consider either that or building it directly on EC2, preparing the AMI with Packer or something, and handling scaling with an autoscaling group.
As an aside, why isn't pgbouncer basically built into Postgres, at least as an option? I'm sure there's a good reason, but as a lay-user, pgbouncer seems to just do connections better than Postgres. It's just a pain to both with it when cloud databases are so easy to launch in comparison.
Culture in Pg community once favored separate, single purpose solutions. This was the replication story for a long time. So here's hoping they'll build-in something to help with the high costs of individual connections.
