For when you inevitably need to expose the ids to the public the uuids prevent a number of attacks that sequential numbers are vulnerable to. In theory they can also be faster/convenient in a certain view as you can generate a UUID without needing something like a central index to coordinate how they are created. They can also be treated as globally unique which can be useful in certain contexts. I don't think anyone would argue that their performance overall is better than serial/bigserial though as they take up more space in indexes.
Most large scale web applications spend their time reading and writing data, both to/from clients and to/from other remote services such as databases. You don't need thousands of hosts. Stackoverflow famously ran 9 server instances in 2016 with 7 dedicated to the primary sites.
Unlike postgres, Oracle and Sql server can support thousands of connections but they see performance degradation at a certain point. So I have never seen them crash from too many connections(Although they definitely get slower.).
To be clear pgbouncer does not add connections to postgres or remove the connection bottleneck. Its still there under the covers. If you are saturating your connections it will not be able to improve on throughput. It sounds like you need a different architecture to allow for queueing work. The approach pgbouncer takes may actually reduce performance overall as it will intermix work on the pg instance which, if you are already saturating the database, will slow down other operations overall.
Don't spin up 50 pods. You have outscaled your database. You can't make IO operations faster by throwing more workers at it and you can only have so many connections working at once. As a side note if your application is a typical IO bound web app its very unlikely you can process enough transactions to effectively use 50 workers in a single region.
Is this because your default approach is to horizontally scale or because you have tried other options? Logically scaling vertically a single server with pooled connections should have a better effect than horizontally scaling to multiple servers that need to share connections through a proxy. The proxy doesn't magically increase the number of connections available to the database. It will add overhead to all your database interactions though.
This still doesn't really make sense to me. You can't scale an application that relies on a database heavily to this level because your fundamental constraint IS the database. If you are already hitting your max number of connections with a small number of applications there are no benefits to further horizontal scaling. You are just passing the buck around because only a limited number can hit the database at any one time.
PgBouncer has always left me confused in the world of application level connection pooling. I have never quite understand the value of it if we are already using connection pools in our applications. I don't want to pool connections to another pool.
It depends. The come and go based on legislation and the current fixed interest rates. They were very popular for a bit but as the fixed rates kept dipping they seemed to mostly vanish. They seem to only really be popular when financial institutions can easily resell them as they tend to target the lower income brackets.
Auto generated client code is nice in theory. In practice I find it only really useful as a starting point. There are enough choices to be made in writing even a simple HTTP api that its unlikely that a generic tool will generate useful code for a given application. This could include library usage (http client, serialization, logging, DI integration), async vs sync, logging requirements, tooling support for generated code. If you are in a language which has established std libraries and patterns this is less of a problem, but in something like Java that has evolved in all these areas over the years it can be a real problem.
The tooling is far from simplistic to setup and the available options can be overwhelming. Its all the pain of the JS stack but with less easily available help and tooling that produces less helpful error messages.
You can use a redis to distribute messages via PUB/SUB. The sockets subscribe to the events that are relevant to them. It can handle thousands of messages a second in a local environment and probably more in a dedicated hosting environment.