6 ms·

Used the "FOR UPDATE SKIP LOCKED LIMIT 1" trick to implement a job server in PG a few years ago for the first time. It's a great solution.

MuffinFlavored 7y ago
Can you show what your schema for your job table looked like?
- evv 7y ago
  Check this out: https://github.com/graphile/worker/blob/3540df5ab4eb73f846d54959fdfad07897b616f0/sql/000001.sql#L11-L21 https://github.com/graphile/worker/blob/3540df5ab4eb73f846d5...
philips 7y ago
It is really good! I started a Go implementation for a project I am working on https://github.com/philips/pg-go-queue https://github.com/philips/pg-go-queue
- davidbanham 7y ago
  I should really get around to writing some docs for this, but you may find this useful: https://github.com/davidbanham/kewpie_go https://github.com/davidbanham/kewpie_go I’ve been using it for years across multiple products. I also built this for a client lately. It lets them interop their Haskell services with kewpie a bit easier without needing to write a native wrapper. https://github.com/paidright/kewpie-http https://github.com/paidright/kewpie-http
alberth 7y ago
Why the “LIMIT 1”?
- aidos 7y ago
  Just grabbing the next available task to work on.
- ubu7737 7y ago
  It makes the job-fetching more atomic, as the article points out properly. If you take >1, you have to sort the situation where 1 or more jobs fail, which adds an unreasonable amount of complexity. On my first go I was taking batches, on the reasoning that it optimized for query performance. It was a complete disaster and I quickly realized that I was engaging in premature optimization. Batching is out, stream-processing is in. If you design your job table correctly, PG will perform very well as an advanced stream processor.
codesnik 7y ago
me too. I was surprised it wasn't that common at the time.
- ubu7737 7y ago
  Right, I was working mostly off one very well-written article on the topic, it was an easy search because there is really only one correct way to implement atomic job-handling with PG.

MuffinFlavored 7y ago

Can you show what your schema for your job table looked like?

evv 7y ago
Check this out: https://github.com/graphile/worker/blob/3540df5ab4eb73f846d54959fdfad07897b616f0/sql/000001.sql#L11-L21 https://github.com/graphile/worker/blob/3540df5ab4eb73f846d5...

philips 7y ago

It is really good! I started a Go implementation for a project I am working on https://github.com/philips/pg-go-queue https://github.com/philips/pg-go-queue

davidbanham 7y ago
I should really get around to writing some docs for this, but you may find this useful: https://github.com/davidbanham/kewpie_go https://github.com/davidbanham/kewpie_go I’ve been using it for years across multiple products. I also built this for a client lately. It lets them interop their Haskell services with kewpie a bit easier without needing to write a native wrapper. https://github.com/paidright/kewpie-http https://github.com/paidright/kewpie-http

alberth 7y ago

Why the “LIMIT 1”?

aidos 7y ago
Just grabbing the next available task to work on.
ubu7737 7y ago
It makes the job-fetching more atomic, as the article points out properly. If you take >1, you have to sort the situation where 1 or more jobs fail, which adds an unreasonable amount of complexity. On my first go I was taking batches, on the reasoning that it optimized for query performance. It was a complete disaster and I quickly realized that I was engaging in premature optimization. Batching is out, stream-processing is in. If you design your job table correctly, PG will perform very well as an advanced stream processor.

codesnik 7y ago

me too. I was surprised it wasn't that common at the time.

ubu7737 7y ago
Right, I was working mostly off one very well-written article on the topic, it was an easy search because there is really only one correct way to implement atomic job-handling with PG.