Overall, I'm wildly impressed at how this Elixir code held up, and it was a joy to revisit this.
Such a pity the industry (customers) reacts with skepticism every time I propose solutions based in Elixir/Erlang. I always hear: "Elixir, what? We want Java/.Net/Python/php"
The one Danger Zone area though is when they want something between WordPress and a "real" app. A lot of the clients I've talked to already have WordPress and are really trying to stretch the framework to places it isn't really made to go. Those customers often think "I should be able to get a full app built for <=$10,000" and often shop around until they find someone who will accept it, and then they usually end up getting a bad/buggy product that is slow as dirt. At that point it's often too late as well because nobody wants to pay to start over. For those clients though I wouldn't recommend Elixir because they would almost rather spend more in maintenance costs than in initial development because the numbers feel smaller when spread out, even if they add up to more in the long run and with a much worse product.
That is not to say of course that any Elixir app is going to be better - there are bad Elixir devs just like any stack, though I think Elixir's relative lack of popularity actually leads to an overall more skilled dev pool.
In the context that doesn't really say a lot, they pay the floor guy and the architect to solve problems, just different problems.
>they are trapped in an institutional/corporate mindset (old patterns are most suitable, 'best practice', 'reliable, proven tech' and such)
While true, it is also correct. From their perspective Elixir is an exotic material, the next "one they pay to solve their problems" is not going to know how to work with it, scrap it, and start over. Therefore the Elixir solution isn't a good solution for them.
As a business man once told me, "I never saw a feature I liked so much that I was wiling to pay for it twice."
The worker is stateless and "dumb" by design (currently it runs on serverless functions) - it just calls SQL functions: "poll_for_tasks" to get some tasks from the queue and then either "complete_task" or "fail_task" after executing user code - that's it, nothing more, so it should be relatively easy to adopt it to other runtimes.
I have written a small architecture primer on pgflow if anyone is interested in its simple but flexible design https://www.pgflow.dev/concepts/how-pgflow-works/
Also FOR UPDATE SKIP LOCKED is interesting.
One thing worth looking into if you do this in production is adding a way to add partitions such that each partition is single threaded. It’s the only way to guarantee ordering if your jobs are doing anything non-deterministic.
We increased partition counts on sale days and it works well for us.
Couple of gotchas we had were.
1) Using hashtext from postgres is sketchy.
2) Increasing partiton count is an orchestra which requires stopping the partition.