IMO, it is most valuable when you are looking for ways of reducing complexity. For a lot of projects, if you're already running Postgres then it is maybe not worth the added complexity of bringing in another technology.

Why use a service that comes with lock-in and poor developer experience when I can use the database I already have?

I agree in general, but there will always be certain requirements and team structures where stuff like this makes sense.

For me, I work in a small team of 6 devs on an ever growing app and feature set. I 100% will leverage managed services where cost and complexity allow. SQS is one of the most stable and cheapest AWS service, and the ability to just use it and not have to sysops it means we can spend more time building features.

When you're building an MVP, you want to keep the architecture as utterly simple as possible. SQS helps at scale, but your product may never reach scale. In the meantime, you vendor-locked into AWS, with all its attendant needs like setting up auth to AWS, account vending, etc. If your MVP already needs stuff like object storage, and you're setting up AWS anyway, then sure, prefer SQS. But a lot of MVPs are better set up as monolith + Postgres to start with and only complicating the architecture after product traction is found.

See https://news.ycombinator.com/item?id=40307454#40311843

no dependence on a third party