It's important to distinguish between the use cases. Queues, streams, logs, databases, etc. are different kinds of tools you can use, and what the right tool is depends on your semantics.
For example: Message queues are good for work that must be done in strict order where you want to deal with one message at a time. They aren't such a great fit for large batch movement of data, like logs or high volume events, because having a per-message acknowledgement state requires a lot of round trips over the network that simply isn't needed; you want to treat the entire bulk of the flow to carve out big chunks of it, because CPUs wnd networks and disks are more efficient when doing the same operation over large amounts of data in one go.
If you are executing "tasks" (like image processing, ML inference, webhooks), ordering by insertion order might not be the right choice, either. Sometimes you want to coalesce (dedupe by key). Sometimes you want to ensure the processing for a key (e.g. a customer ID) is done in the same process and not randomly distributed over all your workers. Sometimes you want delivery to be strictly sequential, requiring an exclusive worker rather than massively parallel fan-out. And so on.
Where I work, we use a mix of things depending on the application. I am a big fan of NATS. It's not itself a message queue, but its primitives can be combined to handle all sorts of behaviors. Core NATS is more like ephemeral pub/sub, while Jetstream gives you durable, highly available Kafka-like streams.
I like combining queues with database state. Use the queue as an efficient way to order items (like jobs or events) for massively scalable distribution, and use the database to store the current state of things.
For example, imagine you're delivering webhook messages. We first store the message in the database with the state "pending", then write an event to the queue about it. The worker receives the event, double-checks its state is still "pending", then executes it. If delivered, mark as "done" and ack the message. Otherwise, mark as "failed" and create a new queue message to retry. This way, you have durable state in a solid database, and the queue is an efficient way to coordinate the workers. (There's a bit more work here to ensure consistency, but this is the gist of it.)
Core NATS is fantastic as a communication primitive between ephemeral processes. You can use it for RPC, for lightweight broadcasts (e.g. reload config everywhere), even for things like leases or caching or similar. Jetstream is like Kafka but more flexible; for example, each message has a wildcard subject that can be filtered on, so different consumers can very efficiently filter a big, commingled stream by interest. In Jetstream streams, messages have per-consumer ack/nack state in addition to a position, so you're not limited to Kafka's linear "position". Overall, a superb data model, and very easy to manage as infra.
One weak point with NATS is a maximum message size of 10MB. This means that you sometimes have to invent your own chunking if your application needs to send larger payloads. Doing this opens up some cans of worms, so I honestly wouldn't recommend it. For large batch stuff, Redpanda is a better option.