I've found that co-locating my queue with my application data enables a very powerful pattern around persisting actions and queuing up callbacks -- especially useful for smaller applications where scale isn't as much of a factor. It means I can persist both my data and my background jobs in a single transaction, and if something goes wrong during that transaction they both rollback (before the jobs are picked up by any workers). No need to coordinate two different systems (which can be full of pitfalls).

Yes for infrequent yet high value tasks where consistency is paramount, DB queues are king. The alternatives using an externalized queue is either non-transactional without failure handling (yet everybody pretends it is), transactional with 2PC where recovery was never thought through (or tested), or devolves to having the equivalent of a DB queue anyway to track work with re-insertion of work into the primary queue.

Define "moderate amount of load".

More importantly, how many existing Postgres users would fall in that bucket?

Right. I use a MySQL-based queue for a moderate amount of load, but that's because it's relatively few tasks that do relatively much work per task. But if your idea of moderate load is hundreds of tasks per second that individually do very little work each, then you may be right.