But I've always struggled to understand the use cases of pgbouncer. In most cases, you want to front your database with a service and don't want many clients connecting to it directly. And I've seen fairly large workloads easily managed over a small number of connections, so a good client-side pool implementation should easily suffice. In what sort of situations does pgbouncer come into play?
As you say, you can write a service to stand in front of the DB and mediate access, but PgBouncer is already written AND production ready
Also, 20 connections per service process seems pretty high. Unless there are many long running queries and clients are willing to wait long times for responses.
The harder part is ensuring people don’t hold onto connections during long-running operations. In some client libraries, it’s easy to accidentally hold one open. That plus autoscaling results in ticking bombs that are hard to catch with automated tests
There are some architectures that tear-down all resources at the end of every requests, e.g. PHP, or lambda-like things, so you can't use a connection pool, because the "pool" would only exist for that request.
pgbouncer can be used locally too, however.
E: Oh, and how could I forget all the mod_php based scripts for these web-app front-ends.
During busy time, Apache Airflow can launch hundreds of workers, each of them need at least 1 connection to Postgresql to save its result. Because each worker run in its own process due to Python's lack of support for threading, client-side connection pool cannot be use effectively.