Something to bear in mind is that if you have a bug or crash in your task handler that causes a rollback, another worker will likely try to grab the same task again, and you might end up clogging all of your workers trying the same failed tasks over and over again. We use a hybrid approach where a worker takes responsibility for a task atomically using SKIP LOCKED and setting a work-in-progress flag, but actually does the bulk of the work outside of a transaction; you can then run arbitrary cleanup code periodically for things that were set as work-in-progress but abandoned, perhaps putting them into lower-priority queues or tracking how many failures were seen in a row.
Postgres is absolutely incredible. If you are at anything less than unicorn scale, outside of analytics where columnar stores are better (though Citus apparently now has a solution for this - https://www.citusdata.com/blog/2021/03/06/citus-10-columnar-...), and highly customizable full-text search (it's hard to beat Elastic/Lucene's bitmap handling), there are very few things that other databases can do better if you have the right indices, tuning, and read replicas on your Postgres database.
Something to bear in mind is that if you have a bug or crash in your task handler that causes a rollback, another worker will likely try to grab the same task again, and you might end up clogging all of your workers trying the same failed tasks over and over again. We use a hybrid approach where a worker takes responsibility for a task atomically using SKIP LOCKED and setting a work-in-progress flag, but actually does the bulk of the work outside of a transaction; you can then run arbitrary cleanup code periodically for things that were set as work-in-progress but abandoned, perhaps putting them into lower-priority queues or tracking how many failures were seen in a row.
Postgres is absolutely incredible. If you are at anything less than unicorn scale, outside of analytics where columnar stores are better (though Citus apparently now has a solution for this - https://www.citusdata.com/blog/2021/03/06/citus-10-columnar-...), and highly customizable full-text search (it's hard to beat Elastic/Lucene's bitmap handling), there are very few things that other databases can do better if you have the right indices, tuning, and read replicas on your Postgres database.