Postgres is great as a queue, but this post doesn't really get into the features that differentiate it from just polling, say SQL Server for tasks.

For me, the best features are:

  * use LISTEN to be notified of rows that have changed that the backend needs to take action on (so you're not actively polling for new work)
  * use NOTIFY from a trigger so all you need to do is INSERT/UPDATE a table to send an event to listeners
  * you can select using SKIP LOCKED (as the article points out)
  * you can use partial indexes to efficiently select rows in a particular state

So when a backend worker wakes up, it can:

  * LISTEN for changes to the active working set it cares about
  * "select all things in status 'X'" (using a partial index predicate, so it's not churning through low cardinality 'active' statuses)
  * atomically update the status to 'processing' (using SKIP LOCKED to avoid contention/lock escalation)
  * do the work
  * update to a new status (which another worker may trigger on)

So you end up with a pretty decent state machine where each worker is responsible for transitioning units of work from status X to status Y, and it's getting that from the source of truth. You also usually want to have some sort of a per-task 'lease_expire' column so if a worker fails/goes away, other workers will pick up their task when they periodically scan for work.

This works for millions of units of work an hour with a moderately spec'd database server, and if the alternative is setting up SQS/SNS/ActiveMQ/etc and then _still_ having to track status in the database/manage a dead-letter-queue, etc -- it's not a hard choice at all.