One of our clusters has well over 500 databases fronted by pg_bouncer.
We get excellent connection "flattening" using pg_bouncer with per-database connection spikes dealt with through a reserve pool.
What if you see at least 4 connections being established by each client during peak ? And if you serve 4 or 2 connections per each DB, then you are creating 1000 or more reserved connections with 500 DBs in a cluster.
Does every database spike at the same time?
The nice thing about separate databases is that it is easy to scale horizontally.
Agreed. But, how about autovacuum ? Workers shift from DB to DB and 500 clusters means you may have to have a lot of manual vacuuming in place as well.
Why would having difference schemas in different DBs change your manual vacuuming needs? And if anything, having separate DBs will make autovacuuming more efficient, as it keeps the statistics collectors stats files smaller.
