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).
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.