IMO, Python rode some of the growth from the data and scientific computing computing. It was lower friction for many people partially due to the amount of blogs, and open source projects that were available. Pandas and Numpy made it quite easy to get up and running with a lot of analytics. pytorch and tensorflow were also there to facilitate this, made people able to get the benefits of the optimized C code but most users did not have to learn C. Eventually FastAPI came out for those looking to build products out of their data, and FastAPI had fantastic documentation and guides, which helped these same folks coming from data/scientific compute build working software. I suppose what I am describing are some of the network effects.
Helpful read, thanks for sharing! We have been (slowly) working on some fairness related queueing features over in pgmq for a bit too https://github.com/pgmq/pgmq/pull/442. It does get complicated on Postgres IMO.
pgmq (which is linked on this gist) provides an api to this functionality. It can be 0 seconds, or 10 years if you want. It's not a row lock in, which can be expensive. In pgmq, its build into the design of the visibility timeout. FOR UPDATE SKIP LOCKED is there to ensure that only a single consumer gets any message, and then the visibility timeout lets consumer determine how long it should continue to remain unavailable to other consumers.
You get exactly once when you consume with pgmq and run your queue operations inside transactions in your postgres database. I can't think of an easy way to get some equivalent on SQS without building something like an outbox.