“Use Postgres for everything” is a great philosophy at low/medium scale to keep things simple, but there comes a scaling point where I want my SQL database doing as little possible.

It’s basically always the bottleneck/problem source in a lot of systems.

Of course. The flip side is that many, many more people are in the "low/medium scale" zone than would self report. Everyone thinks they're a scale outlier because people tend to think in relative terms based on their experience. Just because something is larger scale than one is used to, doesn't mean it's high scale.

Yes. For example you'll typically have a "budget" of 1-10k writes/sec. And a single heavy join can essentially take you offline. Even relatively modest enterprises typically need to shift some query patterns to OLAP/nosql/redis/etc. before very long.