3 ms·

Use the SKIP LOCKED function in your SQL database. You then get a huge amount of flexibility and it becomes easy to implement any custom queueing mechanism tha

Use the SKIP LOCKED function in your SQL database. You then get a huge amount of flexibility and it becomes easy to implement any custom queueing mechanism that suits you.

shoo 7y ago
To expand on andrewstuart's comment: Consider PostgreSQL. PostgreSQL' skip locked functionality lets you roll your own queue using the database. There's a good article here: https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/ https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock... I used this as the starting point to build my own custom queue-thing using postgres & skip locked with the following features: there is a fixed collection of about 100,000 tasks that need to be executed roughly on schedule, each task can have a different schedule and a priority, tasks can require certain resources to be available as a pre-req before they can execute, and finally there's a mechanism for tasks to define dependencies on other tasks. If a dependency of a given task executes, then that task is also scheduled for execution. Maybe "queue" is less of an accurate description than "incomplete, bug ridden, ad-hoc, poorly specified implementation of make + cron". But there is task-queue like functionality so worker processes can do queries like "lock and give me the highest priority task that is scheduled for execution or overdue and that has enough resources to execute". I don't have very many tasks (only 100k) and the update frequency is weekly or slower for most of them. I get reasonable performance running both postgres and two python worker processes (that execute the tasks) and a web app on a single low-energy box with an AMD G-T56N CPU and about 4gb of RAM. Cost is about $40 for the hardware (second hand), $15 a year in electricity, and $0 = $0 / hour * 10 ... 40 hours for the custom software build and a few post-release debugging sessions in order to get the locking working smoothly, since I don't bill myself for software dev services.
imhoguy 7y ago
Exactly this! You can have column like "occurs_at" an then filter with "occurs_at < current_timestamp" together with ordering on that column or possibly some priority one too to have workers perform these tasks in importance order. Retries are trivial too - you can have attempts countdown column etc. Also check NOTIFY/LISTEN which can be used to avoid pooling and wake-up workers efficiently. https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html

Use the SKIP LOCKED function in your SQL database. You then get a huge amount of flexibility and it becomes easy to implement any custom queueing mechanism that suits you.

shoo 7y ago

To expand on andrewstuart's comment: Consider PostgreSQL. PostgreSQL' skip locked functionality lets you roll your own queue using the database. There's a good article here: https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/ https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock... I used this as the starting point to build my own custom queue-thing using postgres & skip locked with the following features: there is a fixed collection of about 100,000 tasks that need to be executed roughly on schedule, each task can have a different schedule and a priority, tasks can require certain resources to be available as a pre-req before they can execute, and finally there's a mechanism for tasks to define dependencies on other tasks. If a dependency of a given task executes, then that task is also scheduled for execution. Maybe "queue" is less of an accurate description than "incomplete, bug ridden, ad-hoc, poorly specified implementation of make + cron". But there is task-queue like functionality so worker processes can do queries like "lock and give me the highest priority task that is scheduled for execution or overdue and that has enough resources to execute". I don't have very many tasks (only 100k) and the update frequency is weekly or slower for most of them. I get reasonable performance running both postgres and two python worker processes (that execute the tasks) and a web app on a single low-energy box with an AMD G-T56N CPU and about 4gb of RAM. Cost is about $40 for the hardware (second hand), $15 a year in electricity, and $0 = $0 / hour * 10 ... 40 hours for the custom software build and a few post-release debugging sessions in order to get the locking working smoothly, since I don't bill myself for software dev services.

imhoguy 7y ago

Exactly this! You can have column like "occurs_at" an then filter with "occurs_at < current_timestamp" together with ordering on that column or possibly some priority one too to have workers perform these tasks in importance order. Retries are trivial too - you can have attempts countdown column etc. Also check NOTIFY/LISTEN which can be used to avoid pooling and wake-up workers efficiently. https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html