It’s made it so clear to me that so much of what you read on HN about the latest and greatest scaling tricks are only relative to a tiny tiny fraction of business.
They are really underutilized by many projects.
Not to say any other software is bad, just that keeping the stack simple can help small teams move quickly and not get bogged down fighting fires. Also, the paths to scale up the major RDBMSes are well documented and understood. With a newer service and many interacting systems in your back end you end up having to be a pioneer (which takes time away from implementing new features).
Probably the most useful things for me was learning relational algebra in college, and having been thrown on the deep end on a team that was very SQL heavy (not withstanding attempting to debug Oracle PL/SQL syntax errors while pulling your hair out about a missing closing parenthesis -- of which isn't the problem).
The usual challenge seems to be fetching data from external services or performing complex business that may conditionally load things -- things that can be awkward in procedural SQL. At the end of the day, you're building a messy ad-hoc dependency graph that is being manually executed very inefficiently. Would be better to just have your code just describe the dependency graph and treat each value transparently as a promise/future and then have a separate engine execute it.
Anyhow, something something monads with lipstick, I digress...
I'd say the biggest overhead is adding Redis if you don't already have it, and that addition's difficulty will vary based on how you host Redis. We use Elasticache on AWS, so just a few clicks and set it in the same VPC.
I guess the real question comes down to how you feel about an extra moving part. Redis is probably the part of our system that has had the least hiccups (very much set and forget and no resource issues so far), but I can understand in the case where you'd rather not add more than a DB.
I'd say it's just as easy to setup as Postgres. Elastic search I hear is a pain, though I have no personal experience.
Agreed, but it's extremely common to need more resilience and/or better multi-DC support than Postgres can provide, and if you've implicitly built your datamodel on Postgres' guarantees then you might find it very hard to migrate to something more distributable. Frankly true master-master HA should have been table stakes for any serious datastore for at least the past decade, as soon as it became clear that cloud was a thing or even before that. (Redis also falls down on that front in terms of what it offers itself, but it's much easier to migrate to something more distributable, IME).
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.
I haven't seen many if any projects that didn't require some architectural rethinking over their lifetime. I have seen more, that where arguably over engineered in the beginning, but then never lived long enough to actually benefit from it.
Not saying everyone should use Postgres-as-queue for every project. But for a lot of projects it is going to be much harder to acquire the active user base generating the throughput Postgres can't handle, than doing continuous refactoring of the system to deal with the changing requirements.
One thing to note, if you use pq or another Postgres-as-queue approach, you should be aware of the work you'll need to do to move off it -- this pattern lets you do exactly-once processing by consuming your tasks in the same DB transaction where you process the side-effects. In general when using a separate task queue (RMQ, SQS, etc.) you need to do idempotent processing (at least once message semantics). A possible exception is if you use Kafka and use transactional event processing, but it's not serializable isolation.
This is probably a reason in favor of using a Postgres task queue initially since exactly-once is way simpler to build, but just be aware that you're going to need to rethink some of your architectural foundation if/when you need to move to a higher-throughput queue implementation.