You also could use an advisory exact lock, which would serialize the order of input and force everyone to wait in line, but the advantage here is that we are allowed shared concurrent reads while maintaining our lock. Thank you for the feedback. It was fun to revisit after 10 years. The initial piece came after a Columbus, OH Ruby meetup that Jose attended. It was quite fun to see him put it together as a whole and I ran with things a bit further than his first ideas with this.
SKIP LOCKED is amazing for this kind of thing. I used it to build a transaction outbox a few years ago.
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 have a system where each pod spins up around 30 scheduled job instances of one job each processing a "partition", then transaction outbox is queried with hash of identifier equating it to partition.
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.
