[hamandcheese]

You don't have to be that big for mixed workloads to cause issues for a do-everything PG instance.

Imagine a scenario where read-heavy but infrequent search queries end up pushing, say, your sessions table out of cache.

Postgres has no facilities for earmarking cache for one table vs another, so the noisy neighbor problem is real, and hard to fix. You can throw money/ram at it, but that's needlessly expensive if you have some workloads that don't require that level of performance.

[LunaSea]

You can get 512GB RAM and 96 core machines for $1000 / month and at that point you can throw any workload on it.

[ForHackernews]

Parent comment already mentioned read-replicas.

The main problem I've seen is companies allowing tables to grow enormous because they never partition (by year, for instance) or archive out old stale data.