4 ms·

Great example of a job queue correctly implemented inside a transactional relational database (with SKIP LOCKED and a table dedicated to the job queue), instead

Great example of a job queue correctly implemented inside a transactional relational database (with SKIP LOCKED and a table dedicated to the job queue), instead of using a specialized tool.

ddorian43 8y ago
When you don't need insane scalability, having a transactional queue is a godsend.
- ngrilly 8y ago
  For the record, it's possible to have scalability and transactional enqueuing at the same time, with something like Google Cloud Datastore: https://cloud.google.com/appengine/docs/standard/python/datastore/transactions#transactional_task_enqueuing https://cloud.google.com/appengine/docs/standard/python/data...
  - ddorian43 8y ago
    Yeah, with sharding. And global transactions. Which are costly. As long as you pass the cost to the customer. But if you're doing a lot, then you can't use them.
shoo 8y ago
yes. the linked 2ndquadrant blog post explaining how to implement this using SKIP LOCKED is a good read: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgresql-9-5/ https://blog.2ndquadrant.com/what-is-select-skip-locked-for-... i used this a few years ago to implement my own (toy-scale: 2 workers 1 db running on a potato-class thin client computer) idiosyncratic job queue in postgres. i have a number of recurring jobs that need to be run periodically, and the jobs are not independent -- different jobs depend upon shared resources -- so if lots of jobs that depend on resource R just ran, we might need to wait a while before the other jobs that also depend on R are deemed feasible to be scheduled and run. it was reasonably easy to cobble something together of the form: each worker process does the following: 0. within a transaction 1. select a shortlist of rows for jobs that are due and are currently feasible, subject constraints ; 2. attempt to acquire one of of those rows using "select ... for update skip locked limit 1" 3. do work, update job result and status in db, commit the transaction the only part of this that is slightly clever is that i don't actually have a hard requirement for mutual exclusion between worker processes to mediate access to shared resources, so that means each worker process can maintain its own state of its 'fair share' of resources that it is consuming, and throttle itself without needing to coordinate this with other workers.

Great example of a job queue correctly implemented inside a transactional relational database (with SKIP LOCKED and a table dedicated to the job queue), instead of using a specialized tool.

ddorian43 8y ago

When you don't need insane scalability, having a transactional queue is a godsend.

ngrilly 8y ago
For the record, it's possible to have scalability and transactional enqueuing at the same time, with something like Google Cloud Datastore: https://cloud.google.com/appengine/docs/standard/python/datastore/transactions#transactional_task_enqueuing https://cloud.google.com/appengine/docs/standard/python/data...
- ddorian43 8y ago
  Yeah, with sharding. And global transactions. Which are costly. As long as you pass the cost to the customer. But if you're doing a lot, then you can't use them.

shoo 8y ago

yes. the linked 2ndquadrant blog post explaining how to implement this using SKIP LOCKED is a good read: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgresql-9-5/ https://blog.2ndquadrant.com/what-is-select-skip-locked-for-... i used this a few years ago to implement my own (toy-scale: 2 workers 1 db running on a potato-class thin client computer) idiosyncratic job queue in postgres. i have a number of recurring jobs that need to be run periodically, and the jobs are not independent -- different jobs depend upon shared resources -- so if lots of jobs that depend on resource R just ran, we might need to wait a while before the other jobs that also depend on R are deemed feasible to be scheduled and run. it was reasonably easy to cobble something together of the form: each worker process does the following: 0. within a transaction 1. select a shortlist of rows for jobs that are due and are currently feasible, subject constraints ; 2. attempt to acquire one of of those rows using "select ... for update skip locked limit 1" 3. do work, update job result and status in db, commit the transaction the only part of this that is slightly clever is that i don't actually have a hard requirement for mutual exclusion between worker processes to mediate access to shared resources, so that means each worker process can maintain its own state of its 'fair share' of resources that it is consuming, and throttle itself without needing to coordinate this with other workers.