> But when PG is running out of open connections or has already consumed all available CPU even the simplest query will struggle.

I don't think it is reasonable to assume or even believe that connection exhaustion is an issue specific to Postgres. If you take the time to learn about the topic, you won't need to spend too much time before stumbling upon Redis and connection pool exhaustion issues.

You can have a separate connection pool for 'cache' requests. You shouldn't have too many PG connections open anyway, on the order of O(num of CPUs).