One of the issues with modelling queue semantics over a database is performance. All that locking, key lookups and mutating of B trees is expensive.
The latest generation of durable messaging systems that offer queue semantics do so by modelling those semantics over a distributed, replicated log, such as Apache Pulsar and RabbitMQ's new replicated queue type called Quorum Queues.
A queue is different to a log in that reading from a queue is destructive, but reading from a log is not. So if I have two applications (shipping and auditing) that want a queue with all the shipping orders in, then each needs their own queue - so they don't compete over the messages. Whereas a log can be read by both, but both need to track their independent position in the log.
Apache Pulsar offers queue semantics to shipping and auditing by storing the shipping orders in one distributed log (a topic) and creating two separate subscriptions (also logs) that track the position (like Kafka consumer offsets). The destructive read of a queue is simulated by advancing the cursor (offset) of the subscription. The performance improvement this append-only log data structure offers compared to a mutable B-tree of the RBDMS is massive.
Quorum queues do it a different way, but still modelling queue semantics over a log.
Of course some future RDBMS storage backend wouldn't have to use B-trees and read_past locking etc, it could also use a log based data structure for message storage too.