I once was the CTO of a company that serviced about 100k requests per day across all our services. We grew to millions and eventually 10's of millions, but, somewhere along the way, an engineer on our team decided he wanted to build a queue off LISTEN/NOTIFY semantics in order to take advantage of strong consistency with the rest of our data model, which seemed reasonable given LISTEN/NOTIFY is not that hard to understand and we did need consistency guarantees for this workflow and this would remove the need for yet another place data got stored and transfered.
In practice, this eventually ended up being very awkward because extending the functionality (since we had "built" it) and had to work around internal pg semantics (we should have just moved off much sooner). It also did not scale well. We ended up getting a ton of disk contention on our RDS instance in non-obvious ways, and the vacuum runs on that table was a nightmare. Additionally, it was hard to get other engineers to really debug and take ownership of the system because they automatically viewed a queue (very easy to understand) implemented in a foreign way (off pg internals) as something "scary". It was emotional, not rational, but we are emotional beings, and I do not blame them. These were good engineers with a lot of other things on their plates.
Obviously, this is all hand-wavy without discussing the internal schema, indeces, etc. that we had set up, but my main takeaway with core technology from this experience was to always reach for the dumb, expected, simple thing. Even if it adds another moving piece in the infra stack. Unless I need very strong data consistency guarantees, it's always better to use something like SQS, Redis queues, etc. where the understanding is that it is just a queue (or at least the API contract suggests simplicity), and then everything needs to work around it.
The fewer mechanistic responsibilities per core data store, the better in my experience.