Rabbit MQ and Elastic Search for a public facing site. The dedicated queue for workers to denormalize and push updates. To elastic. Why, because the $10k/month RDBMS servers couldn't handle the search load and were overly normalized. Definitely a hard sell.
I've also seen literally hundreds of lambda functions connecting to dozens of dynamo databases.
I'm firmly in the camp of use an RDBMS (PostgreSQL my first choice) for most things in most apps. A lot of times you can simply apply the lessons from other databases at scale in pg rather than something completely different.
I'm also more than okay leveraging a cloud's own MQ option, it's usually easy enough to swap out as/if needed.
You just need a little bit of appropriate index selection and ability to read the output of EXPLAIN ANALYZE to do so.
There are probably use cases where this doesn't hold, but I found in general that it is beneficial to stick to Postgres for this, especially if you want some ability to query using relations.