Hacker News Reader

Comment by hmmokidk | Hacker News Reader

Incredibly well written. You clearly put a lot of thought and effort into this. Thank you.

Also FOR UPDATE SKIP LOCKED is interesting.

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.

FOR UPDATE SKIP LOCKED is great, but it needs to be in a transaction. In the example code it won't "do" anything because it selects for update then immediately loses the lock.

Claude says you can use a CTE to select and the run your update with the locked rows, but I have only ever used transactions.

Reply on news.ycombinator.com