My go-to has been replicas for each use case, with well defined semantics for replication lags. I'm working on something that does most of this seamlessly (transactional,search, columnar & time series, vectors and queues) without having to bother about extension management, replication setup or tuning.

Hopefully there is some value in this - one click multipurpose postgres fleet.