It seems to me that there are great benefits to be had if you can keep the transactional integrity of direct connections to a relational database like PostgreSQL. The performance and scalability is often better than people expect because the data stays closer to CPUs and it avoids moving too much across multiple nodes on the relatively slow and unreliable network.
In a lot of cases, there are natural separation lines such as between groups of customers you can use to shard and scale things up. Unless you are building something like a social network where everything is connected to everything, you don't need a database that runs over a large cluster or clustered queues in between components. These are often just more moving parts that can break.
The benefits of transactional queues in your database are hard to overstate; commit the result of the task in the same transaction as you commit the queue update. Don't worry about idempotency, lost messages, or duplicate messages.
I suspect the advice to avoid it because of performance has become invalid for all but extreme use-cases. My company has dozens of high activity 1-100M item queues in single postgresql databases. It works great.
If you segment your databases into micro services, it think you loose transactions. Say two services talk via DB queue. You'll need to store the DB for each service in the same management server, but different schemas. If you ever want to move your DB for one service, you have to introduce distributed transactions. That it drop them.
In a lot of cases, there are natural separation lines such as between groups of customers you can use to shard and scale things up. Unless you are building something like a social network where everything is connected to everything, you don't need a database that runs over a large cluster or clustered queues in between components. These are often just more moving parts that can break.